Oracle中利用10053事件来分析Oracle是如何做出最终的执行计划
我们都知道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).
索引的聚