我们都知道Oracle从10g开始SQL语句选择什么样的执行方式,是全表扫描,还是走索引的依据是执行代价.那么我们怎么可以去看执行代价的信息呢?通过10053事件可以Oracle依据的执行代价和如何做出执行计划的.如果我们发现某一条SQL语句的执行计划和想像的不一样,我们就可以去看看Oracle所使用的统计分析数据是否准确,是不是统计信息太久没有分析了,重新分析有问题的对象,最终让Oracle做出正确的执行计划。
我们来做一次10053事件的示例:
SQL> create table t1 as select rownum rn from dba_objects;
Table created.
SQL> create index ind_t1 on t1(rn);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,’t1′,cascade=>true);
PL/SQL procedure successfully completed.
SQL> create table t2 as select rn, ‘t2’ name from t1 where rn <10000;
Table created.
SQL> create index ind_t2 on t2(rn);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,’t2′,cascade=>true);
PL/SQL procedure successfully completed.
SQL> alter session set tracefile_identifier=’mysession’;
Session altered.
SQL> alter session set events ‘10053 trace name context forever,level 1’;
Session altered.
SQL> explain plan for select t2.* from t1,t2 where t1.rn <100 and t1.rn=t2.rn;
Explained.
SQL> alter session set events ‘10053 trace name context off’;
Session altered.
和上次讲SQL_TRACE的时候一样,生成的trace文件的路径是$ORACLE_BASE/admin/SID/udump目录.
与SQL_TRACE和10046事件不同的是,生成的trace文件不能用tkprof处理,只能阅读原始的trace文件.
对trace文件做一个大体的介绍:
**************************
Predicate Move-Around (PM)
**************************
SQL:******* UNPARSED QUERY IS *******
SELECT “T2″.”RN” “RN”,”T2″.”NAME” “NAME” FROM “YORKER”.”T1″ “T1″,”YORKER”.”T2″ “T2” WHERE “T1”.”RN”<100 AND "T1"."RN"="T2"."RN"
FPD: Current where clause predicates in SEL$1 (#0) :
“T1”.”RN”<100 AND "T1"."RN"="T2"."RN"
kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
predicates with check contraints: “T1”.”RN”<100 AND "T1"."RN"="T2"."RN" AND "T2"."RN"<100
after transitive predicate generation: “T1”.”RN”<100 AND "T1"."RN"="T2"."RN" AND "T2"."RN"<100
finally: “T1”.”RN”<100 AND "T1"."RN"="T2"."RN" AND "T2"."RN"<100
FPD: Following transitive predicates are generated in SEL$1 (#0) :
“T2”.”RN”<100
apadrv-start: call(in-use=340, alloc=0), compile(in-use=31192, alloc=0)
kkoqbc-start
: call(in-use=344, alloc=0), compile(in-use=31976, alloc=0)
******************************************
我们会发现Oracle会对SQL语句做一个转换,把它改成最符合Oracle处理的语句.
比如查询条件变成了 finally: “T1”.”RN”<100 AND "T1"."RN"="T2"."RN" AND "T2"."RN"<100
接下来是对一些trace文件中使用的缩写的描述和绑定变量的使用:
The following abbreviations are used by optimizer trace.
CBQT – cost-based query transformation
JPPD – join predicate push-down
FPD – filter push-down
PM – predicate move-around
CVM – complex view merging
SPJ – select-project-join
*******************************************
Peeked values of the binds in SQL statement
*******************************************
接下来是SQL用到的表,索引的统计信息,如果我们发现这个地方和实际不符,可能需要对对象做分析了.
这些信息包括了表的行数,数据块数,行的平均长度.
列平均长度,非重复的值,空值数,密度,最小值和最大值.
索引的高度,叶子块数目,每一个索引键值占据的块数(LB/K),每一个索引键值对应的表的数据块数目(DB/K).
索引的聚合因子(CLUF).
聚合因子指的是索引中键值在索引块的分布和对应的表中的数据块分布的一种关系.
当索引键值和表中数据排列顺序大致相同时,这个值就越小,当一个索引键值的数据分布在越多的表
的数据块时,这个值越大,意味着使用索引的代价越高.
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T2 Alias: T2
#Rows: 9999 #Blks: 21 AvgRowLen: 6.00
Column (#1): RN(NUMBER)
AvgLen: 4.00 NDV: 9999 Nulls: 0 Density: 1.0001e-004 Min: 1 Max: 9999
Index Stats::
Index: IND_T2 Col#: 1
LVLS: 1 #LB: 21 #DK: 9999 LB/K: 1.00 DB/K: 1.00 CLUF: 17.00
***********************
Table Stats::
Table: T1 Alias: T1
#Rows: 51060 #Blks: 86 AvgRowLen: 4.00
Column (#1): RN(NUMBER)
AvgLen: 5.00 NDV: 51060 Nulls: 0 Density: 1.9585e-005 Min: 5 Max: 51055
Index Stats::
Index: IND_T1 Col#: 1
LVLS: 1 #LB: 113 #DK: 51060 LB/K: 1.00 DB/K: 1.00 CLUF: 78.00
***************************************
接下来是对各种访问方式的代价的信息,通过比较所有的数据访问方式的代价,选择代价最小的方式
作为执行计划.
对于T1表,列出了
Access Path: TableScan 全表扫描 Cost: 22.86
Access Path: index (index (FFS)) 索引快速扫描 Cost: 27.63
Access Path: index (IndexOnly)只访问索引.(因为只用到了索引数据RN) Cost: 2.01
可以看到只访问索引的代价最小.
对于T2表有如下访问方式:(最后会走index (RangeScan))
Access Path: TableScan Cost: 6.37
Access Path: index (RangeScan) Cost: 3.01
然后再是对关联顺序的考虑:
T1关联T2
NL Join(nested loops join) Cost: 2051.15
SM Join(Sort merge join) SM cost: 6.02
HA Join (Hash join) HA cost: 5.52
T2关联T1
NL Join(nested loops join) Cost: 475.12
SM Join(Sort merge join) SM cost: 6.02
HA Join (Hash join) HA cost: 5.52
T1关联T2的CPU代价更小,最后会走T1关联T2.
SINGLE TABLE ACCESS PATH
Table: T1 Alias: T1
Card: Original: 51060 Rounded: 95 Computed: 95.02 Non Adjusted: 95.02
Access Path: TableScan
Cost: 22.86 Resp: 22.86 Degree: 0
Cost_io: 21.00 Cost_cpu: 10824444
Resp_io: 21.00 Resp_cpu: 10824444
Access Path: index (index (FFS))
Index: IND_T1
resc_io: 26.00 resc_cpu: 9484923
ix_sel: 0.0000e+000 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 27.63 Resp: 27.63 Degree: 1
Cost_io: 26.00 Cost_cpu: 9484923
Resp_io: 26.00 Resp_cpu: 9484923
Access Path: index (IndexOnly)
Index: IND_T1
resc_io: 2.00 resc_cpu: 33443
ix_sel: 0.0018609 ix_sel_with_filters: 0.0018609
Cost: 2.01 Resp: 2.01 Degree: 1
Best:: AccessPath: IndexRange Index: IND_T1
Cost: 2.01 Degree: 1 Resp: 2.01 Card: 95.02 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
Table: T2 Alias: T2
Card: Original: 9999 Rounded: 99 Computed: 99.01 Non Adjusted: 99.01
Access Path: TableScan
Cost: 6.37 Resp: 6.37 Degree: 0
Cost_io: 6.00 Cost_cpu: 2151330
Resp_io: 6.00 Resp_cpu: 2151330
Access Path: index (RangeScan)
Index: IND_T2
resc_io: 3.00 resc_cpu: 58364
ix_sel: 0.009902 ix_sel_with_filters: 0.009902
Cost: 3.01 Resp: 3.01 Degree: 1
Best:: AccessPath: IndexRange Index: IND_T2
Cost: 3.01 Degree: 1 Resp: 3.01 Card: 99.01 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]: T1[T1]#0 T2[T2]#1
***************
Now joining: T2[T2]#1
***************
NL Join
Outer table: Card: 95.02 Cost: 2.01 Resp: 2.01 Degree: 1 Bytes: 4
Inner table: T2 Alias: T2
Access Path: TableScan
NL Join: Cost: 475.12 Resp: 475.12 Degree: 0
Cost_io: 440.00 Cost_cpu: 204409816
Resp_io: 440.00 Resp_cpu: 204409816
kkofmx: index filter:”T2″.”RN”<100 AND "T1"."RN"<100
Access Path: index (AllEqJoinGuess)
Index: IND_T2
resc_io: 2.00 resc_cpu: 15463
ix_sel: 1.0001e-004 ix_sel_with_filters: 9.9030e-007
NL Join (ordered): Cost: 115.77 Resp: 115.77 Degree: 1
Cost_io: 115.60 Cost_cpu: 950127
Resp_io: 115.60 Resp_cpu: 950127
Best NL cost: 115.77
resc: 115.77 resc_io: 115.60 resc_cpu: 950127
resp: 115.77 resp_io: 115.60 resp_cpu: 950127
Join Card: 94.08 = outer (95.02) * inner (99.01) * sel (0.01)
Join Card – Rounded: 94 Computed: 94.08
SM Join
Outer table:
resc: 2.01 card 95.02 bytes: 4 deg: 1 resp: 2.01
Inner table: T2 Alias: T2
resc: 3.01 card: 99.01 bytes: 6 deg: 1 resp: 3.01
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 231 Area size: 202752 Max Area size: 40684544
Degree: 1
Blocks to Sort: 1 Row size: 17 Total Rows: 99
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5849269
Total Temp space used: 0
SM join: Resc: 6.02 Resp: 6.02 [multiMatchCost=0.00]
SM cost: 6.02
resc: 6.02 resc_io: 5.00 resc_cpu: 5941076
resp: 6.02 resp_io: 5.00 resp_cpu: 5941076
HA Join
Outer table:
resc: 2.01 card 95.02 bytes: 4 deg: 1 resp: 2.01
Inner table: T2 Alias: T2
resc: 3.01 card: 99.01 bytes: 6 deg: 1 resp: 3.01
using dmeth: 2 #groups: 1
Cost per ptn: 0.50 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 5.52 Resp: 5.52 [multiMatchCost=0.00]
HA cost: 5.52
resc: 5.52 resc_io: 5.00 resc_cpu: 3025807
resp: 5.52 resp_io: 5.00 resp_cpu: 3025807
Best:: JoinMethod: Hash
Cost: 5.52 Degree: 1 Resp: 5.52 Card: 94.08 Bytes: 10
***********************
Best so far: Table#: 0 cost: 2.0057 card: 95.0186 bytes: 380
Table#: 1 cost: 5.5199 card: 94.0778 bytes: 940
***********************
Join order[2]: T2[T2]#1 T1[T1]#0
***************
Now joining: T1[T1]#0
***************
NL Join
Outer table: Card: 99.01 Cost: 3.01 Resp: 3.01 Degree: 1 Bytes: 6
Inner table: T1 Alias: T1
Access Path: TableScan
NL Join: Cost: 2051.15 Resp: 2051.15 Degree: 0
Cost_io: 1867.00 Cost_cpu: 1071678304
Resp_io: 1867.00 Resp_cpu: 1071678304
Access Path: index (index (FFS))
Index: IND_T1
resc_io: 24.74 resc_cpu: 9484923
ix_sel: 0.0000e+000 ix_sel_with_filters: 1
Inner table: T1 Alias: T1
Access Path: index (FFS)
NL Join: Cost: 2613.36 Resp: 2613.36 Degree: 0
Cost_io: 2452.00 Cost_cpu: 939065714
Resp_io: 2452.00 Resp_cpu: 939065714
kkofmx: index filter:”T1″.”RN”<100
Access Path: index (AllEqJoinGuess)
Index: IND_T1
resc_io: 1.00 resc_cpu: 8171
ix_sel: 1.9585e-005 ix_sel_with_filters: 3.6446e-008
NL Join (ordered): Cost: 102.15 Resp: 102.15 Degree: 1
Cost_io: 102.00 Cost_cpu: 872287
Resp_io: 102.00 Resp_cpu: 872287
Best NL cost: 102.15
resc: 102.15 resc_io: 102.00 resc_cpu: 872287
resp: 102.15 resp_io: 102.00 resp_cpu: 872287
Join Card: 94.08 = outer (99.01) * inner (95.02) * sel (0.01)
Join Card – Rounded: 94 Computed: 94.08
SM Join
Outer table:
resc: 3.01 card 99.01 bytes: 6 deg: 1 resp: 3.01
Inner table: T1 Alias: T1
resc: 2.01 card: 95.02 bytes: 4 deg: 1 resp: 2.01
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 231 Area size: 202752 Max Area size: 40684544
Degree: 1
Blocks to Sort: 1 Row size: 15 Total Rows: 95
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 5847820
Total Temp space used: 0
SM join: Resc: 6.02 Resp: 6.02 [multiMatchCost=0.00]
SM cost: 6.02
resc: 6.02 resc_io: 5.00 resc_cpu: 5939627
resp: 6.02 resp_io: 5.00 resp_cpu: 5939627
HA Join
Outer table:
resc: 3.01 card 99.01 bytes: 6 deg: 1 resp: 3.01
Inner table: T1 Alias: T1
resc: 2.01 card: 95.02 bytes: 4 deg: 1 resp: 2.01
using dmeth: 2 #groups: 1
Cost per ptn: 0.50 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 5.52 Resp: 5.52 [multiMatchCost=0.00]
HA cost: 5.52
resc: 5.52 resc_io: 5.00 resc_cpu: 3026007
resp: 5.52 resp_io: 5.00 resp_cpu: 3026007
Join order aborted: cost > best plan cost
***********************
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations tried: 2
*********************************
(newjo-save) [1 0 ]
Final – All Rows Plan: Best join order: 1
Cost: 5.5199 Degree: 1 Card: 94.0000 Bytes: 940
Resc: 5.5199 Resc_io: 5.0000 Resc_cpu: 3025807
Resp: 5.5199 Resp_io: 5.0000 Resc_cpu: 3025807
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT “T2″.”RN” “RN”,”T2″.”NAME” “NAME” FROM “YORKER”.”T1″ “T1″,”YORKER”.”T2″ “T2” WHERE “T1”.”RN”<100 AND "T2"."RN"<100 AND "T1"."RN"="T2"."RN"
kkoqbc-end
: call(in-use=31732, alloc=0), compile(in-use=33436, alloc=0)
apadrv-end: call(in-use=31732, alloc=0), compile(in-use=34024, alloc=0)
sql_id=ar0vn3xs804bs.
Current SQL statement for this session:
explain plan for select t2.* from t1,t2 where t1.rn <100 and t1.rn=t2.rn============
Plan Table
============
————————————————+———————————–+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
————————————————+———————————–+
| 0 | SELECT STATEMENT | | | | 6 | |
| 1 | HASH JOIN | | 94 | 940 | 6 | 00:00:01 |
| 2 | INDEX RANGE SCAN | IND_T1 | 95 | 380 | 2 | 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | T2 | 99 | 594 | 3 | 00:00:01 |
| 4 | INDEX RANGE SCAN | IND_T2 | 99 | | 2 | 00:00:01 |
————————————————+———————————–+
，