读懂Oracle 10053事件
我们可以通过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
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: T1#0 T#1
***************
Now joining: 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: 0Total 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: 0Total CPU sort cost: 31913716
Total Temp space used: 0
SM join: Resc: 6.01 Resp: 6.01
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
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: T#1 T1#0
***************
Now joining: 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: 0Total CPU sort cost: 31913716
Total Temp space used: 0
SM join: Resc: 5.01 Resp: 5.01
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
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.03resc_cpu: 1029945
sort merge join：6.01resc_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 and T
kkoBloomFilter: join (lcdn:99 rcdn:99 jcdn:98 limit:4901)
Computing bloom ndv for creator:T1 ccdn:99.0 and user: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)
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"
页:
[1]