Oracle 10g 10053事件

文章由LinuxBoy分享于2019-03-30 01:03:29热评（486）

Oracle 10g 10053事件

10053事件
你是否想知道一句sql语句如何执行，它是否走索引，是否采用不同得驱动表，是否用nestloop join,hash join…..?这一切对你是否很神秘呢？或许你会说 execution plan 能看到这些东西，但是你是否清楚 execution plan 是如何得到？这篇文章就是给出了隐藏在 execution plan 底下的具体实现。幸运的是，现在我们有了这样一种方法，它能10046事件一样，一步一步地将CBO做出的执行计划的整个过程演示给我们看。这个方法就是10053事件，让我们能够直接窥视这里

究竟发生了什么，10053事件依然无法再Oracle官方文档上找到任何关于它的信息。

   现在让我们来演示如何产生一个10053事件的trace文件

SQL> create table t as select rownum x from dba_objects;

Table created.

SQL> create index ind_t on t(x);

SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> create table t1 as select x,'T1' name from t where x<10000;

Table created.

SQL> create index ind_t1 on t1(x);

SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);

SQL> alter session set tracefile_identifier='jscntest53_1';

SQL> alter session set events '10053 trace name context forever,level 1' ;

Session altered.

SQL> explain plan for select t1.* from t,t1 where t.x<100 and t.x=t1.x;

Explained.

SQL> alter session set events '10053 trace name context off';

Session altered.

10053事件的使用方法和10046一样，首先给事件一个级别level，然后运行sql（或者直接使用explain plan的方式产生执行计划），最终终止事件。
在/oracle/ora10/admin/jscn/udump目录我们看到比较特殊的一个trace文件jscn_ora_20033_jscntest53_1.trc，这个就是我们要分析的trace文件

让我一起看看这些内容，10053时间不能tkprof，可以通过该名使trac文件加亮。
[oracle@GD-TEST-84 udump]$ cp jscn_ora_20124_jscntest53_2.trc jscn_ora_20124_jscntest53_2.sql
第一部分
/oracle/ora10/admin/jscn/udump/jscn_ora_20124_jscntest53_2.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
。。。。。。。。。。。。。。。。。
Predicate Move-Around (PM)

这一部分为trace文件通用的，包含了操作系统、数据库和会话的信息，这里不再累述。

从Predicate Move-Around (PM) 这个开始，进入了10053的trace信息部分，这一部分CBO主要工作是对SQL语句谓词进行分析、重写，把它改写为最符合逻辑的SQL，比如我们最初的谓语
形式（通俗讲就是我们刚开始自己写的where条件）：
"T"."X"<100 AND "T"."X"="T1"."X"
被oracle改成了
"T"."X"<100 AND "T"."X"="T1"."X" AND "T1"."X"<100

Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM:   Checking validity of predicate move-around in SEL$1 (#0).
PM:     PM bypassed: Outer query contains no views.
FPD: Considering simple filter push in SEL$1 (#0)
FPD:   Current where clause predicates in SEL$1 (#0) :
         "T"."X"<100 AND "T"."X"="T1"."X"
kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
predicates with check contraints: "T"."X"<100 AND "T"."X"="T1"."X" AND "T1"."X"<100
after transitive predicate generation: "T"."X"<100 AND "T"."X"="T1"."X" AND "T1"."X"<100
finally: "T"."X"<100 AND "T"."X"="T1"."X" AND "T1"."X"<100
FPD:   Following transitive predicates are generated in SEL$1 (#0) :
         "T1"."X"<100
apadrv-start: call(in-use=1064, alloc=16344), compile(in-use=35272, alloc=36536)
kkoqbc-start
            : call(in-use=1072, alloc=16344), compile(in-use=36488, alloc=36536)
kkoqbc-subheap (create addr=0x2b297cb0c000)
******************************************
Current SQL statement for this session:
explain plan for select t1.* from t,t1 where t.x<100 and t.x=t1.x
*******************************************

很容易看出，从逻辑上看着两个谓词是等价的，CBO把它改成成这样子，主要是为了更方便计算每一步的成本和估算cardinality（基数），比如我们在这条
sql语句中既要访问t1表中的x例，也要访问那么列，CBO就可以按照这个条件估算没搞操作的结果集（Cardinglity）。
接下来：
*******************************************
Legend
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
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
ST - star transformation
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUCSPEED - CPU Speed
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
1: no partitioning required
2: value partitioned
4: right is random (round-robin)
512: left is random (round-robin)
8: broadcast right and partition left
16: broadcast left and partition right
32: partition left using partitioning of right
64: partition right using partitioning of left
128: use hash partitioning dimension
256: use range partitioning dimension
2048: use list partitioning dimension
1024: run the join in serial
0: invalid distribution method
sel - selectivity
ptn - partition

*******************************************

推荐文章：

Oracle 10g 10053事件