10053事件分析案例一则-mysql教程

测试库两张表，数据一致，(表有复合主键A+B)，但同样执行DELETE TABLE FROM T1/T2 WHERE A=1 AND ROWNUM100;时，T1表删除时间非常长，T2表删除时间很快。在PLSQL中或sqlplus中查看执行计划都是一样的，表示都用到了索引范围扫描。 PLAN_TABLE_OUTPUT -------

测试库两张表，数据一致，(表有复合主键A+B)，但同样执行DELETE TABLE FROM T1/T2 WHERE A=&＃39;1&＃39; AND ROWNUM<100;时，T1表删除时间非常长，T2表删除时间很快。在PLSQL中或sqlplus中查看执行计划都是一样的，表示都用到了索引范围扫描。

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1000 | 12000 | 3217 |
| 1 | DELETE | T1 | | | |
|* 2 | COUNT STOPKEY | | | | |
|* 3 | INDEX RANGE SCAN | IDX_T1 | 420K| 4931K| 3217 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - filter(ROWNUM<=1000)
3 - access("T1"."A"=&＃39;1&＃39;)
Note: cpu costing is off

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1000 | 12000 | 2965 |
| 1 | DELETE | T2 | | | |
|* 2 | COUNT STOPKEY | | | | |
|* 3 | INDEX RANGE SCAN | IDX_T2 | 393K| 4607K| 2965 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - filter(ROWNUM<=1000)
3 - access("T2"."A"=&＃39;1&＃39;)
Note: cpu costing is off

显然感觉这两个表的实际操作和执行计划不太相符，这时10053事件就起到了作用。

10053介绍：

10053 事件是oracle 提供的用于跟踪sql 语句成本计算的内部事件，它能记载CBO 模式下oracle 优化器如何计算sql 成本，生成相应的执行计划。 用来描述oracle如何选择执行计划的过程，然后输出到trace文件里，因为我们经常看执行计划怎么执行的消耗了哪些资源，而不是常看执行计划怎么选择出来了的。

10053特点：

(1) 只可以了解oracle执行计划的选择过程

(2) 无法获知代价的计算公式，因为这是oracle内部的商业机密，而且每个oracle版本的优化器计算公式都不相同差距还是蛮大的，不同版本的同一个语句的代价也不一样，优化器现在还不是很成熟，还有待完善。

(3) 在这个里面我们重点要了解的是“代价”是如何计算出来的，然后我们才能了解执行计划是如何选择的。

(4) 在10053中可以了解哪些因素影响sql的执行代价

(5) oracle 8i cost等价IO资源消耗 9i以后cost等价IO+CPU+网络+等待事件+其他代价

T1表的10053事件信息：

***************************************

BASE STATISTICAL INFORMATION
***********************
Table stats Table: T1 Alias: T1 来自user_tables视图
TOTAL :: CDN: 2341358 NBLKS: 13921 AVG_ROW_LEN: 40
-- Index stats 来自user_indexes视图
INDEX NAME: IDX_STAROTHER COL#: 2 3
TOTAL :: LVLS: 2 #LB: 13609 #DK: 2156054 LB/K: 1 DB/K: 1 CLUF: 165252
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
Column: AIRLINE_CO Col#: 2 Table: T1 Alias: T1
NDV: 7 NULLS: 0 DENS: 1.4286e-01
NO HISTOGRAM: #BKT: 1 #VAL: 2
TABLE: STAROTHERPRF ORIG CDN: 2341358 ROUNDED CDN: 334480 CMPTD CDN: 334480
Access path: tsc Resc: 1340 Resp: 1340 全表扫描代价(1340)，这里tsc我想应该是TableScan的缩写
Skip scan: ss-sel 0 andv 308008
ss cost 308008 索引跳跃扫描的代价(1945)
index io scan cost 1945
Access path: index (index-only) 索引(范围)扫描代价(1947)
Index: IDX_T1
TABLE: T1
RSC_CPU: 0 RSC_IO: 1947
IX_SEL: 1.4286e-01 TB_SEL: 1.4286e-01

BEST_CST: 1340.00 PATH: 2 Degree: 1 最佳代价是1340，即全表扫描

对应的执行计划：

***************************************
GENERAL PLANS
***********************
Join order[1]: STAROTHERPRF[STAROTHERPRF]#0
Best so far: TABLE#: 0 CST: 1340 CDN: 334480 BYTES: 4348240
Final - All Rows Plan:
JOIN ORDER: 1
CST: 1340 CDN: 334480 RSC: 1340 RSP: 1340 BYTES: 4348240
IO-RSC: 1340 IO-RSP: 1340 CPU-RSC: 0 CPU-RSP: 0
QUERY
explain plan for delete from starotherprf WHERE AIRLINE_CODE = &＃39;US&＃39; AND ROWNUM <= 1000
PLAN
Cost of plan: 1340
Operation...........Object name.....Options.........Id...Pid..
DELETE STATEMENT 0
DELETE STAROTHERPRF 1
COUNT STOPKEY 2 1
TABLE ACCESS T1 FULL 3 2
QUERY

显示用的就是全表扫描

T2表的10053事件信息：

***************************************

SINGLE TABLE ACCESS PATH
Column: AIRLINE_CO Col#: 1 Table: T2 Alias: T2
NDV: 19 NULLS: 0 DENS: 5.2632e-02
NO HISTOGRAM: #BKT: 1 #VAL: 2
TABLE: CASTARPRF ORIG CDN: 6665065 ROUNDED CDN: 350793 CMPTD CDN: 350793
Access path: tsc Resc: 4275 Resp: 4275 全表扫描代价(4275)
Skip scan: ss-sel 0 andv 413617 索引跳跃扫描代价(413617)
ss cost 413617
index io scan cost 1973
Access path: index (index-only) 索引(范围)扫描代价(1975)
Index: IDX_T2
TABLE: T2
RSC_CPU: 0 RSC_IO: 1975
IX_SEL: 5.2632e-02 TB_SEL: 5.2632e-02
BEST_CST: 1975.00 PATH: 4 Degree: 1 最佳代价是1975，即索引扫描

对应的执行计划：

***************************************
GENERAL PLANS
***********************
Join order[1]: CASTARPRF[CASTARPRF]#0
Best so far: TABLE#: 0 CST: 1975 CDN: 350793 BYTES: 4911102
prefetching is on for IDX_CASTAR
Final - All Rows Plan:
JOIN ORDER: 1
CST: 1975 CDN: 350793 RSC: 1975 RSP: 1975 BYTES: 4911102
IO-RSC: 1975 IO-RSP: 1975 CPU-RSC: 0 CPU-RSP: 0
QUERY
explain plan for delete from castarprf WHERE AIRLINE_CODE = &＃39;US&＃39; AND ROWNUM <= 1000
PLAN
Cost of plan: 1975
Operation...........Object name.....Options.........Id...Pid..
DELETE STATEMENT 0
DELETE CASTARPRF 1
COUNT STOPKEY 2 1
INDEX IDX_T2 RANGE SCAN 3 2
QUERY

显示用的就是索引扫描

现在就可以知道为什么这两张表删除时间不同了，原因就是T1表CBO选择了错误的执行计划，导致全表扫描，因此百万级的数据就会耗费更长的时间。

总结：当感觉SQL语句执行时走的是错误的执行计划，而又找不到原因时，这时请用10053来分析一下原因。这就是10053的适用场景。