Oracle中利用10053事件来分析Oracle是如何做出最终的执行计划

我们都知道Oracle从10g开始SQL语句选择什么样的执行方式,是全表扫描,还是走索引的依据是执行代价.那么我们怎么可以去看执行代价的

我们都知道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 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 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"FPD:   Current where clause predicates in SEL$1 (#0) :
         "T1"."RN"kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
predicates with check contraints: "T1"."RN"after transitive predicate generation: "T1"."RN"finally: "T1"."RN"FPD:   Following transitive predicates are generated in SEL$1 (#0) :
         "T2"."RN"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"接下来是对一些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"  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"  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"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  
============
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 |
------------------------------------------------+-----------------------------------+

，