读懂Oracle 10053事件

erlchina

　　我们可以通过10046事件看到一个SQL的执行的统计信息，以及执行计划，但是我们只看到了CBO最终告诉我们的执行结果，却并不知道CBO为什么要这么选择，那么就可以通过10053事件来生成SQL分析的整个过程到trace文件中，通俗点讲10053跟踪选路过程，10046产生结果。
　　关于10046事件参考我的blog：
　　http://5073392.blog.51cto.com/5063392/1308195
　　10053包含2个级别：
　　Level 2：2级是1级的一个子集，它包含以下内容：
　　Column statistics
　　Single Access Paths
　　Join Costs
　　Table Joins Considered
　　Join Methods Considered (NL/MS/HA)
　　Level 1：1级比2级更详细，它包含2级的所有内容，在加如下内容：
　　Parameters used by the optimizer
　　Index statistics
　　产生一个10053的trace文件

　　SQL> create table t as select rownum>　　Table created.

　　SQL> create index>　　Index created.
　　SQL> exec dbms_stats.gather_table_stats('sys','t',cascade=>true);
　　PL/SQL procedure successfully completed.

　　SQL> create table t1 as select>　　Table created.
　　SQL> select tracefile from v$process where addr=(select paddr from v$session where sid=(select distinct sid from v$mystat));
　　TRACEFILE
　　--------------------------------------------------------------------------------
　　/u01/app/oracle/diag/rdbms/rbksafari/RBKSAFARI/trace/RBKSAFARI_ora_9558.trc

　　SQL>>
　　Session>　　SQL> explain plan for select t1.* from t,t1 where t.id>
　　Session>　　10053和10046事件的用法是一样的，需要注意的是这个trace文件不能用tkprof工具处理，tkprof工具只能处理10046和sql_trace文件，下面是刚才生成的trace文件内容
　　这里从BASE STATISTICAL INFORMATION开始看
　　***************************************
　　BASE STATISTICAL INFORMATION
　　***********************
　　Table Stats::
　　Table: T1 Alias: T1 (NOT ANALYZED)
　　#Rows: 164 #Blks: 2 AvgRowLen: 100.00
　　***********************
　　Table Stats::
　　Table: T Alias: T
　　#Rows: 73924 #Blks: 113 AvgRowLen: 5.00
　　Index Stats::

　　Index:>　　LVLS: 1 #LB: 164 #DK: 73924 LB/K: 1.00 DB/K: 1.00 CLUF: 113.00
　　Access path analysis for T
　　***************************************
　　这一部分是sql语句中用到的对象基本信息，包括关联表和索引信息，我们看到这里列出了关于这个查询的三个对象信息，表T,T1和索引IDX_T，表信息包含了表的行数（#Rows），数据块数（#Blks），平均行长（AvgRowLen）索引项部分列出了索引叶块数（#LB），每个索引键值占据的数据块数（LB/K），每个索引键值对应的表中数据块数（DB/K）索引的聚合因子（CLUF）,当索引的键值对应的数据库越大，索引的聚合因子就越大，越不利用索引的使用。
　　接下来的部分是CBO计算的每个对象单独访问的代价
　　***************************************
　　SINGLE TABLE ACCESS PATH
　　Single Table Cardinality Estimation for T[T]
　　Table: T Alias: T
　　Card: Original: 73924.000000 Rounded: 99 Computed: 99.00 Non Adjusted: 99.00
　　Access Path: TableScan
　　Cost: 32.49 Resp: 32.49 Degree: 0
　　Cost_io: 32.00 Cost_cpu: 15589523
　　Resp_io: 32.00 Resp_cpu: 15589523
　　Access Path: index (index (FFS))

　　Index:>　　resc_io: 46.00 resc_cpu: 13734996
　　ix_sel: 0.000000 ix_sel_with_filters: 1.000000
　　Access Path: index (FFS)
　　Cost: 46.43 Resp: 46.43 Degree: 1
　　Cost_io: 46.00 Cost_cpu: 13734996
　　Resp_io: 46.00 Resp_cpu: 13734996
　　Access Path: index (IndexOnly)

　　Index:>　　resc_io: 2.00 resc_cpu: 34243
　　ix_sel: 0.001339 ix_sel_with_filters: 0.001339
　　Cost: 2.00 Resp: 2.00 Degree: 1
　　Best:: AccessPath: IndexRange

　　Index:>　　Cost: 2.00 Degree: 1 Resp: 2.00 Card: 99.00 Bytes: 0
　　Access path analysis for T1
　　***************************************
　　这里有两个指标对于我们分析执行计划比较重要：
　　Card: Original: 73924.000000
　　原纪录数，也就是操作数据原的输入记录数，在这里就是表的实际行数73294
　　Card: Rounded： 99
　　输出的记录数，CBO计算出通过条件过滤，预计得到的行数99
　　通过这里我们可以看出对于T表CBO给我们计算出了3种访问方式
　　全表扫描
　　Access Path: TableScan  开销：Cost: 32.49
　　索引快速扫描
　　Access Path: index (index (FFS)) 开销：Cost: 46.43
　　单独访问索引
　　Access Path: index (IndexOnly) 开销：Cost: 2.00
　　可以看出，单独访问索引的方式是代价最低的，因此CBO得出下来结论
　　Best:: AccessPath: IndexRange

　　Index:>　　Cost: 2.00 Degree: 1 Resp: 2.00 Card: 99.00 Bytes: 0
　　这里我不明白上面写的是IndexOnly，为什么最后结论改写成IndexRange。为何oracle不直接将Access Path写成IndexRange
　　下面是对T1表访问方式的描述
　　Table: T1 Alias: T1
　　Card: Original: 999.000000 Rounded: 99 Computed: 99.00 Non Adjusted: 99.00
　　Access Path: TableScan
　　Cost: 2.01 Resp: 2.01 Degree: 0
　　Cost_io: 2.00 Cost_cpu: 216023
　　Resp_io: 2.00 Resp_cpu: 216023
　　Best:: AccessPath: TableScan
　　Cost: 2.01 Degree: 1 Resp: 2.01 Card: 99.00 Bytes: 0
　　由于我们没有在T1上创建索引因此对于T1表的访问只有TableScan全表扫描一种方式
　　下面是T与T1表的关联统计最终分析出关联最小的开销作为最终的执行计划
　　OPTIMIZER STATISTICS AND COMPUTATIONS
　　***************************************
　　GENERAL PLANS
　　***************************************
　　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: 2.01 Resp: 2.01 Degree: 1 Bytes: 17
　　Access path analysis for T
　　Inner table: T Alias: T
　　Access Path: TableScan
　　NL Join: Cost: 3082.41 Resp: 3082.41 Degree: 1
　　Cost_io: 3034.00 Cost_cpu: 1543578772
　　Resp_io: 3034.00 Resp_cpu: 1543578772
　　Access Path: index (index (FFS))

　　Index:>　　resc_io: 44.43 resc_cpu: 13734996
　　ix_sel: 0.000000 ix_sel_with_filters: 1.000000
　　Inner table: T Alias: T
　　Access Path: index (FFS)
　　NL Join: Cost: 4443.65 Resp: 4443.65 Degree: 1
　　Cost_io: 4401.00 Cost_cpu: 1359980643
　　Resp_io: 4401.00 Resp_cpu: 1359980643
　　kkofmx: index filter:"T"."ID"　　resc_io: 1.00 resc_cpu: 8171
　　ix_sel: 0.000014 ix_sel_with_filters: 0.000000
　　***** Logdef predicate Adjustment ******
　　Final IO cst 0.00 , CPU cst 50.00
　　***** End Logdef Adjustment ******
　　NL Join : Cost: 101.03 Resp: 101.03 Degree: 1
　　Cost_io: 101.00 Cost_cpu: 1029945
　　Resp_io: 101.00 Resp_cpu: 1029945
　　Best NL cost: 101.03  --nested loops join 代价是101.03
　　resc: 101.03 resc_io: 101.00 resc_cpu: 1029945
　　resp: 101.03 resp_io: 101.00 resc_cpu: 1029945
　　Join Card: 98.011326 = = outer (99.000000) * inner (99.001339) * sel (0.010000)
　　Join Card - Rounded: 98 Computed: 98.01
　　Outer table: T1 Alias: T1
　　resc: 2.01 card 99.00 bytes: 17 deg: 1 resp: 2.01
　　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>　　Degree:    1

　　Blocks to Sort: 1 Row>　　Initial runs: 1 Merge passes: 0 IO Cost / pass:   0
　　Total IO sort cost: 0  Total CPU sort cost: 31913716
　　Total Temp space used: 0
　　SORT ressource   Sort statistics

　　Sort>　　Degree:    1

　　Blocks to Sort: 1 Row>　　Initial runs: 1 Merge passes: 0 IO Cost / pass:   0
　　Total IO sort cost: 0  Total CPU sort cost: 31913716
　　Total Temp space used: 0
　　SM join: Resc: 6.01 Resp: 6.01 [multiMatchCost=0.00]
　　SM Join （二）
　　SM cost: 6.01 --Sort merge join 的代价是6.01
　　resc: 6.01 resc_io: 4.00 resc_cpu: 64077698
　　resp: 6.01 resp_io: 4.00 resp_cpu: 64077698
　　Outer table: T1 Alias: T1
　　resc: 2.01 card 99.00 bytes: 17 deg: 1 resp: 2.01
　　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.50 #ptns: 1
　　hash_area: 124 (max=7731) buildfrag: 1 probefrag: 1 ppasses: 1
　　hash_area: 124 (max=7731) buildfrag: 1 probefrag: 1 ppasses: 1
　　Hash join: Resc: 4.51 Resp: 4.51 [multiMatchCost=0.00]
　　HA Join （三）
　　HA cost: 4.51 --Hash join的代价是4.51
　　resc: 4.51 resc_io: 4.00 resc_cpu: 16217089
　　resp: 4.51 resp_io: 4.00 resp_cpu: 16217089
　　Best:: JoinMethod: Hash --第一种关联花费最小的是Hash join
　　Cost: 4.51 Degree: 1 Resp: 4.51 Card: 98.01 Bytes: 22
　　***********************
　　Best so far: Table#: 0 cost: 2.0068 card: 99.0000 bytes: 1683
　　Table#: 1 cost: 4.5086 card: 98.0113 bytes: 2156
　　***********************
　　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: 57.67 Resp: 57.67 Degree: 1
　　Cost_io: 57.00 Cost_cpu: 21420508
　　Resp_io: 57.00 Resp_cpu: 21420508
　　Best NL cost: 57.67
　　resc: 57.67 resc_io: 57.00 resc_cpu: 21420508
　　resp: 57.67 resp_io: 57.00 resc_cpu: 21420508
　　Join Card: 98.011326 = = outer (99.001339) * 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: 2.01 card: 99.00 bytes: 17 deg: 1 resp: 2.01
　　using dmeth: 2 #groups: 1
　　SORT ressource   Sort statistics

　　Sort>　　Degree:    1

　　Blocks to Sort: 1 Row>　　Initial runs: 1 Merge passes: 0 IO Cost / pass:   0
　　Total IO sort cost: 0  Total CPU sort cost: 31913716
　　Total Temp space used: 0
　　SM join: Resc: 5.01 Resp: 5.01 [multiMatchCost=0.00]
　　SM Join （二）
　　SM cost: 5.01
　　resc: 5.01 resc_io: 4.00 resc_cpu: 32163982
　　resp: 5.01 resp_io: 4.00 resp_cpu: 32163982
　　Outer table: T Alias: T
　　resc: 2.00 card 99.00 bytes: 5 deg: 1 resp: 2.00
　　Inner table: T1 Alias: T1
　　resc: 2.01 card: 99.00 bytes: 17 deg: 1 resp: 2.01
　　using dmeth: 2 #groups: 1
　　Cost per ptn: 0.50 #ptns: 1
　　hash_area: 124 (max=7731) buildfrag: 1 probefrag: 1 ppasses: 1
　　Hash join: Resc: 4.51 Resp: 4.51 [multiMatchCost=0.00]
　　HA Join （三）
　　HA cost: 4.51
　　resc: 4.51 resc_io: 4.00 resc_cpu: 16217089
　　resp: 4.51 resp_io: 4.00 resp_cpu: 16217089
　　Join order aborted: cost > best plan cost
　　***********************
　　2中关联方式统计如下：
　　T1关联T
　　nested join： 101.03  resc_cpu: 1029945
　　sort merge join：6.01  resc_cpu: 64077698
　　Hash join：4.51   resc_cpu: 16217089
　　T关联T1
　　nested join：57.67resc_cpu: 21420508
　　nested join：5.01resc_cpu: 32163982
　　nested join：4.51resc_cpu: 16217089
　　下面是最终关联计算开销的结果Best join order: 1，从上面的结果看出2种关联中最优的都是Hash join，碰巧这个两个hash join开销一样，oracle选择第一种关联方式
　　Number of join permutations tried: 2
　　*********************************
　　Consider using bloom filter between T1[T1] and T[T]
　　kkoBloomFilter: join (lcdn:99 rcdn:99 jcdn:98 limit:4901)
　　Computing bloom ndv for creator:T1[T1] ccdn:99.0 and user:T[T] ucdn:99.0
　　kkopqComputeBloomNdv: predicate (bndv:73924 ndv:100) and (bndv:999 ndv:99)
　　kkopqComputeBloomNdv: pred cnt:2 ndv:99 reduction:1
　　kkoBloomFilter: join ndv:0 reduction:0.999986 (limit:0.500000) rejected because distinct value ratio
　　(newjo-save) [0 1 ]
　　Trying or-Expansion on query block SEL$1 (#0)
　　Transfer Optimizer annotations for query block SEL$1 (#0)
　　id=0 frofand predicate="T1"."ID"　　--------------------------------------+-----------------------------------+
　　Predicate Information:
　　----------------------
　　1 - access("T"."ID"="T1"."ID")
　　2 - filter("T1"."ID"