我们来做一次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文件中使用的缩写的描述和绑定变量的使用:
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)) 索引快速扫描 Cos