转载

Oracle事件之10053 跟踪的trace文件相关解释

一. 10053事件

　　当一个SQL出现性能问题的时候，可以使用SQL_TRACE 或者 10046事件来跟踪SQL. 通过生成的trace来了解SQL的执行过程。我们在查看一条SQL的执行计划的时候，只能看到CBO 最终告诉我们的执行计划结果，但是不知道CBO 是根据什么来做的。如果遇到了执行计划失真，如：一个SQL语句，很明显oracle应该使用索引，但是执行计划却没有使用索引。无法进行分析判断。

　　而10053事件就提供了这样的功能。它产生的trace文件提供了Oracle如何选择执行计划，为什么会得到这样的执行计划信息。

　　10053事件生成trace文件目录和SQL_TRACE一样。
　　在Oracle 10g中，SQL_TRACE生成的trace文件默认路劲是$ORACLE_BASE/admin/SID/udump.
　　在Oracle 11g，trace 默认路径在：$ORACLE_BASE/diag/rdbms/orcl/orcl/trace目录下

　　对于10053事件的trace文件，我们只能直接阅读原始的trace文件，不能使用tkprof工具来处理，tkprof工具只能用来处理sql_trace 和 10046事件产生的trace文件。

10053事件有两个级别：

Level 2：2级是1级的一个子集，它包含以下内容：

Column statistics
Single Access Paths
Join Costs
Table Joins Considered
Join Methods Considered (NL/MS/HA)

Level 1： 1级比2级更详细，它包含2级的所有内容，在加如下内容：

Parameters used by the optimizer
Index statistics

1.1启用10053事件：

ALTER SESSION SET EVENTS='10053 trace name context forever, level 1'; ALTER SESSION SET EVENTS='10053 trace name context forever, level 2';

1.2关闭10053事件：

ALTER SESSION SET EVENTS '10053 trace name context off';

说明：

（1）sqlplus中打开autotrace看到的执行计划实际上是用explain plan 命令得到的，explain plan 命令不会进行bind peeking。应该通过v$sql_plan查看SQL的真实的执行计划。

（2）10053只对CBO有效，而且如果一个sql语句已经解析过，就不会产生新的trace信息。

二．实验10053事件：

1.设定当前的trace 文件

　　1.1 设定trace 文件名称

SQL> alter session set tracefile_identifier='10053事件';

会话已更改。

　　设置标识的目的就是方便我们查找生成的trace文件。我们只需要在trace目录查找文件名里带有标识的文件即可。

　　1.2直接用如下SQL直接查出，当前的trace文件名。

SELECT d.VALUE || '/' || LOWER (RTRIM (i.INSTANCE, CHR (0))) || '_ora_' || p.spid || '.trc' AS "trace_file_name" FROM (SELECT p.spid FROM v$mystat m, v$session s, v$process p WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
         (SELECT t.INSTANCE FROM v$thread t, v$parameter v WHERE v.NAME = 'thread' AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
         (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;

2.启动10053事件

SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

3.执行事务

SQL> select * from pub_user u, pub_department dept where u.department_id = dept.department_id;

SQL>Explain plan for select * from pub_user u, pub_department dept where u.department_id = dept.department_id;

4.关闭10053事件

SQL> ALTER SESSION SET EVENTS '10053 trace name context off';

三．查看生成的trace文件

　　在此之前设置了标识，所以直接进入trace目录，找到含有 ‘10053事件’标识的trace 文件。

Trace file D:/oracle/product/10.2.0/admin/dw/udump/10053事件.trc

四、10053事件内容解析

1. Predicate Move-Around (PM)(对SQL语句的谓词进行分析、重写，把它改为最符合逻辑的SQL语句)

2. 解释trace文件用到的一些缩写的指标定义

3. Peeked values of the binds in SQL statement(绑定变量的描述)

4. Bug Fix Control Environment(一些修复的bug信息)

5. PARAMETERS WITH DEFAULT VALUES(性能相关的初始化参数)

6. BASE STATISTICAL INFORMATION(SQL引用对象的基本信息)

7. CBO计算每个对象单独访问的代价

8. CBO计算列出两个表关联方式，并计算出每一种关联方式的代价，最终选择最小的cost

五、实验：10053事件的妙用

　　在我们写sql时，一条明显可以查询出来数据的语句，为什么我们写完之后却不返回数据？这时，10053可以解答我们的疑问。
　　见如下order by 查不出数据实验：

---10.2.0.1版本加了order by查不出数据实验 Drop table test1 purge; Drop table test2 purge; create table test1 (id number(20),name varchar2(20)); insert into test1 values (1,'A'); insert into test1 values (2,'A'); insert into test1 values (3,'A'); insert into test1 values (4,'A'); insert into test1 values (5,'B'); insert into test1 values (6,'B'); insert into test1 values (7,'C'); insert into test1 values (8,'C'); insert into test1 values (9,'C'); insert into test1 values (10,'C'); create table test2 (id number(20),name varchar2(20)); insert into test2 values (1,'A'); insert into test2 values (2,'A'); insert into test2 values (3,'A'); insert into test2 values (4,'A'); insert into test2 values (5,'A'); insert into test2 values (6,'A'); insert into test2 values (7,'A'); insert into test2 values (8,'B'); insert into test2 values (9,'C'); insert into test2 values (10,'C');

SELECT * FROM (SELECT * FROM (SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM FROM (select test2.* from test2,
                               (SELECT t.id, t.name FROM test1 T WHERE T.id = (SELECT MAX(T1.id) FROM test1 T1 WHERE T.name = T1.name)) test1 where test2.name = test1.name order by test2.name ---加上order by就没有数据   ) INNER_TABLE) WHERE OUTER_TABLE_ROWNUM <= 18) OUTER_TABLE WHERE OUTER_TABLE_ROWNUM > 0;

SELECT * FROM (SELECT * FROM (SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM FROM (select test2.* from test2,
                               (SELECT t.id, t.name FROM test T WHERE T.id in (SELECT MAX(T1.id) FROM test T1 group by name)) test1 where test2.name = test1.name order by test2.name) INNER_TABLE) WHERE OUTER_TABLE_ROWNUM <= 18) OUTER_TABLE WHERE OUTER_TABLE_ROWNUM > 0;

10053和10046事件的用法是一样的，需要注意的是这个trace文件不能用tkprof工具处理，tkprof工具只能处理10046和sql_trace文件，下面是刚才生成的trace文件内容

这里从BASE STATISTICAL INFORMATION开始看

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

BASE STATISTICAL INFORMATION

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

Table Stats::

Table: T1 Alias: T1 (NOT ANALYZED)

#Rows: 164 #Blks: 2 AvgRowLen: 100.00

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

Table Stats::

Table: T Alias: T

#Rows: 73924 #Blks: 113 AvgRowLen: 5.00

Index Stats::

Index: IDX_T Col#: 1

LVLS: 1 #LB: 164 #DK: 73924 LB/K: 1.00 DB/K: 1.00 CLUF: 113.00

Access path analysis for T

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

这一部分是sql语句中用到的对象基本信息，包括关联表和索引信息，我们看到这里列出了关于这个查询的三个对象信息，表T,T1和索引IDX_T，表信息包含了表的行数（#Rows），数据块数（#Blks），平均行长（AvgRowLen）索引项部分列出了索引叶块数（#LB），每个索引键值占据的数据块数（LB/K），每个索引键值对应的表中数据块数（DB/K）索引的聚合因子（CLUF）,当索引的键值对应的数据库越大，索引的聚合因子就越大，越不利用索引的使用。

接下来的部分是CBO计算的每个对象单独访问的代价

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

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for T[T]

Table: T Alias: T

Card: Original: 73924.000000 Rounded: 99 Computed: 99.00 Non Adjusted: 99.00

Access Path: TableScan

Cost: 32.49 Resp: 32.49 Degree: 0

Cost_io: 32.00 Cost_cpu: 15589523

Resp_io: 32.00 Resp_cpu: 15589523

Access Path: index (index (FFS))

Index: IDX_T

resc_io: 46.00 resc_cpu: 13734996

ix_sel: 0.000000 ix_sel_with_filters: 1.000000

Access Path: index (FFS)

Cost: 46.43 Resp: 46.43 Degree: 1

Cost_io: 46.00 Cost_cpu: 13734996

Resp_io: 46.00 Resp_cpu: 13734996

Access Path: index (IndexOnly)

Index: IDX_T

resc_io: 2.00 resc_cpu: 34243

ix_sel: 0.001339 ix_sel_with_filters: 0.001339

Cost: 2.00 Resp: 2.00 Degree: 1

Best:: AccessPath: IndexRange

Index: IDX_T

Cost: 2.00 Degree: 1 Resp: 2.00 Card: 99.00 Bytes: 0

Access path analysis for T1

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

这里有两个指标对于我们分析执行计划比较重要：

Card: Original: 73924.000000

原纪录数，也就是操作数据原的输入记录数，在这里就是表的实际行数73294

Card: Rounded： 99

输出的记录数，CBO计算出通过条件过滤，预计得到的行数99

通过这里我们可以看出对于T表CBO给我们计算出了3种访问方式

全表扫描

Access Path: TableScan 开销：Cost: 32.49

索引快速扫描

Access Path: index (index (FFS)) 开销：Cost: 46.43

单独访问索引

Access Path: index (IndexOnly) 开销：Cost: 2.00

可以看出，单独访问索引的方式是代价最低的，因此CBO得出下来结论

Best:: AccessPath: IndexRange

Index: IDX_T

Cost: 2.00 Degree: 1 Resp: 2.00 Card: 99.00 Bytes: 0

这里我不明白上面写的是IndexOnly，为什么最后结论改写成IndexRange。为何oracle不直接将Access Path写成IndexRange

下面是对T1表访问方式的描述

Table: T1 Alias: T1

Card: Original: 999.000000 Rounded: 99 Computed: 99.00 Non Adjusted: 99.00

Access Path: TableScan

Cost: 2.01 Resp: 2.01 Degree: 0

Cost_io: 2.00 Cost_cpu: 216023

Resp_io: 2.00 Resp_cpu: 216023

Best:: AccessPath: TableScan

Cost: 2.01 Degree: 1 Resp: 2.01 Card: 99.00 Bytes: 0

由于我们没有在T1上创建索引因此对于T1表的访问只有TableScan全表扫描一种方式

下面是T与T1表的关联统计最终分析出关联最小的开销作为最终的执行计划

OPTIMIZER STATISTICS AND COMPUTATIONS

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

GENERAL PLANS

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

Considering cardinality-based initial join order.

Permutations for Starting Table :0

Join order[1]: T1[T1]#0 T[T]#1

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

Now joining: T[T]#1

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

NL Join （一）

Outer table: Card: 99.00 Cost: 2.01 Resp: 2.01 Degree: 1 Bytes: 17

Access path analysis for T

Inner table: T Alias: T

Access Path: TableScan

NL Join: Cost: 3082.41 Resp: 3082.41 Degree: 1

Cost_io: 3034.00 Cost_cpu: 1543578772

Resp_io: 3034.00 Resp_cpu: 1543578772

Access Path: index (index (FFS))

Index: IDX_T

resc_io: 44.43 resc_cpu: 13734996

ix_sel: 0.000000 ix_sel_with_filters: 1.000000

Inner table: T Alias: T

Access Path: index (FFS)

NL Join: Cost: 4443.65 Resp: 4443.65 Degree: 1

Cost_io: 4401.00 Cost_cpu: 1359980643

Resp_io: 4401.00 Resp_cpu: 1359980643

kkofmx: index filter:"T"."ID"<100

Access Path: index (AllEqJoinGuess)

Index: IDX_T

resc_io: 1.00 resc_cpu: 8171

ix_sel: 0.000014 ix_sel_with_filters: 0.000000

***** Logdef predicate Adjustment ******

Final IO cst 0.00 , CPU cst 50.00

***** End Logdef Adjustment ******

NL Join : Cost: 101.03 Resp: 101.03 Degree: 1

Cost_io: 101.00 Cost_cpu: 1029945

Resp_io: 101.00 Resp_cpu: 1029945

Best NL cost: 101.03 --nested loops join 代价是101.03

resc: 101.03 resc_io: 101.00 resc_cpu: 1029945

resp: 101.03 resp_io: 101.00 resc_cpu: 1029945

Join Card: 98.011326 = = outer (99.000000) * inner (99.001339) * sel (0.010000)

Join Card - Rounded: 98 Computed: 98.01

Outer table: T1 Alias: T1

resc: 2.01 card 99.00 bytes: 17 deg: 1 resp: 2.01

Inner table: T Alias: T

resc: 2.00 card: 99.00 bytes: 5 deg: 1 resp: 2.00

using dmeth: 2 #groups: 1

SORT ressource Sort statistics

Sort width: 179 Area size: 157696 Max Area size: 31666176

Degree: 1

Blocks to Sort: 1 Row size: 29 Total Rows: 99

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost: 0 Total CPU sort cost: 31913716

Total Temp space used: 0

SORT ressource Sort statistics

Sort width: 179 Area size: 157696 Max Area size: 31666176

Degree: 1

Blocks to Sort: 1 Row size: 16 Total Rows: 99

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost: 0 Total CPU sort cost: 31913716

Total Temp space used: 0

SM join: Resc: 6.01 Resp: 6.01 [multiMatchCost=0.00]

SM Join （二）

SM cost: 6.01 --Sort merge join 的代价是6.01

resc: 6.01 resc_io: 4.00 resc_cpu: 64077698

resp: 6.01 resp_io: 4.00 resp_cpu: 64077698

Outer table: T1 Alias: T1

resc: 2.01 card 99.00 bytes: 17 deg: 1 resp: 2.01

Inner table: T Alias: T

resc: 2.00 card: 99.00 bytes: 5 deg: 1 resp: 2.00

using dmeth: 2 #groups: 1

Cost per ptn: 0.50 #ptns: 1

hash_area: 124 (max=7731) buildfrag: 1 probefrag: 1 ppasses: 1

Hash join: Resc: 4.51 Resp: 4.51 [multiMatchCost=0.00]

HA Join （三）

HA cost: 4.51 --Hash join的代价是4.51

resc: 4.51 resc_io: 4.00 resc_cpu: 16217089

resp: 4.51 resp_io: 4.00 resp_cpu: 16217089

Best:: JoinMethod: Hash --第一种关联花费最小的是Hash join

Cost: 4.51 Degree: 1 Resp: 4.51 Card: 98.01 Bytes: 22

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

Best so far: Table#: 0 cost: 2.0068 card: 99.0000 bytes: 1683

Table#: 1 cost: 4.5086 card: 98.0113 bytes: 2156

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

Join order[2]: T[T]#1 T1[T1]#0

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

Now joining: T1[T1]#0

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

NL Join （一）

Outer table: Card: 99.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 5

Access path analysis for T1

Inner table: T1 Alias: T1

Access Path: TableScan

NL Join: Cost: 57.67 Resp: 57.67 Degree: 1

Cost_io: 57.00 Cost_cpu: 21420508

Resp_io: 57.00 Resp_cpu: 21420508

Best NL cost: 57.67

resc: 57.67 resc_io: 57.00 resc_cpu: 21420508

resp: 57.67 resp_io: 57.00 resc_cpu: 21420508

Join Card: 98.011326 = = outer (99.001339) * inner (99.000000) * sel (0.010000)

Join Card - Rounded: 98 Computed: 98.01

Outer table: T Alias: T

resc: 2.00 card 99.00 bytes: 5 deg: 1 resp: 2.00

Inner table: T1 Alias: T1

resc: 2.01 card: 99.00 bytes: 17 deg: 1 resp: 2.01

using dmeth: 2 #groups: 1

SORT ressource Sort statistics

Sort width: 179 Area size: 157696 Max Area size: 31666176

Degree: 1

Blocks to Sort: 1 Row size: 29 Total Rows: 99

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost: 0 Total CPU sort cost: 31913716

Total Temp space used: 0

SM join: Resc: 5.01 Resp: 5.01 [multiMatchCost=0.00]

SM Join （二）

SM cost: 5.01

resc: 5.01 resc_io: 4.00 resc_cpu: 32163982

resp: 5.01 resp_io: 4.00 resp_cpu: 32163982

Outer table: T Alias: T

resc: 2.00 card 99.00 bytes: 5 deg: 1 resp: 2.00

Inner table: T1 Alias: T1

resc: 2.01 card: 99.00 bytes: 17 deg: 1 resp: 2.01

using dmeth: 2 #groups: 1

Cost per ptn: 0.50 #ptns: 1

hash_area: 124 (max=7731) buildfrag: 1 probefrag: 1 ppasses: 1

Hash join: Resc: 4.51 Resp: 4.51 [multiMatchCost=0.00]

HA Join （三）

HA cost: 4.51

resc: 4.51 resc_io: 4.00 resc_cpu: 16217089

resp: 4.51 resp_io: 4.00 resp_cpu: 16217089

Join order aborted: cost > best plan cost

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

2中关联方式统计如下：

T1关联T

nested join： 101.03 resc_cpu: 1029945

sort merge join：6.01 resc_cpu: 64077698

Hash join：4.51 resc_cpu: 16217089

T关联T1

nested join：57.67resc_cpu: 21420508

nested join：5.01resc_cpu: 32163982

nested join：4.51resc_cpu: 16217089

下面是最终关联计算开销的结果Best join order: 1，从上面的结果看出2种关联中最优的都是Hash join，碰巧这个两个hash join开销一样，oracle选择第一种关联方式

Number of join permutations tried: 2

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

Consider using bloom filter between T1[T1] and T[T]

kkoBloomFilter: join (lcdn:99 rcdn:99 jcdn:98 limit:4901)

Computing bloom ndv for creator:T1[T1] ccdn:99.0 and user:T[T] ucdn:99.0

kkopqComputeBloomNdv: predicate (bndv:73924 ndv:100) and (bndv:999 ndv:99)

kkopqComputeBloomNdv: pred cnt:2 ndv:99 reduction:1

kkoBloomFilter: join ndv:0 reduction:0.999986 (limit:0.500000) rejected because distinct value ratio

(newjo-save) [0 1 ]

Trying or-Expansion on query block SEL$1 (#0)

Transfer Optimizer annotations for query block SEL$1 (#0)

id=0 frofand predicate="T1"."ID"<100

id=0 frofkksm[i] (sort-merge/hash) predicate="T"."ID"="T1"."ID"

id=0 frosand (sort-merge/hash) predicate="T"."ID"="T1"."ID"

id=0 frofkke[i] (index stop key) predicate="T"."ID"<100

Final cost for query block SEL$1 (#0) - All Rows Plan:

Best join order: 1

Cost: 4.5086 Degree: 1 Card: 98.0000 Bytes: 2156

Resc: 4.5086 Resc_io: 4.0000 Resc_cpu: 16217089

Resp: 4.5086 Resp_io: 4.0000 Resc_cpu: 16217089

kkoqbc-subheap (delete addr=0x7ff58e2dfa50, in-use=29224, alloc=41296)

kkoqbc-end:

call(in-use=19752, alloc=82024), compile(in-use=67328, alloc=68488), execution(in-use=89616, alloc=93504)

kkoqbc: finish optimizing query block SEL$1 (#0)

下面是最终算出的执行计划结果

============

Plan Table

============

--------------------------------------+-----------------------------------+

--------------------------------------+-----------------------------------+

| 0 | SELECT STATEMENT | | | | 5 | |

| 1 | HASH JOIN | | 98 | 2156 | 5 | 00:00:01 |

| 2 | TABLE ACCESS FULL | T1 | 99 | 1683 | 2 | 00:00:01 |

| 3 | INDEX RANGE SCAN | IDX_T | 99 | 495 | 2 | 00:00:01 |

--------------------------------------+-----------------------------------+

Predicate Information:

----------------------

1 - access("T"."ID"="T1"."ID")

2 - filter("T1"."ID"<100)

3 - access("T"."ID"<100)

注：trace文件头部和尾部省略

trace文件如下,篇幅原因有省略
/u01/app/admin/orcl/udump/orcl_ora_2590.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle
System name: Linux
Node name: rac1
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 33
Unix process pid: 2590, image: oracle@rac1 (TNS V1-V3)
trace文件通用，包含了操作系统、数据库和会话的信息，这里不再累述。
*** 2012-04-25 10:53:00.982
*** ACTION NAME:() 2012-04-25 10:53:00.981
*** MODULE NAME:(SQL*Plus) 2012-04-25 10:53:00.981
*** SERVICE NAME:(SYS$USERS) 2012-04-25 10:53:00.981
*** SESSION ID:(159.5) 2012-04-25 10:53:00.981
Registered qb: SEL$1 0x2db12034 (PARSER)
signature (): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=4 objn=53393 hint_alias="T"@"SEL$1"
fro(1): flg=4 objn=53395 hint_alias="T1"@"SEL$1"
下面是10053 trace信息

***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1 标识10053事件用的时level1级别
***************************************

**************************
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"<:B1 AND "T"."X"="T1"."ID" #最初的谓词条件
kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
predicates with check contraints: "T"."X"<:B1 AND "T"."X"="T1"."ID" AND "T1"."ID"<:B2
after transitive predicate generation: "T"."X"<:B1 AND "T"."X"="T1"."ID" AND "T1"."ID"<:B2
finally: "T"."X"<:B1 AND "T"."X"="T1"."ID" AND "T1"."ID"<:B2 #最终的谓词条件
可以看出，从逻辑上这两个谓词条件是等价的，CBO只所以进行改写，是为了方便计算每一步的成本和估算Cardinality
FPD: Following transitive predicates are generated in SEL$1 (#0) :
"T1"."ID"<:B1
apadrv-start: call(in-use=340, alloc=16360), compile(in-use=34068, alloc=37692)
kkoqbc-start
: call(in-use=344, alloc=16360), compile(in-use=34824, alloc=37692)
******************************************
Current SQL statement for this session:
select t1.* from t1,t where t.x<:c and t.x=t1.id
*******************************************
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
。。。。。省略若干行。。。。。。
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

****************
QUERY BLOCK TEXT
****************
select t1.* from t1,t where t.x<:c and t.x=t1.id
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=0 objn=74723 hint_alias="T"@"SEL$1"
fro(1): flg=0 objn=74725 hint_alias="T1"@"SEL$1"

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------

***************************************
Peeked values of the binds in SQL statement
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b7b1efb8 bln=22 avl=00 flg=05
BIND ：Variables bound to a cursor，游标号
bind N ：The bind position being bound，绑定游标的位置，从0开始，0是第一个游标
dty ：Data type，数据类型
mxl ：Maximum length of the bind variable (private max len in paren)，绑定变量的最大长度
mal ：Array length，最大数组长度(当用到绑定变量数组或批量操作时)
scl ：Scale，比例
pre ：Precision，精度
oacflg ：Special flag indicating bind options，内部标记，若是奇数，则绑定变量为空值，允许有空值。
oacflg2 ：Continuation of oacflg，内部标记的扩展
size ：Amount of memory to be allocated for this chunk，缓冲区的大小
offset ：Offset into this chunk for this bind buffer，缓冲区的chunk大小
bfp ：Bind address，绑定变量地址
bln ：Bind buffer length，绑定变量缓冲区长度
avl ：Actual value length (array length too)，实际值的长度
flg ：Special flag indicating bind status，内部标记
value ：The actual value of the bind variable，绑定变量的实际值，有可能是16进制转储

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
_b_tree_bitmap_plans = false
optimizer_dynamic_sampling = 3
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
性能相关的初始化参数值
。。。。。省略若干行。。。。。。
_optimizer_star_tran_in_with_clause = true
_optimizer_complex_pred_selectivity = true
_gby_hash_aggregation_enabled = true
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
****************
QUERY BLOCK TEXT
****************
select t1.* from t1,t where t.x<100 and t.x=t1.id
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=0 objn=53393 hint_alias="T"@"SEL$1"
fro(1): flg=0 objn=53395 hint_alias="T1"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats 基于非工作量统计模式

CPUSPEEDNW: 2696 millions instructions/sec (default is 100) 非工作量统计模式下CPU主频

IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IO传输速率（字节/毫秒）

IOSEEKTIM: 10 milliseconds (default is 10) IO寻址时间（毫秒）

MBRC: -1 blocks (default is 8) 一次多块读可以读几个数据块
***************************************
BASE STATISTICAL INFORMATION
这一部分是sql中应用到的对象基本信息，包括表关联和各自索引的信息，这些信息都可以在相关视图中找到，如user_indexes,user_tables等
***********************
Table Stats::
Table: T Alias: T
#Rows: 50701 #Blks: 86 AvgRowLen: 4.00
Column (#1): X(NUMBER)
AvgLen: 5.00 NDV: 50701 Nulls: 0 Density: 1.9723e-05 Min: 6 Max: 50700
Index Stats::
Index: T_IDX Col#: 1
LVLS: 1 #LB: 112 #DK: 50701 LB/K: 1.00 DB/K: 1.00 CLUF: 78.00
***********************
Table Stats::
Table: T1 Alias: T1
#Rows: 50701 #Blks: 251 AvgRowLen: 29.00
Column (#1): ID(NUMBER)
AvgLen: 5.00 NDV: 50701 Nulls: 0 Density: 1.9723e-05 Min: 8 Max: 53394
Index Stats::
Index: T1_IDX Col#: 1
LVLS: 1 #LB: 112 #DK: 50701 LB/K: 1.00 DB/K: 1.00 CLUF: 393.00
表信息的部分中包括了表的行数、数据块数、平均行数。对于字段，只列出了谓词条件中包含的字段。对于在谓词中没有出现的字段，因为它不影响执行计划的选择，所以以CBO不需要将他考虑到代价中，我们看到，这里列出的是X字段，因为它既是两表关联的字段，同时自身也是一个谓词条件，X列的信息包括了它的类型、平均长度、非重复的值、空值、密度以及列的最大最小值，这些信息在CBO做执行计划代价的计算上都要作为输入的值。
索引项部分中列出了所以的高度，索引页块数（LB，Leaf Blocks），每个索引占据的数据块数（LB/K Leaf Blocks/Key），每个索引键值对应的表中数据块（DB/K，Data Blocks/Key），索引的聚合因子（CLUF，Clustering Factor）。集合因子CLUF（索引聚合因子），它表示索引中的键值和元表中的数据分布的一种关系，当索引键值和表中数据的排列顺序大致相同时，它意味着键值指向的数据块越多时（数据排序和索引相差越大）时，这个因子就越大，越不利于索引的使用。了解这个指标对于我们分析sql的执行计划很有用处，比如我们发现SQL执行计划异常，可是从cardinality上无法解释，也许应该考虑一下是否是CLUF的影响导致的。关于CLUF可以参加如下文章：
http://czmmiao.iteye.com/blog/1481957
***************************************
SINGLE TABLE ACCESS PATH
*** 2012-04-25 10:53:00.998
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning FALSE.
Table: T1 Alias: T1
Card: Original: 50701 Rounded: 87 Computed: 87.37 Non Adjusted: 87.37

原始行数             近似值         精确值             非修正值
Access Path: TableScan
Cost: 58.69 Resp: 58.69 Degree: 0             --Cost:总代价
Cost_io: 57.00 Cost_cpu: 11929421          --Cost:总代价=IO代价 + CPU代价
Resp_io: 57.00 Resp_cpu: 11929421          --并行访问代价
Access Path: index (RangeScan)
Index: T1_IDX
resc_io: 3.00 resc_cpu: 53924                       --串行访问代价
ix_sel: 0.0017233 ix_sel_with_filters: 0.0017233

索引选择率     带过滤条件索引选择率
Cost: 3.01 Resp: 3.01 Degree: 1
Best:: AccessPath: IndexRange Index: T1_IDX
Cost: 3.01 Degree: 1 Resp: 3.01 Card: 87.37 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
*** 2012-04-25 10:53:00.998
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning FALSE.
Table: T Alias: T
Card: Original: 50701 Rounded: 94 Computed: 94.01 Non Adjusted: 94.01
Access Path: TableScan
Cost: 22.53 Resp: 22.53 Degree: 0
Cost_io: 21.00 Cost_cpu: 10752644
Resp_io: 21.00 Resp_cpu: 10752644
Access Path: index (index (FFS))
Index: T_IDX
resc_io: 26.00 resc_cpu: 9416771
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 27.34 Resp: 27.34 Degree: 1
Cost_io: 26.00 Cost_cpu: 9416771
Resp_io: 26.00 Resp_cpu: 9416771
Access Path: index (IndexOnly)
Index: T_IDX
resc_io: 2.00 resc_cpu: 33243
ix_sel: 0.0018543 ix_sel_with_filters: 0.0018543
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange Index: T_IDX
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 94.01 Bytes: 0
这部分展示了CBO计算的每个对象单独访问的代价。CBO要计算出每个对象单独访问时的代价，通过比较所有的数据访问的代价，选择出代价最小的一种访问方式。以T表为例我们比较关心如下两个指标
Card:Original:50741
原纪录数，也就是操作数据源的数据纪录数，在这里就是表的实际纪录50741
Card：Rounded：94
输出的纪录数，CBO计算出通过条件过滤，预计得到的纪录数。我们知道T安装条件小于100的纪录数是94条，这里估算出是96条，比较接近实际值。
通过这一部分的信息我们看到，对于T表，CBO人为可能使用下面几种方式来访问数据。
全表扫描
Access Path: TableScan
索引快速扫描
Access Path: index (index (FFS))
单独访问索引
Access Path: index (IndexOnly)
因为在结果集里面是T1表的信息，所以对于T表，只需要访问索引做关联条件查询，不需要访问表，所以单独访问索引也是可行的。
CBO计算出三种方式产生的代价分别是：
TableScan: 22.53
index (FFS) 26
index (IndexOnly) 2.00
很显然，单独访问索引的方式是代价最低的，所以CBO得出的结论，对于T表上的查询，选择使用单独访问索引的方式。
Best:: AccessPath: IndexRange Index: T_IDX
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 94.01 Bytes: 0
T1表的分析方法雷同，这里不再赘述。这一部分，CBO计算了每个表单独进行数据访问代价最小的方式，为下一步表关联查询提供了代价计算的数据依据
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]: T1[T1]#0 T[T]#1     #T1关联T
***************
Now joining: T[T]#1
***************
NL Join      #NESTED LOOPS JOIN
Outer table: Card: 87.37 Cost: 3.01 Resp: 3.01 Degree: 1 Bytes: 29
Inner table: T Alias: T
Access Path: TableScan
NL Join: Cost: 1773.79 Resp: 1773.79 Degree: 0
Cost_io: 1641.00 Cost_cpu: 935533938
Resp_io: 1641.00 Resp_cpu: 935533938
Access Path: index (index (FFS))
Index: T_IDX
resc_io: 24.52 resc_cpu: 9416771
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Inner table: T Alias: T
Access Path: index (FFS)
NL Join: Cost: 2252.29 Resp: 2252.29 Degree: 0
Cost_io: 2136.00 Cost_cpu: 819313026
Resp_io: 2136.00 Resp_cpu: 819313026
kkofmx: index filter:"T"."X"<100 AND "T"."X"="T1"."ID" AND "T1"."ID"<100
Access Path: index (AllEqJoinGuess)
Index: T_IDX
resc_io: 1.00 resc_cpu: 8171
ix_sel: 1.9723e-05 ix_sel_with_filters: 3.6573e-08
NL Join (ordered): Cost: 90.11 Resp: 90.11 Degree: 1
Cost_io: 90.00 Cost_cpu: 769190
Resp_io: 90.00 Resp_cpu: 769190
Best NL cost: 90.11 #最好的nested loops join方式，代价为90.11
resc: 90.11 resc_io: 90.00 resc_cpu: 769190
resp: 90.11 resp_io: 90.00 resp_cpu: 769190
Join Card: 86.47 = outer (87.37) * inner (94.01) * sel (0.010526)
Join Card - Rounded: 86 Computed: 86.47
SM Join       #SORT MERGE JOIN
Outer table:
resc: 3.01 card 87.37 bytes: 29 deg: 1 resp: 3.01
Inner table: T Alias: T
resc: 2.00 card: 94.01 bytes: 4 deg: 1 resp: 2.00
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 106 Area size: 131072 Max Area size: 18874368
Degree: 1
Blocks to Sort: 1 Row size: 15 Total Rows: 94
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 7073149
Total Temp space used: 0
SM join: Resc: 6.02 Resp: 6.02 [multiMatchCost=0.00]
SM cost: 6.02    #Sort merge join的代价为6.02
resc: 6.02 resc_io: 5.00 resc_cpu: 7160316
resp: 6.02 resp_io: 5.00 resp_cpu: 7160316
HA Join
Outer table:
resc: 3.01 card 87.37 bytes: 29 deg: 1 resp: 3.01
Inner table: T Alias: T
resc: 2.00 card: 94.01 bytes: 4 deg: 1 resp: 2.00
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      #hash join的代价为5.52
resc: 5.52 resc_io: 5.00 resc_cpu: 3632312
resp: 5.52 resp_io: 5.00 resp_cpu: 3632312
Best:: JoinMethod: Hash
Cost: 5.52 Degree: 1 Resp: 5.52 Card: 86.47 Bytes: 33
***********************
Best so far: Table#: 0 cost: 3.0077 card: 87.3729 bytes: 2523
Table#: 1 cost: 5.5156 card: 86.4652 bytes: 2838
#CBO得出结论，T1表关联T表代价最下的join方式为hash join的代价为5.52
***********************
Join order[2]: T[T]#1 T1[T1]#0      #T表关联T1表
***************
Now joining: T1[T1]#0
***************
NL Join      #NESTED LOOPS JOIN
Outer table: Card: 94.01 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 4
Inner table: T1 Alias: T1
Access Path: TableScan
NL Join: Cost: 5324.17 Resp: 5324.17 Degree: 0
Cost_io: 5165.00 Cost_cpu: 1121398858
Resp_io: 5165.00 Resp_cpu: 1121398858
kkofmx: index filter:"T1"."ID"<100
Access Path: index (AllEqJoinGuess)
Index: T1_IDX
resc_io: 2.00 resc_cpu: 15463
ix_sel: 1.9723e-05 ix_sel_with_filters: 3.3989e-08
NL Join (ordered): Cost: 190.21 Resp: 190.21 Degree: 1
Cost_io: 190.00 Cost_cpu: 1491454
Resp_io: 190.00 Resp_cpu: 1491454
Best NL cost: 190.21      #最好的nested loops join的代价为190.21
resc: 190.21 resc_io: 190.00 resc_cpu: 1491454
resp: 190.21 resp_io: 190.00 resp_cpu: 1491454
Join Card: 86.47 = outer (94.01) * inner (87.37) * sel (0.010526)
Join Card - Rounded: 86 Computed: 86.47
SM Join      #Sort merge join
Outer table:
resc: 2.00 card 94.01 bytes: 4 deg: 1 resp: 2.00
Inner table: T1 Alias: T1
resc: 3.01 card: 87.37 bytes: 29 deg: 1 resp: 3.01
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 106 Area size: 131072 Max Area size: 18874368
Degree: 1
Blocks to Sort: 1 Row size: 42 Total Rows: 87
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 7070644
Total Temp space used: 0
SM join: Resc: 6.02 Resp: 6.02 [multiMatchCost=0.00]
SM cost: 6.02      #Sort merge join的代价为6.02
resc: 6.02 resc_io: 5.00 resc_cpu: 7157811
resp: 6.02 resp_io: 5.00 resp_cpu: 7157811
HA Join      #hash join
Outer table:
resc: 2.00 card 94.01 bytes: 4 deg: 1 resp: 2.00
Inner table: T1 Alias: T1
resc: 3.01 card: 87.37 bytes: 29 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      #hash join的代价为5.52，这里计算出来的代价值和上面T1关联T表的代价值相等，那么CBO会继续比较串行执行和并行执行的IO和CPU代价
resc: 5.52 resc_io: 5.00 resc_cpu: 3632662    #串行执行的CPU代价为3632662大于上面计算出来的3632312
resp: 5.52 resp_io: 5.00 resp_cpu: 3632662    #并行执行的CPU代价为3632662大于上面计算出来的3632312
Join order aborted: cost > best plan cost    # 废弃该join方式
***********************
(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    # 得出结论，采用T1表hash joinT表的方式
Cost: 5.5156 Degree: 1 Card: 86.0000 Bytes: 2838    # 具体代价
Resc: 5.5156 Resc_io: 5.0000 Resc_cpu: 3632312
Resp: 5.5156 Resp_io: 5.0000 Resc_cpu: 3632312
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."OBJECT_NAME" "OBJECT_NAME" FROM "HR"."T1" "T1","HR"."T" "T" WHERE "T1"."ID"<:B1 AND "T"."X"="T1"."ID" AND "T"."X"<:B2
kkoqbc-end
: call(in-use=43384, alloc=49112), compile(in-use=37140, alloc=37692)
apadrv-end: call(in-use=43384, alloc=49112), compile(in-use=37760, alloc=41816)
sql_id=azdnm8t9dwdb3.
Current SQL statement for this session:
select t1.* from t1,t where t.x<:c and t.x=t1.id
============
Plan Table
============
------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 6 | |
| 1 | HASH JOIN | | 3 | 99 | 6 | 00:00:01 |
| 2 | INDEX RANGE SCAN | T_IDX | 3 | 12 | 2 | 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | T1 | 5 | 145 | 3 | 00:00:01 |
| 4 | INDEX RANGE SCAN | T1_IDX | 5 | | 2 | 00:00:01 |
------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("T"."X"="T1"."ID")
2 - access("T"."X"<:C)
4 - access("T1"."ID"<:C)
Content of other_xml column
执行计划
===========================
db_version : 10.2.0.1
parse_schema : HR
plan_hash : 1611193875
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPT_PARAM('optimizer_dynamic_sampling' 3)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."ID"))
INDEX(@"SEL$1" "T"@"SEL$1" ("T"."X"))
LEADING(@"SEL$1" "T1"@"SEL$1" "T"@"SEL$1")
USE_HASH(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/

Optimizer environment:
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
参数和bug信息
。。。。。省略若干行。。。。。。
Query Block Registry:
*********************
MISC$1 0xb7f4ac90 (PARSER) [FINAL]
Optimizer State Dump: call(in-use=84156, alloc=84156), compile(in-use=38936, alloc=82100)

深入解析10053事件

你是否想知道一句sql语句如何执行，它是否走索引，是否采用不同得驱动表，是否用nestloop join,hash join…..?这一切对你是否很神秘呢？或许你会说execution plan能看到这些东西，但是你是否清楚execution plan是如何得到？这篇文章就是给出了隐藏在execution plan底下的具体实现。

10053事件

10053事件是oracle提供的用于跟踪sql语句成本计算的内部事件，它能记载CBO模式下oracle优化器如何计算sql成本，生成相应的执行计划。

如何设置10053事件

设置本session的10053

开启：

Alter session set events’10053 trace name context forever[,level {1/2}]’;

关闭：

Alter session set events’10053 trace name context off’;

设置其他session的10053

开启：

SYS.DBMS_SYSTEM.SET_EV (, , 10053, {1|2}, '')

关闭：

SYS.DBMS_SYSTEM.SET_EV (, , 10053,0, '')

跟其他跟踪事件不同，10053提供了两个跟踪级别，但是级别2的跟踪信息比级别1少（其他跟踪事件如10046跟踪级别越高信息越多），跟踪信息将被记录到user_dump_dest目录底下。注意，要实现跟踪必须满足两个条件：sql语句必须被hard parse并且必须使用CBO优化器模式。如果sql语句已经被parse过，那么10053不生成跟踪信息。如果你使用RULE优化器，那么10053也不会生成跟踪信息。

跟踪内容

跟踪文件包括6部分：

Sql语句

优化器相关参数

基本统计信息

基本表访问成本

综合计划

特殊功能的成本重计算

这篇文章将会涉及到前4项和一部分第5项的内容，我们将会用以下语句作为例子：

select dname, ename from emp, dept

where emp.deptno = dept.deptno

and ename = :b1

sql语句：

这部分是整个跟踪文件里最容易理解的部分，包括了所执行的sql语句，如果你采用RULE模式优化器，那么除了这一部分外将不会有多余信息出现在跟踪文件里。

Oracle事件之10053 跟踪的trace文件相关解释

优化器相关参数：

记载了所有影响成本计算的参数

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

PARAMETERS USED BY THE OPTIMIZER

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

OPTIMIZER_FEATURES_ENABLE = 8.1.6

OPTIMIZER_MODE/GOAL = Choose

OPTIMIZER_PERCENT_PARALLEL = 0

HASH_AREA_SIZE = 131072

HASH_JOIN_ENABLED = TRUE

HASH_MULTIBLOCK_IO_COUNT = 0

OPTIMIZER_SEARCH_LIMIT = 5

PARTITION_VIEW_ENABLED = FALSE

_ALWAYS_STAR_TRANSFORMATION = FALSE

_B_TREE_BITMAP_PLANS = FALSE

STAR_TRANSFORMATION_ENABLED = FALSE

_COMPLEX_VIEW_MERGING = FALSE

_PUSH_JOIN_PREDICATE = FALSE

PARALLEL_BROADCAST_ENABLED = FALSE

OPTIMIZER_MAX_PERMUTATIONS = 80000

OPTIMIZER_INDEX_CACHING = 0

OPTIMIZER_INDEX_COST_ADJ = 100

QUERY_REWRITE_ENABLED = TRUE

QUERY_REWRITE_INTEGRITY = ENFORCED

_INDEX_JOIN_ENABLED = FALSE

_SORT_ELIMINATION_COST_RATIO = 0

_OR_EXPAND_NVL_PREDICATE = FALSE

_NEW_INITIAL_JOIN_ORDERS = FALSE

_OPTIMIZER_MODE_FORCE = TRUE

_OPTIMIZER_UNDO_CHANGES = FALSE

_UNNEST_SUBQUERY = FALSE

_PUSH_JOIN_UNION_VIEW = FALSE

_FAST_FULL_SCAN_ENABLED = TRUE

_OPTIM_ENHANCE_NNULL_DETECTION = TRUE

_ORDERED_NESTED_LOOP = FALSE

_NESTED_LOOP_FUDGE = 100

_NO_OR_EXPANSION = FALSE

_QUERY_COST_REWRITE = TRUE

QUERY_REWRITE_EXPRESSION = TRUE

_IMPROVED_ROW_LENGTH_ENABLED = TRUE

_USE_NOSEGMENT_INDEXES = FALSE

_ENABLE_TYPE_DEP_SELECTIVITY = TRUE

_IMPROVED_OUTERJOIN_CARD = TRUE

_OPTIMIZER_ADJUST_FOR_NULLS = TRUE

_OPTIMIZER_CHOOSE_PERMUTATION = 0

_USE_COLUMN_STATS_FOR_FUNCTION = FALSE

_SUBQUERY_PRUNING_ENABLED = TRUE

_SUBQUERY_PRUNING_REDUCTION_FACTOR = 50

_SUBQUERY_PRUNING_COST_FACTOR = 20

_LIKE_WITH_BIND_AS_EQUALITY = FALSE

_TABLE_SCAN_COST_PLUS_ONE = FALSE

_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE

_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE

_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE

DB_FILE_MULTIBLOCK_READ_COUNT = 32

SORT_AREA_SIZE = 131072

基本统计信息:

下一部分是所有表和索引的基本统计信息

基本统计信息包括

表:

Trace label dba_tables column

CDN NUM_ROWS 表记录数

NBLKS BLOCKS 高水位以下的block数

TABLE_SCAN_CST 全表扫描的I/O成本

AVG_ROW_LEN AVG_ROW_LEN 平均行长

索引:

Trace label dba_indexes column

Index#, col# 索引号及表列号

LVLS BLEVEL BTREE索引高度

#LB LEAF_BLOCKS 索引叶块数

#DK DISTINCT_KEYS 不重复索引关键字

LB/K AVG_LEAF_BLOCKS_PER_KEY 叶块/关键字

DB/K AVG_DATA_BLOCKS_PER_KEY 数据块/关键字

CLUF CLUSTERING_FACTOR 索引聚合因子

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

BASE STATISTICAL INFORMATION

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

Table stats Table: DEPT Alias: DEPT

TOTAL :: CDN: 16 NBLKS: 1 TABLE_SCAN_CST: 1 AVG_ROW_LEN: 20

-- Index stats

INDEX#: 23577 COL#: 1

TOTAL :: LVLS: 0 #LB: 1 #DK: 16 LB/K: 1 DB/K: 1 CLUF: 1

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

Table stats Table: EMP Alias: EMP

TOTAL :: CDN: 7213 NBLKS: 85 TABLE_SCAN_CST: 6 AVG_ROW_LEN: 36

-- Index stats

INDEX#: 23574 COL#: 1

TOTAL :: LVLS: 1 #LB: 35 #DK: 7213 LB/K: 1 DB/K: 1 CLUF: 4125

INDEX#: 23575 COL#: 2

TOTAL :: LVLS: 1 #LB: 48 #DK: 42 LB/K: 1 DB/K: 36 CLUF: 1534

INDEX#: 23576 COL#: 8

TOTAL :: LVLS: 1 #LB: 46 #DK: 12 LB/K: 3 DB/K: 34 CLUF: 418

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

基本表访问成本：

这里开始CBO将会计算单表访问的成本

单表访问路径

SINGLE TABLE ACCESS PATH .........................................................................................................................................1

Column: ENAME Col#: 2 Table: EMP Alias: EMP.....................................................................2

NDV: 42 NULLS: 0 DENS: 2.3810e-002 ...........................................................................3

TABLE: EMP ORIG CDN: 7213 CMPTD CDN: 172 ........................................................................................4

Access path: tsc Resc: 6 Resp: 6............................................................................................................5

Access path: index (equal) ...............................................................................................................................6

INDEX#: 23575 TABLE: EMP ...........................................................................................................................7

CST: 39 IXSEL: 0.0000e+000 TBSEL: 2.3810e-002.......................................................................8

BEST_CST: 6.00 PATH: 2 Degree: 1..............................................................................................................9

我们看一下上面是什么意思。首先CBO列出了ename列的统计信息(第2，3行),这些统计信息来自dba_tab_columns。

列的统计信息和dba_tab_columns中对应的列名如下

Trace label dba_tables column

NDV NUM_DISTINCT 列的不重复值数

NULLS NUM_NULLS 列的空行数

DENS DENSITY 列密度，没有直方图的情况下= 1/NDV

LO LOW_VALUE 列的最小值 (只对数字列)

HI HIGH_VALUE 列的最大值 (只对数字列)

第4行出现了表的行数ORIG CDN和计算过的行数 CMPTD CDN (computed cardinality). 计算公司如下，

CMPTD CDN = ORIG CDN * FF

在这里 FF 表示过滤因子（Filter Factor）。我们稍后再来看FF是什么及如何计算的。

第5行表示了全表扫描的成本。这里的成本是62, 是由NBLKS和db_file_multi_block_read_count初始化参数计算出来的。.

第6－8行是索引访问的成本。

第9行是总结了以上信息并选出了最优的访问路径为全表扫描，成本为6。

表扫描成本

让我们来看一下全表扫描成本(tsc)是如何计算的这里有其他两个大表的基本统计信息。

TOTAL :: CDN: 115630 NBLKS: 4339 TABLE_SCAN_CST: 265 AVG_ROW_LEN: 272

TOTAL :: CDN: 454503 NBLKS: 8975 TABLE_SCAN_CST: 548 AVG_ROW_LEN: 151

你可能曾经看到过全表扫描成本= 访问的块数目/ db_file_multi_block_read_count. 看起来这个等式很有意义因为oracle在做全表扫描时每个I/O请求将会读取db_file_multi_block_read_count个块。但是，我们计算以上统计信息得到

NBLKS / TABLE_SCAN_CST = 4339 / 265 = 16.373 ≠ db_file_multi_block_read_count(这里的值是32，可以看前面参数那一页)

另外一个表为

NBLKS / TABLE_SCAN_CST = 8975 / 548 = 16.377

全表扫描成本和db_file_multi_block_read_count

CBO将会根据NBLKS和db_file_multiblock_read_count来估计全表扫描成本，但是db_file_multiblock_read_count通常会被打上折扣。实际上我们可以认为等式会是

TABLE_SCAN_CST = NBLKS / k

我们来看一下k和db_file_multiblock_read_count 究竟有什么规律可寻。我们来做一个实验，使用不同的

db_file_multiblock_read_count值4, 6,8, 12,16, 24,32来测试。

Oracle事件之10053 跟踪的trace文件相关解释

横轴为db_file_multiblock_read_count，纵轴为K。

注意参数K仅仅用在全表扫描或快速索引扫描上，实际的I/O成本还与其他因数有关，比如说需要访问的表已经在内存中的块及块的数量。

过滤因子(FF)

为了理解索引访问成本我们需要了解一下过滤因子。过滤因子是一个介于0和1之间的数字，反映了记录的可选择性。如果一个列有10种不同的值，我们需要查询等于其中某一个值的记录时，如果这10种值平均分布的话，你将得到1/10的行数。如果没有直方图，过滤因子为FF = 1/NDV = density

再来看一下过滤因子和查询条件的关系

不使用绑定变量的情况:

predicate Filter factor

c1 = value 1/c1.num_distinct4

c1 like value 1/c1.num_distinct

c1 > value (Hi - value) / (Hi - Lo)

c1 >= value (Hi - value) / (Hi - Lo) + 1/c1.num_distinct

c1 < value (value - Lo) / (Hi - Lo)

c1 <= value (value - Lo) / (Hi - Lo) + 1/c1.num_distinct

c1 between val1 and val2 (val2 – val1) / (Hi - Lo) + 2 * 1/c1.num_distinct

使用绑定变量的情况（8i）:

predicate Filter factor

col1 = :b1 col1.density

col1 {like | > | >= | < | <=} :b1 {5.0000e-02 | col1.density }5

col1 between :b1 and :b2 5.0000e-02 * 5.0000e-02

包含and和or的情况:

predicate Filter factor

predicate 1 and predicate 2 FF1 * FF2

predicate 1 or predicate 2 FF1 + FF2 – FF1 * FF2

包含直方图的列：

如果一个列包含了直方图信息，那么它的density就来自于直方图。关于直方图的内容请参考官方手册，这里不在细述。由于直方图的存在FF并不是简单的等于1/NDV,而是来自于直方图中各个列的density，所有有直方图的话CBO将可能采取不一样的执行路径。

索引访问成本：

现在我们知道了聚合因子的概念，我们再来看一看索引访问的成本