mysql 10053_Oracle 10g 10053事件

你是否想知道一句sql语句如何执行，它是否走索引，是否采用不同得驱动表，是否用nestloop join,hash joinhellip;..?这一切对你是

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

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

本文原创发布php中文网，转载请注明出处，感谢您的尊重！