思维导图
10053事件概述
我们在查看一条SQL语句的执行计划时，只看到了CBO最终告诉我们的执行计划结果，但是我们并不知道CBO为何要这样做。
特别是当执行计划明显失真时，我们特别想搞清楚为什么CBO会做出这样的一个选择，那么就可以用10053事件来分析SQL分析过程的trace文件。
同10046事件一样，10053事件依然无法在官网上找到相关的信息。
10053事件为我们真正的揭开蒙在CBO身上的面纱。
如何做10053事件分析
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as zmc@xgj
SQL> create table t as select rownum x from dba_objects;
Table created
SQL> create index index_t on t(x);
Index created
##同时对表和索引进行分析
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade => true);
PL/SQL procedure successfully completed
SQL> create table t1 as select x , 'T1' name from t where x < 10000;
Table created
##开启10053事件分析
SQL> alter session set events '10053 trace name context forever,level 1';
Session altered
##执行计划
SQL> explain plan for select t1.* from t ,t1 where t.x<100 and t.x=t1.x;
Explained
##关闭10053事件
SQL> alter session set events '10053 trace name context off';
Session altered- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
我们可以发现，10053事件的使用方法和10046是一样的，首先需要个事件设置一个级别level 1 ,然后运行SQL（或者直接使用explain plan的方式产生执行计划），最后终止事件。
获取10053生成的trace文件
10053事件同10046事件一样也会在同样的路径下产生一个trace文件。 值得注意的是，10053生成的trace文件不能够使用tkprof工具处理。 tkprof工具只能处理sql_trace或者100046事件产生的trace文件。 对于10053事件我们直接阅读原始文件即可。
如何获取，查看10046事件获取trace文件的方法步骤，不再赘述了
oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$ls *4348*
cc_ora_4348.trc cc_ora_4348.trm- 1
- 2
分析10053生成的trace文件
查看cc_ora_4348.trc原文件，第一部分都是trace文件通用的，包含操作系统，数据库和会话的信息等等…
10053事件从 Predicate Move-Around (PM) 开始 进入 10053事件的trace信息部分， 这一部分CBO的主要工作是对SQL语句的谓词进行分析、重写，把它改写成最符合逻辑的SQL语句.
接下来的部分：
这一部分解释trace文件中常用到的一些缩写的指标含义，在trace的开头列举出来，以便更加容易的阅读trace文件。
从逻辑上看 “T”.”X”<100 AND “T”.”X”=”T1”.”X” 和 “T”.”X”<100 AND “T”.”X”=”T1”.”X” AND “T1”.”X”<100 这两个谓词条件上是等价的，CBO把它改写成这样，主要是为了方便统计每一步的成本和估算Cardinality(基数)。
下面是绑定变量的描述
Peeked values of the binds in SQL statement
如果SQL中有变量绑定，并且SQL语句执行了bind peeking,在这一项中会有相应的信息。
接下来就是
BASE STATISTICAL INFORMATION， 主要是SQL语句中饮用到的基本对象信息，包括关联表和各自索引的信息，这些信息可以在相关的视图中查到比如user_tables 和 user_index,这些值在CBO计算代价的时候都会被考虑到。
可以看到这部分的信息一共列出了3个对象信息： t表 t1表 INDEX_T。
表信息的部分中包含了表的行数，数据块数，平均行长， 对于字段，只列出了谓词条件中包含的字段，对谓词中没有出现的字段，因为它不影响执行计划的选择，所以CBO不考虑将它考虑到代价中来。
我们看到，这里列出的字段是x字段。因为它既是两表关联的字段，同时自身也是一个谓词条件。
字段部分：X列的信息包含了它的类型、平均长度、非重复的值、空值、密度及列的最大最小值，这些信息在CBO做执行计划的计算上都要作为输入的值。
索引部分中列出了 索引的高度、索引页数块（LB, Leaf Blocks）,每个索引键值占据的数据块数（LB/K, Leaf Blocks/Key）,每个索引键值对应的数据块数（DB/K,Data Blokcs/Key）, 索引的聚合因子（CLUF，Clustering Factor）.
值得一提的是 CLUF索引的聚合因子。 它表示索引中的键值和原表上的数据分部的一种关系，当索引键值和表中数据的排列顺序大致相同时，它以为着索引键值指向的数据块越集中，CLUF 因子越小，越有利于索引的使用。 反之，CLUF的值越大，越不利于索引的使用。
了解这个指标对我们分析SQL执行计划很有用处，比如当我们发现SQL执行计划异常，可是从Cardinality上无法解释时，也许应该考虑下是否是Clustering Factor的影响导致的。
接下来的部分是CBO计算的每个对象单独访问的代价。 CBO要计算出每个对象单独访问时的代价，通过比较所有的数据访问代价，选择出代价最小的一种访问方式：
SINGLE TABLE ACCESS PATH
T表：
这里面有2个指标对对于我们分析执行计划比较重要
Card: Original: 35252.000000
原始记录数,也就是操作数据源的输入记录数，在这里就是表的实际记录数35252
SQL> select count(1) from t ;
COUNT(1)
----------
35252- 1
- 2
- 3
- 4
- 5
Rounded: 99
输出的记录数，CBO计算出通过这些过滤条件，预计得到的记录数。可知符合条件的记录99条，CBO估算出99条。（有些时候可能不是一样，有可能是比较接近实际值）
SQL> select count(1) from t ,t1 where t.x<100 and t.x=t1.x;
COUNT(1)
----------
99
- 1
- 2
- 3
- 4
- 5
- 6
T1表：
T1忘记做表分析了…
T1表，忘记建索引了，只能全表扫描….
至此，CBO 计算出了每个表单独访问数据代价的最小方式，为下一步多表关联查询提供代价计算的数据依据。
下面的部分CBO会列出 T、 T1表所有的关联方式，并计算出每一种关联方式的代价，最终选择出代价最小的关联方式作为SQL的执行计划：
这里面会有六种情况：
OPTIMIZER STATISTICS AND COMPUTATIONS
Join order[1]: T1[T1]#0 T[T]#1 （T1关联 T）
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: T1[T1]#0 T[T]#1
***************
Now joining: T[T]#1
***************
NL Join
Outer table: Card: 99.00 Cost: 7.08 Resp: 7.08 Degree: 1 Bytes: 17
Access path analysis for T
Inner table: T Alias: T
Access Path: TableScan
NL Join: Cost: 1643.96 Resp: 1643.96 Degree: 1
Cost_io: 1618.00 Cost_cpu: 742442284
Resp_io: 1618.00 Resp_cpu: 742442284
Access Path: index (index (FFS))
Index: INDEX_T
resc_io: 21.14 resc_cpu: 6548312
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Inner table: T Alias: T
Access Path: index (FFS)
NL Join: Cost: 2122.74 Resp: 2122.74 Degree: 1
Cost_io: 2100.00 Cost_cpu: 650434250
Resp_io: 2100.00 Resp_cpu: 650434250
kkofmx: index filter:"T"."X"<100
Access Path: index (AllEqJoinGuess)
Index: INDEX_T
resc_io: 1.00 resc_cpu: 8171
ix_sel: 0.000028 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 50.00
***** End Logdef Adjustment ******
NL Join : Cost: 106.10 Resp: 106.10 Degree: 1
Cost_io: 106.00 Cost_cpu: 2965253
Resp_io: 106.00 Resp_cpu: 2965253
Best NL cost: 106.10
resc: 106.10 resc_io: 106.00 resc_cpu: 2965253
resp: 106.10 resp_io: 106.00 resc_cpu: 2965253
Join Card: 98.012780 = outer (99.000000) * inner (99.002808) * sel (0.010000)
Join Card - Rounded: 98 Computed: 98.01
Outer table: T1 Alias: T1
resc: 7.08 card 99.00 bytes: 17 deg: 1 resp: 7.08
Inner table: T Alias: T
resc: 2.00 card: 99.00 bytes: 5 deg: 1 resp: 2.00
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort 1227 Area size: 1048576 Max Area size: 214743040
Degree: 1
Blocks to Sort: 1 Row size: 29 Total Rows: 99
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 28632647
Total Temp space used: 0
SORT ressource Sort statistics
Sort 1227 Area size: 1048576 Max Area size: 214743040
Degree: 1
Blocks to Sort: 1 Row size: 16 Total Rows: 99
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 28632647
Total Temp space used: 0
SM join: Resc: 11.08 Resp: 11.08 [multiMatchCost=0.00]
SM Join
SM cost: 11.08
resc: 11.08 resc_io: 9.00 resc_cpu: 59450866
resp: 11.08 resp_io: 9.00 resp_cpu: 59450866
Outer table: T1 Alias: T1
resc: 7.08 card 99.00 bytes: 17 deg: 1 resp: 7.08
Inner table: T Alias: T
resc: 2.00 card: 99.00 bytes: 5 deg: 1 resp: 2.00
using dmeth: 2 #groups: 1
Cost per ptn: 0.02 #ptns: 1
hash_area: 256 (max=52428) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join: Resc: 9.10 Resp: 9.10 [multiMatchCost=0.00]
HA Join
HA cost: 9.10
resc: 9.10 resc_io: 9.00 resc_cpu: 2810323
resp: 9.10 resp_io: 9.00 resp_cpu: 2810323
Best:: JoinMethod: Hash
Cost: 9.10 Degree: 1 Resp: 9.10 Card: 98.01 Bytes: 22
***********************
Best so far: Table#: 0 cost: 7.0752 card: 99.0000 bytes: 1683
Table#: 1 cost: 9.0983 card: 98.0128 bytes: 2156
***********************Join order[2]: T[T]#1 T1[T1]#0（T关联 T1）
Join order[2]: T[T]#1 T1[T1]#0
***************
Now joining: T1[T1]#0
***************
NL Join
Outer table: Card: 99.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 5
Access path analysis for T1
Inner table: T1 Alias: T1
Access Path: TableScan
NL Join: Cost: 574.45 Resp: 574.45 Degree: 1
Cost_io: 567.00 Cost_cpu: 213015937
Resp_io: 567.00 Resp_cpu: 213015937
Best NL cost: 574.45
resc: 574.45 resc_io: 567.00 resc_cpu: 213015937
resp: 574.45 resp_io: 567.00 resc_cpu: 213015937
Join Card: 98.012780 = outer (99.002808) * inner (99.000000) * sel (0.010000)
Join Card - Rounded: 98 Computed: 98.01
Outer table: T Alias: T
resc: 2.00 card 99.00 bytes: 5 deg: 1 resp: 2.00
Inner table: T1 Alias: T1
resc: 7.08 card: 99.00 bytes: 17 deg: 1 resp: 7.08
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort 1227 Area size: 1048576 Max Area size: 214743040
Degree: 1
Blocks to Sort: 1 Row size: 29 Total Rows: 99
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 28632647
Total Temp space used: 0
SM join: Resc: 10.08 Resp: 10.08 [multiMatchCost=0.00]
SM Join
SM cost: 10.08
resc: 10.08 resc_io: 9.00 resc_cpu: 30818220
resp: 10.08 resp_io: 9.00 resp_cpu: 30818220
Outer table: T Alias: T
resc: 2.00 card 99.00 bytes: 5 deg: 1 resp: 2.00
Inner table: T1 Alias: T1
resc: 7.08 card: 99.00 bytes: 17 deg: 1 resp: 7.08
using dmeth: 2 #groups: 1
Cost per ptn: 0.02 #ptns: 1
hash_area: 256 (max=52428) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join: Resc: 9.10 Resp: 9.10 [multiMatchCost=0.00]
HA Join
HA cost: 9.10
resc: 9.10 resc_io: 9.00 resc_cpu: 2810323
resp: 9.10 resp_io: 9.00 resp_cpu: 2810323
Join order aborted: cost > best plan cost
***********************名词解释：
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
从trace文件开头部分可以查看
Join order[1]: T1[T1]#0 T[T]#1
T1关联 T 可以提取到的信息：
NL Join
Access Path: TableScan
NL Join: Cost: 1643.96 Resp: 1643.96 Degree: 1
Cost_io: 1618.00 Cost_cpu: 742442284
Resp_io: 1618.00 Resp_cpu: 742442284
Access Path: index (FFS)
NL Join: Cost: 2122.74 Resp: 2122.74 Degree: 1
Cost_io: 2100.00 Cost_cpu: 650434250
Resp_io: 2100.00 Resp_cpu: 650434250
Access Path: index (AllEqJoinGuess)
Index: INDEX_T
NL Join : Cost: 106.10 Resp: 106.10 Degree: 1
Cost_io: 106.00 Cost_cpu: 2965253
Resp_io: 106.00 Resp_cpu: 2965253
结论：（NL Join 的最小Cost是1-6.10 ）
Best NL(Nested loop)
NL Join : Cost: 106.10 Resp: 106.10 Degree: 1
SM Join
SM cost: 11.08
resc: 11.08 resc_io: 9.00 resc_cpu: 59450866
resp: 11.08 resp_io: 9.00 resp_cpu: 59450866
HA Join
HA cost: 9.10
resc: 9.10 resc_io: 9.00 resc_cpu: 2810323
resp: 9.10 resp_io: 9.00 resp_cpu: 2810323
NL Join SM Join HAJoin 再对比得出最小cost
Best JoinMethod: Hash
Cost: 9.10 Degree: 1 Resp: 9.10 Card: 98.01 Bytes: 22
Best so far: Table#: 0 cost: 7.0752 card: 99.0000 bytes: 1683
Table#: 1 cost: 9.0983 card: 98.0128 bytes: 2156
Join order[2]: T[T]#1 T1[T1]#0
T关联 T1 可以提取到的信息：
(T1没有建索引)
NL Join
Access Path: TableScan
NL Join: Cost: 574.45 Resp: 574.45 Degree: 1
Cost_io: 567.00 Cost_cpu: 213015937
Resp_io: 567.00 Resp_cpu: 213015937
Best NL cost: 574.45
resc: 574.45 resc_io: 567.00 resc_cpu: 213015937
resp: 574.45 resp_io: 567.00 resc_cpu: 213015937
SM Join
SM cost: 10.08
resc: 10.08 resc_io: 9.00 resc_cpu: 30818220
resp: 10.08 resp_io: 9.00 resp_cpu: 30818220
HA Join
HA cost: 9.10
resc: 9.10 resc_io: 9.00 resc_cpu: 2810323
resp: 9.10 resp_io: 9.00 resp_cpu: 2810323
NL Join SM Join HAJoin 再对比得出最小cost
可知道，Join order[2] 最小cost也是Ha
对比下 Join order[1] 和 Join order[2] 的Ha Cost
感觉应该是 1 和 2 中最小的cost 对比， 这里都是HA最小
看trace里1 和 2 的HA 是一样的…..
然而Oracle确放弃了这个（不知道跟T1没有索引有没有关系） ，是个疑问
总之Oracle的选择是：
Join order aborted: cost > best plan cost
Number of join permutations tried: 2
oralce通过对比之后实际选择的关联顺序
CBO先估量出 T1 和 T 使用什么方式 扫描 最优，从上面对表的分析我们也可以看到 对T1 才去的是全表扫描，对T采取的是索引 。
然后再估量出 这两个表使用何种关联方式最优，最终得到一个执行计划。
经过一些列的比较，Oracle最终选择了如上的执行计划作为SQL的最终执行计划。
接下来trace文件最后一部分依然是参数和bug修复信息，忽略…
总结
通过分析10053事件的trace原文件，我们会发现，CBO一定最终选择的是代价最低的数据访问路径作为SQL的执行计划。
如果我们觉得CBO做出的执行计划不是最优的，就应该去分析，比如对于CBO选择的每一个代价最低的访问数据方式，我们提供给CBO的分析信息是否真实？ 抑或是代价高的数据访问方式的分析是否真实？
因为CBO只不过是一个数据模型，它只是机械的将搜集到的各种信息通过固定的方式进行计算，如果我们能够保证给CBO提供的各种信息是正确的，CBO通常就应该会计算出最优的执行计划。
10053事件给我们提供了一种深入CBO的内部去查看CBO如何工作的方式，不仅能看到ORACLE是根据什么样的一句来得出最终的执行计划，同时我们也能人为的去验证CBO使用的一些统计数据的准确性。