一. 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_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
============
--------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------+-----------------------------------+
| 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模式优化器，那么除了这一部分外将不会有多余信息出现在跟踪文件里。
优化器相关参数：
记载了所有影响成本计算的参数
***************************************
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来测试。
横轴为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将可能采取不一样的执行路径。
索引访问成本：
现在我们知道了聚合因子的概念，我们再来看一看索引访问的成本
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
我们来看6-8行，这里表示了索引访问的成本。第6行表示这里采取索引equal的方法来访问，再来回忆一下索引的基本统计信息
INDEX#: 23575 COL#: 2
TOTAL :: LVLS: 1 #LB: 48 #DK: 42 LB/K: 1 DB/K: 36 CLUF: 1534
根据索引成本计算公式
blevel + FF*leaf_blocks + FF*clustering_factor
1 + 2.3810e-002-2*48 + 2.3810e-002-2*1534 = 1 + 1.1429 + 36.5245 = 38.6674
这里的FF就等于TBSEL=DENS=2.3810e-002,由于我们的查询条件为ename = :b1所以得出FF为ENAME列的DENS，
其实索引访问方式的成本计算公式
? Unique scan blevel+1
? Fast full scan leaf_blocks / k ( k = 1.6765x0.6581 )
? Index-only blevel + FF*leaf_blocks
让我们用别的例子证明一下索引成本计算，语句为
select … from tbl a
where a.col#1 = :b1
and a.col#12 = :b2
and a.col#8 = :b3
索引和列的基本统计数据如下
INDEX# COL# LVLS #LB #DK LB/K DB/K CLUF
8417 27,1 1 13100 66500 1 22 1469200
8418 1,12,7 2 19000 74700 1 15 1176500
8419 3,1,4,2 2 31000 49700 1 2 118000
15755 1,12,8 1 12600 18800 1 30 1890275
8416 1,2,33,4,5,6 2 25800 1890300 1 1 83900
Col#: 1 NDV: 10 NULLS: 0 DENS: 1.0000e-001-1
Col#: 12 NDV: 8 NULLS: 0 DENS: 1.2500e-001
Col#: 8 NDV: 33 NULLS: 0 DENS: 3.0303e-001
Access path: index (scan)...................................................................................................................................1
INDEX#: 8418 CST: 14947 IXSEL: 1.2500e-002 TBSEL: 1.2500e-002 ........................................2
Access path: index (equal) ...............................................................................................................................3
INDEX#: 15755 CST: 7209 IXSEL: 0.0000e+000 TBSEL: 3.7879e-003 ......................................4
Access path: index (scan) .................................................................................................................................5
INDEX#: 8416 CST: 10972 IXSEL: 1.0000e-001 TBSEL: 1.0000e-001 ........................................6
5个索引中，索引(#8417 and #8419) 将不会被考虑因为他们的首列不出现在查询条件中。.
INDEX# 8418
索引包含的3个列中只有2列出现在查询条件中，所以只用2列的DENS来计算过滤因子
FF = 1.0000e-001 * 1.2500e-001= 1.2500e-002
cost = lvl + FF*#LB + FF*clustering factor
= 2 + 19,000*1.2500e-002 + 1176500*1.2500e-002
= 2 + 237.5 + 14706.25 = 14945.75
INDEX# 15755
索引包含的3列都出现在查询条件中，用3列的DENS计算过滤因子
FF = 1.0000e-001 * 1.2500e-001 * 3.0303e-001 = 3.7879e-003
cost = lvl + FF*#LB + FF*clustering factor
= 1 + 12,600*3.7879e-003 + 1,890,275*3.7879e-003
= 2 + 47.73 + 7160.13 = 7208.86
INDEX# 8416
索引包含的3个列中只有1列出现在查询条件中，所以只用1列的DENS来计算过滤因子
FF = 1.0000e-001
cost = lvl + FF*#LB + FF*clustering factor
= 2 + 25,800*1.0000e-001+ 83,900*1.0000e-001
= 2 + 2580 + 8390 = 10972
虽然索引8416只有一列出现在查询条件中，但是它的成本还是低于索引8418，因为它的聚合因子(clustering factor)比较低，所以统计出来成本也比较低。关于聚合因子可以参考oracle官方文档。
综合计划：
这一部分开始是10053最大的一部分，在这里CBO会评估各种JOIN方式及顺序的成本。
1. NL - NESTED LOOP JOIN
join cost = cost of accessing outer table
+ (row number of outer table * cost of accessing inner table )
2. SM – SORT MERGE JOIN
join cost = (cost of accessing outer table + outer sort cost)
+ (cost of accessing inner table + inner sort cost)
3. HA – HASH JOIN
join cost = (cost of accessing outer table)
+ (cost of building hash table)
+ (cost of accessing inner table )
JOIN ORDER [N]
Join order[1]: DEPT [DEPT] EMP [EMP]
Now joining: EMP [EMP] *******
JOINS – NL
NL Join ..............................................................................................................................................................................1
Outer table: cost: 1 cdn: 16 rcz: 13 resp: 1..................................................................................2
Inner table: EMP ......................................................................................................................................................3
Access path: tsc Resc: 6 ...............................................................................................................................4
Join resc: 97 Resp: 97 ...............................................................................................................................5
Access path: index (join stp) ...........................................................................................................................6
INDEX#: 23575 TABLE: EMP ...........................................................................................................................7
CST: 39 IXSEL: 0.0000e+000 TBSEL: 2.3810e-002.......................................................................8
Join resc: 625 resp:625 .............................................................................................................................9
Access path: index (join index).....................................................................................................................10
INDEX#: 23576 TABLE: EMP .........................................................................................................................11
CST: 37 IXSEL: 0.0000e+000 TBSEL: 8.3333e-002.....................................................................12
Join resc: 593 resp:593 ...........................................................................................................................13
Access path: and-equal...................................................................................................................................14
CST: 19 ...............................................................................................................................................................15
Join resc: 305 resp:305 ...........................................................................................................................16
Join cardinality: 172 = outer (16) * inner (172) * sel (6.2500e-002) [flag=0].................17
Best NL cost: 97 resp: 97...............................................................................................................................18
第1行为JOIN方式
第2行为驱动表的成本，行数，行大小。这里的行数为16，平均行长原本为20，但是因为DEPT表包含(DEPTNO, DEPT, and LOC)3列但仅有DEPTNO,DEPT等2列需要被join，所以计算后平均行长为16，所以在这里也被称为low row size.
第3行到16行通过NL JOIN的成本计算公式，计算出几种不同join方法的成本。
1. Tablescan of EMP at a cost of 6:
cost = cost of outer + cardinality of outer * cost of inner = 1 + 16 * 6 = 97 lines 3 to 5
2. Scan of index 23575 on ENAME at a cost of 39:
cost = 1 + 16 * 39 = 625 lines 6 to 9
3. Scan of index 23576 on DEPTNO at a cost of 37:
cost = 1 + 16 * 37 = 593 lines 10 to 13
4. An “and-equal” access at a cost of 19:
cost = 1 + 16 * 19 = 305 lines 14 to 16
第17行CBO估算出这个JOIN结果集的记录数，它将被最为下一次join的输入。它的计算公式为
Join cardinality:= outer * inner * join selectivity
而join selectivity为
join selectivity = 1/max[ NDV(t1.c1), NDV(t2.c2) ]
* [ (card t1 - # t1.c1 NULLs) / card t1 ]
* [ (card t2 - # t2.c2 NULLs) / card t2 ]
Join cardinality只会被用于NL JOIN中，其他JOIN会采取不同办法。
最后在18行，CBO将会列出成本最低的NL JOIN的方法。
JOINS - SM
SM Join
Outer table:
resc: 1 cdn: 16 rcz: 13 deg: 1 resp: 1
Inner table: EMP
resc: 6 cdn: 172 rcz: 9 deg: 1 resp: 6
SORT resource Sort statistics
Sort width: 3 Area size: 43008 Degree: 1
Blocks to Sort: 1 Row size: 25 Rows: 16
Initial runs: 1 Merge passes: 1 Cost / pass: 2
Total sort cost: 2
SORT resource Sort statistics
Sort width: 3 Area size: 43008 Degree: 1
Blocks to Sort: 1 Row size: 20 Rows: 172
Initial runs: 1 Merge passes: 1 Cost / pass: 2
Total sort cost: 2
Merge join Cost: 10 Resp: 10
SM Join (with index on outer)
Access path: index (no sta/stp keys)
INDEX#: 23577 TABLE: DEPT
CST: 2 IXSEL: 1.0000e+000 TBSEL: 1.0000e+000
Outer table:
resc: 2 cdn: 16 rcz: 13 deg: 1 resp: 2
Inner table: EMP
resc: 6 cdn: 172 rcz: 9 deg: 1 resp: 6
SORT resource Sort statistics
Sort width: 3 Area size: 43008 Degree: 1
Blocks to Sort: 1 Row size: 20 Rows: 172
Initial runs: 1 Merge passes: 1 Cost / pass: 2
Total sort cost: 2
Merge join Cost: 10 Resp: 10
在SM JOIN中成本为
Cost of outer + cost of inner + sort cost for outer + sort cost for inner = 1+ 6 + 2 + 2 = 11.
在这里CBO减去1所以最终等于10。在第2个SM JOIN的方法下通过了已经排序的索引，所以成本为 2 + 6 + 0 (no sort on outer) + 2 = 10.
JOINS – HA
HA Join
Outer table:
resc: 1 cdn: 16 rcz: 13 deg: 1 resp: 1
Inner table: EMP
resc: 6 cdn: 172 rcz: 9 deg: 1 resp: 6
Hash join one ptn: 1 Deg: 1
hash_area: 32 buildfrag: 33 probefrag: 1 ppasses: 2
Hash join Resc: 8 Resp: 8
Join result: cost: 8 cdn: 172 rcz: 22
根据HA JOIN公式，计算出成本为
(cost of accessing outer table)+ (cost of building hash table)+ (cost of accessing inner table )
=1+6+1=8
所以在这里HA JOIN会被选做最优化的执行路径，SQL语句将会最终走HA JOIN.
多重JOIN:
如果出现大于两个表进行JOIN的情况，那么会有更多的join顺序被考虑，4个表join的话会有24种join顺序，5个表的话会有120个join顺序，n个表会有n!个join顺序。由于估算每种join顺序都会耗费cpu，所以oracle用一个初始化参数optimizer_max_permutations来限制最大计算join顺序。若想了解多重join的更多信息，请搜索相关sql调整的资料。
结论：
10053是一个很好的理解CBO工作机制的工具，如果辅以10046事件查看执行计划，那么整个sql语句从解析到执行的过程都一目了然了。
【原创】ORACLE 深入解析10053事件
新年新说：
新年伊始，2012年过去了，我们又踏上了2013年的，回顾2012我们付出了很多，辛勤和汗水换来了知识和友谊，当我们技术成长的时候我才发现长路漫漫，唯心可敬。一份耕耘一份收获，走技术之路是艰辛的 孤独的 漫长的，在此向刚入门的小伙子们，说一说心得体会。做好心理准备，可能你为了小小的虚荣心，为了生活所迫，才走上此路，但你也要走的洒脱 走的稳健，当你站在第一个里程碑时回顾来时路，你会发现你的收获是值得的，你的付出是有意思的，你才能有继续走下去的勇气。我要感谢 Alantany tigerfish 海哥 张老师 飚哥 寅总 dingjun 晶晶 童mm 蓓蓓 还有 好多好多 帮助过我的人们，我的成长离不开你们的鼓励。飙完泪之后开始上干货吧：）
ORACLE 深入解析10053事件
本次我们主要讲解oracle 10053事件和实验，好多朋友可能对这个事件不是很熟悉，因为在日常运维中用到的不是很多。Oracle 10046和10053 都是非官方trace sql的方法，在官方文档上是找不到相关资料的，但在MOS上可以找到。sql_trace是官方推荐的trace sql的方法，在官方文档上是可以查询出来的。
10053事件：用来描述oracle如何选择执行计划的过程，然后输出到trace文件里，共我们参考，因为我们经常看执行计划怎么执行的消耗了哪些资源，而不是常看执行计划怎么选择出来了的。
10053场景：当SQL语句执行时走的是错误的执行计划，而又找不到原因时，这时请用10053来分析一下原因。
10053特点：
（1）只可以了解oracle执行计划的选择过程
（2）无法获知代价的计算公式，因为这是oracle内部的商业机密，而且每个oracle版本的优化器计算公式都不相同差距还是蛮大的，不同版本的同一个语句的代价也不一样，优化器现在还不是很成熟，还有待完善。
（3）在这个里面我们重点要了解的是“代价”是如何计算出来的，然后我们才能了解执行计划是如何选择的。
（4）在10053中可以了解哪些因素影响sql的执行代价
（5）oracle 8i cost等价IO资源消耗 9i以后cost等价IO+CPU+网络+等待事件+其他代价
一般IO资源的权重比较大 CPU权重较小
10053内容：
参数区：初始化参数，隐含参数，这些参数可以左右oracle工作方式
SQL区：执行的SQL语句，是否使用绑定变量，是否进行了转换操作
系统信息区：操作系统统计信息 cpu主频 CPU执行时间 IO寻址时间 单块读时间 多块读时间
对象统计信息区：
数据访问方式：访问方式不一样计算代价的方法也不一样，全表扫描 走索引 多表关联 代价都不同
关联查询：把每张表都作为驱动表去组合，择优选择“代价”最小的关联方式，与哪个表在前无关系
代价的最后修正：oracle会对选择出来的代价再进行最后的修正，使其更准确一些，更合理一些
选择出最终执行计划：这个过程是非常快速的，毫秒级就搞定啦
实验环境
LEO1@LEO1> select * from v$version; 这是我的oracle edition
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
1.验证全表扫描的成本计算公式，贴出执行计划和计算公式。
LEO1@LEO1> col sname for a20
LEO1@LEO1> col pname for a20
LEO1@LEO1> col pual1 for a30
LEO1@LEO1> col pual2 for a30
LEO1@LEO1> select * from sys.aux_stats$; 查看操作系统统计信息
SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ---------- ---------------------------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 08-15-2009 00:49
SYSSTATS_INFO DSTOP 08-15-2009 00:49
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 2657.0122
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
说明
aux_stats$是sys管理员用户下的一个基表后缀为$，必须写schema才能查询到，所谓的基表就是给动态性能视图提供数据的原始表，由于基表非常重要，oracle规定不允许直接访问和修改基表，如果你比较了解这些那么另说了。这个表中记录了“操作系统统计信息”。Oracle会利用操作系统统计信息来修正执行计划的代价，也就是说这些信息是影响代价计算的因素之一。
注意：如果oracle收集了操作系统统计信息，那么CBO采用工作量统计模式计算代价
如果oracle没有收集操作系统统计信息，那么CBO采用非工作量统计模式计算代价，看上面MBRC没有参数值就说明还没有收集操作系统统计信息
这两个模式计算代价的公式是不同的。
SNAME：是指操作系统统计信息
PNAME：parameter name 参数名
PVAL1：参数值
PVAL2：参数值
参数解释
FLAGS：标志
CPUSPEEDNW：非工作量统计模式下CPU主频，直接来自硬件
IOSEEKTIM：IO寻址时间（毫秒），直接来自硬件
IOTFRSPEED：IO传输速率（字节/毫秒）
SREADTIM：读取单个数据块的平均时间
MREADTIM：读取多个数据块的平均时间
CPUSPEED：工作量统计模式下CPU主频，根据当前工作量评估出一个合理值
MBRC：oracle收集完统计信息后评估出的一次多块读可以读几个数据块db_file_multiblock_read_count
MAXTHR：最大IO吞吐量（字节/秒）
SLAVETHR：平均IO吞吐量（字节/秒）
后面这6个参数是在oracle收集完统计信息后才能得出的参数值，有什么用呢？我来解释一下下
CBO在计算SQL语句的代价时，需要使用数据库对象例如表 索引 等对象统计数据，还要使用操作系统统计数据例如CPU周期 IO速度 数据块读时间等，选择花费时间最少的执行计划为最佳执行计划。
Oracle使用dbms_stats.gather_system_stats存储过程来收集操作系统统计信息，收集来的数据存放在sys.aux_stats$表中，如果我们做了收集操作那么会有统计数据，如果没有做就没有统计数据，这两种计算代价的方法是不同的，后续会讲。
dbms_stats.gather_system_stats语法
execute dbms_stats.gather_system_stats（
gathering_mode varchar2 default ‘noworkload’
interval integer default null,
stattab varchar2 default null,
statid varchar2 default null,
statown varchar2 default null);
解释
gathering_mode 参数，默认值“noworkload”，还可以设置为“workload”含义
noworkload：非工作量统计模式，收集上来的数据都是来自硬件
workload：工作量统计模式，收集上来的数据需要在特定的数据库负载间隔内统计出来的，这样的数据才能真实反映出数据库的操作系统参数（需要执行sql测评出来）
interval：可以指定收集统计信息的时间间隔，例如 5 收集5分钟的统计信息
命令：execute dbms_stats.gather_system_stats(‘noworkload’,5);
START和STOP关键字自己决定何时开始何时结束收集统计信息
命令：execute dbms_stats.gather_system_stats(‘start’);
上下两条指令间隔3分钟执行，然后把这3分钟的统计信息写入到sys.aux_stats$表里面
execute dbms_stats.gather_system_stats(‘stop’);
注意：上面有个MBRC参数我想多聊一下，它是初始化参数db_file_multiblock_read_count的简写中文翻译“一次读多少个数据块or一次多块读可以读几个数据块”，如果收集了统计信息那么CBO会用MBRC计算代价，如果没有收集统计信息CBO会用这个初始化参数db_file_multiblock_read_count计算代价。
LEO1@LEO1> show parameter db_file_multiblock_read_count 这是我机器上参数默认值
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------------------
db_file_multiblock_read_count integer 79
LEO1@LEO1> show parameter db_block_size 我们的一个块大小为8k
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------------------------
db_block_size integer 8192
这个参数值并不是无限大的，大多数平台下的oracle都是128。一般oracle block size =8k
128*8=1M，也就是说1M是大多数操作系统一次最大IO的限制，如果还有其他限制要从这1M里面扣除，初始化参数db_file_multiblock_read_count的最大值之所以定为128，也是为了保守策略。
79*8k=632K
测试
LEO1@LEO1> drop table leo1 purge; 清空环境
Table dropped.
LEO1@LEO1> create table leo1 as select * from dba_objects; 创建leo1表
Table created.
LEO1@LEO1> begin
dbms_stats.gather_table_stats( 收集表的统计信息
wnname=>'leo1', 用户名
tabname=>'leo1', 表名
cascade=>true, 级联操作
estimate_percent=>null, 全表采样
method_opt=>'for all columns size 1'); 不作直方图分析，减小代价计算的影响
end;
/
2 3 4 5 6 7 8 9
PL/SQL procedure successfully completed.
LEO1@LEO1> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 79
LEO1@LEO1> alter session set db_file_multiblock_read_count=16; 把多块读参数修改成16方便计算
Session altered.
LEO1@LEO1> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
LEO1@LEO1> select * from sys.aux_stats$; 没有收集操作系统统计信息
SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ---------- ------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 08-15-2009 00:49
SYSSTATS_INFO DSTOP 08-15-2009 00:49
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 2657.0122
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
我们没有收集操作系统统计信息，所以CBO采用了非工作量统计模式(noworkload)来计算代价
LEO1@LEO1> select blocks from user_tables where table_name='LEO1'; LEO1表总数据块为1051
BLOCKS
------------------
1051
LEO1@LEO1> set autotrace trace explain
LEO1@LEO1> select * from leo1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2716644435
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71968 | 6817K| 233 (1)| 00:00:03 |
| 1 | TABLE ACCESS FULL| LEO1 | 71968 | 6817K| 233 (1)| 00:00:03 |
--------------------------------------------------------------------------
全表扫描的成本等于233，其中CPU代价占整个权重百分比的1%
###################################################################################
成本的计算公式如下：
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
CPUCycles / cpuspeed
) / sreadtime
#SRds - number of single block reads 单块读的次数
#MRds - number of multi block reads 多块读的次数
#CPUCyles - number of CPU cycles 一个CPU周期
sreadtim - single block read time 读取单个数据块的平均时间
mreadtim - multi block read time 读取多个数据块的平均时间
cpuspeed - CPU cycles per second CPU周期/秒
注意：如果oracle收集了操作系统统计信息，那么CBO采用工作量统计模式计算代价
如果oracle没有收集操作系统统计信息，那么CBO采用非工作量统计模式计算代价我们现在处于“非工作量统计模式”
#SRds=0,因为是全表扫描，单块读为0，全都使用的是多块读
#MRds=表的块数/多块读参数=1051/16=65.6875
mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed=10+16*8192/4096=42
sreadtim=ioseektim+db_block_size/iotfrspeed=10+8192/4096=12
CPUCycles 等于 PLAN_TABLE里面的CPU_COST
LEO1@LEO1> explain plan for select * from leo1;
Explained.
LEO1@LEO1> select cpu_cost from plan_table;
CPU_COST
-----------------
38430873
cpuspeed 等于 CPUSPEEDNW= 2657.0122
COST=65.6875*42/12+38430873/2657.0122/12/1000(毫秒换算成秒)=229.90625+1.20532=231.11157
229.90625 是IO代价
1.20532 是CPU代价
手工计算出来的COST用四舍五入等于232，和我们看到的233有差别，这是由于隐含参数_table_scan_cost_plus_one参数造成的
LEO1@LEO1> conn / as sysdba 切换到sys用户才能查看隐含参数
SYS@LEO1> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM x$ksppi x,x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND x.ksppinm LIKE '%_table_scan_cost_plus_one%'; 2 3 4 5
NAME VALUE DESCRIB
----------------------------------------------------------------------------------------------------------------------------------------------
_table_scan_cost_plus_one TRUE bump estimated full table scan and index ffs cost by one
根据该参数的描述，在table full scan和index fast full scan的时候会将cost+1 即 232+1=233
我们把_table_scan_cost_plus_one参数禁用看看cost变化
SYS@LEO1> alter session set "_table_scan_cost_plus_one"=false; 禁用
Session altered.
SYS@LEO1> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM x$ksppi x,x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND x.ksppinm LIKE '%_table_scan_cost_plus_one%'; 2 3 4 5 生效
NAME VALUE DESCRIB
----------------------------------------------------------------------------------------------------------------------------------------------
_table_scan_cost_plus_one FALSE bump estimated full table scan and index ffs cost by one
SYS@LEO1> select * from leo1.leo1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2716644435
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71968 | 6817K| 232 (1)| 00:00:03 |
| 1 | TABLE ACCESS FULL| LEO1 | 71968 | 6817K| 232 (1)| 00:00:03 |
--------------------------------------------------------------------------
这次得到的COST等于232，与计算值正好匹配，这是禁用隐含参数的结果
SYS@LEO1> alter session set db_file_multiblock_read_count=32; 我们修改一下多块读参数
Session altered.
SYS@LEO1> select * from leo1.leo1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2716644435
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71968 | 6817K| 204 (1)| 00:00:03 |
| 1 | TABLE ACCESS FULL| LEO1 | 71968 | 6817K| 204 (1)| 00:00:03 |
--------------------------------------------------------------------------
#SRds=0,因为是全表扫描，单块读为0，全都使用的是多块读
#MRds=表的块数/多块读参数=1051/32=32.84375
mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed=10+32*8192/4096=74
sreadtim=ioseektim+db_block_size/iotfrspeed=10+8192/4096=12
CPUCycles=38430873
cpuspeed 等于 CPUSPEEDNW= 2657.0122
COST=32.84375*74/12+38430873/2657.0122/12/1000(毫秒换算成秒)= 202.53645+1.20532=203.74177
四舍五入等于204，与执行计划中COST=204相一致
小结：从实验中可以得出，oracle 11gR2中，全表扫描非工作量统计模式下COST计算公式依然和9i/10g一样，没有变化。同时我们也看到了IO成本占整个代价权重的极大部分，是影响SQL效率的主要因素，需要我们多关注。
2.给出B-tree索引 Unique scan的成本计算公式，贴出执行计划和计算公式。
CBO各种类型成本计算公式如下：
全表扫描
Full table scan cost= HWM/dbf_mbrc
索引唯一扫描
Unique scan cost = blevel +1
索引快速全扫描
Fast Full Scan cost=leaf_blocks/adj_mbrc
只访问索引，不访问原表扫描
Index-only cost = Blevel + effective index selectivity * leaf_blocks
索引范围扫描
Range Cost = Blevel + effectivity index selectivity* leaf_blocks
+ effective table selectivity * clustering_factor
嵌套循环关联
nested loop join cost =outer access cost + (inner access cost * outer cardinality)
排序合并关联
sort merge join cost = outer access cost + inner access cost + sort costs
哈希关联
hash join cost = (outer access cost * # of hash partitions) + inner access cost
实验
LEO1@LEO1> drop table leo2 purge; 清理环境
Table dropped.
LEO1@LEO1> create table leo2 as select * from dba_objects; 创建leo2表
Table created.
LEO1@LEO1> create index idx_leo2 on leo2(object_id); 创建idx_leo2
Index created.
LEO1@LEO1> begin
dbms_stats.gather_table_stats( 收集表的统计信息
wnname=>'leo1', 用户名
tabname=>'leo2', 表名
cascade=>true, 级联操作
estimate_percent=>null, 全表采样
method_opt=>'for all columns size 1'); 不作直方图分析，减小代价计算的影响
end;
/
2 3 4 5 6 7 8 9
PL/SQL procedure successfully completed.
必须要做分析，如果表没有分析，下面统计信息就没有了
LEO1@LEO1> select index_name,blevel,leaf_blocks,clustering_factor,num_rows,distinct_keys from dba_indexes where index_name='IDX_LEO2';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS DISTINCT_KEYS
------------------------------ ---------- ----------- ----------------- ---------- ----------------------- -----------------
IDX_LEO2 1 159 1076 71968 71968
BLEVEL：索引层数 1表示就1层
LEAF_BLOCKS：索引树的叶子块数 159
CLUSTERING_FACTOR：索引聚簇因子
NUM_ROWS：有索引的行数 71968和数据行数相匹配
DISTINCT_KEYS：不同的索引键值 71968
LEO1@LEO1> select count(*) from leo2;
COUNT(*)
-----------------
71968
LEO1@LEO1> select * from leo2 where object_id=10000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2495991774
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | LEO2 | 1 | 97 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IDX_LEO2 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
COST=2，其中CPU代价=0，等值查询与索引的条数无关，消耗CPU资源可以忽略不计
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=10000)
公式
Unique scan cost = blevel +1
INDEX UNIQUE SCAN的COST=1 就是blevel，CBO看看需要递归几层索引，与统计信息中的blevel一致
TABLE ACCESS BY INDEX ROWID的COST=1 通过索引rowid访问表产生的代价
因此最终COST=1+1=2
3.通过10053事件分析一个SQL执行计划的产生过程，需要贴出trace中的相关信息和必要的文字说明。
测试
LEO1@LEO1> drop table leo3 purge; 清理环境
Table dropped.
LEO1@LEO1> drop table leo4 purge;
Table dropped.
LEO1@LEO1> create table leo3 as select * from dba_objects; 创建leo3表
Table created.
LEO1@LEO1> create table leo4 as select * from leo3 where rownum<100; 创建leo4表
Table created.
LEO1@LEO1> select count(*) from leo4; 这是个小表
COUNT(*)
----------
99
LEO1@LEO1> create index idx_leo3 on leo3(object_id); 创建了索引
Index created.
LEO1@LEO1> create index idx_leo4 on leo4(object_id); 同上
Index created.
LEO1@LEO1> begin
dbms_stats.gather_table_stats( leo3表做统计分析
wnname=>'leo1',
tabname=>'leo3',
cascade=>true,
estimate_percent=>null,
method_opt=>'for all columns size 1');
end;
/
2 3 4 5 6 7 8 9
PL/SQL procedure successfully completed.
LEO1@LEO1> begin
dbms_stats.gather_table_stats( leo4表做统计分析
wnname=>'leo1',
tabname=>'leo4',
cascade=>true,
estimate_percent=>null,
method_opt=>'for all columns size 1');
end;
/
2 3 4 5 6 7 8 9
PL/SQL procedure successfully completed.
LEO1@LEO1> alter session set events '10053 trace name context forever,level 1'; 启动10053事件
10053事件有2个level，1和2，1级比2级内容要详细的多
Session altered.
LEO1@LEO1> select count(*) from leo3,leo4 where leo3.object_id=leo4.object_id; 执行SQL
COUNT(*)
----------
99
LEO1@LEO1> alter session set events '10053 trace name context off'; 关闭10053事件
Session altered.
LEO1@LEO1> select value from v$diag_info where name='Default Trace File'; 当前会话写入的trace
VALUE
-----------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_22298.trc
下面我们来看看trace文件中相关信息
参数区 包含初始化参数和隐含参数等
******************************************
----- Current SQL Statement for this session (sql_id=fh7dku2xy52rc) ----- 这个会话的SQL_ID
select count(*) from leo3,leo4 where leo3.object_id=leo4.object_id
*******************************************
Legend 下面这些缩写都是优化器使用的trace标识
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style. (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
…………….
Compilation Environment Dump
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
parallel_query_forced_dop = 0
parallel_dml_forced_dop = 0
parallel_ddl_forced_degree = 0
这些都是参数的默认值
……………………………………
***************************************
Column Usage Monitoring is ON: tracking level = 1 标识10053事件用的时level1级别
***************************************
SQL区 SQL查询转换 合并块 计数统计
**************************
Query transformations (QT)
**************************
****************
QUERY BLOCK TEXT 查询块文本，就是执行的哪个SQL语句
****************
select count(*) from leo3,leo4 where leo3.object_id=leo4.object_id
操作系统统计信息区
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats 基于非工作量统计模式
CPUSPEEDNW: 2657 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) 一次多块读可以读几个数据块
基本统计信息（对象级别统计信息） OLAP系统而言拥有对象级别统计信息就已经足够了
***************************************
BASE STATISTICAL INFORMATION 这些统计信息都来自于视图
***********************
Table Stats:: 来自user_tables视图
Table: LEO4 Alias: LEO4
#Rows: 99 #Blks: 5 AvgRowLen: 75.00
行数 块数 平均行长
Index Stats:: 来自user_indexes视图
Index: IDX_LEO4 Col#: 4
LVLS: 0 #LB: 1 #DK: 99 LB/K: 1.00 DB/K: 1.00 CLUF: 2.00
索引几层 叶子块数 多少个唯一键值 每个键值有多少个叶块 每个键值有多少个数据块 聚簇因子
***********************
Table Stats::
Table: LEO3 Alias: LEO3
#Rows: 71969 #Blks: 1051 AvgRowLen: 97.00
行数 块数 平均行长
Index Stats::
Index: IDX_LEO3 Col#: 4
LVLS: 1 #LB: 159 #DK: 71969 LB/K: 1.00 DB/K: 1.00 CLUF: 1078.00
索引几层 叶子块数 多少个唯一键值 每个键值有多少个叶块 每个键值有多少个数据块 聚簇因子
Access path analysis for LEO3 LEO3表访问路径的不同代价
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for LEO3[LEO3]
Table: LEO3 Alias: LEO3
Card: Original: 71969.000000 Rounded: 71969 Computed: 71969.00 Non Adjusted: 71969.00
原始行数 近似值 精确值 非修正值
Access Path: TableScan 全表扫描代价
Cost: 286.71 Resp: 286.71 Degree: 0 总代价=286.71
Cost_io: 286.00 Cost_cpu: 22598123 总代价=IO代价+CPU代价
Resp_io: 286.00 Resp_cpu: 22598123 并行访问代价
Access Path: index (index (FFS)) 索引快速全扫描
Index: IDX_LEO3
resc_io: 45.00 resc_cpu: 9768589 串行访问代价=45（因为索引是串行存储的）
ix_sel: 0.000000 ix_sel_with_filters: 1.000000 ix_sel=1/DK=1/71969=0.000013 索引选择率
ix_sel_with_filters带过滤条件索引选择率
Access Path: index (FFS)
Cost: 45.31 Resp: 45.31 Degree: 1 索引并行访问代价=45.31>45(串行访问代价)
Cost_io: 45.00 Cost_cpu: 9768589 所以要选择串行访问
Resp_io: 45.00 Resp_cpu: 9768589 并行度=1
Access Path: index (FullScan) 索引全扫描
Index: IDX_LEO3
resc_io: 160.00 resc_cpu: 15533230 串行访问代价=160，这个比较高
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 160.49 Resp: 160.49 Degree: 1 并行度=1
Best:: AccessPath: IndexFFS
Index: IDX_LEO3
Cost: 45.31 Degree: 1 Resp: 45.31 Card: 71969.00 Bytes: 0
###############################################################################
Access path analysis for LEO4 LEO4表访问路径的不同代价
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for LEO4[LEO4]
Table: LEO4 Alias: LEO4
Card: Original: 99.000000 Rounded: 99 Computed: 99.00 Non Adjusted: 99.00
原始行数 近似值 精确值 非修正值
Access Path: TableScan 全表扫描代价
Cost: 3.00 Resp: 3.00 Degree: 0 总代价=3
Cost_io: 3.00 Cost_cpu: 56397 IO代价+CPU代价
Resp_io: 3.00 Resp_cpu: 56397 并行访问代价
Access Path: index (index (FFS)) 索引快速全扫描
Index: IDX_LEO4
resc_io: 2.00 resc_cpu: 19001 串行访问代价=2
ix_sel: 0.000000 ix_sel_with_filters: 1.000000 ix_sel=1/DK=1/99=0.01 索引选择率
ix_sel_with_filters带过滤条件索引选择率
Access Path: index (FFS)
Cost: 2.00 Resp: 2.00 Degree: 1 索引并行访问代价=2，并行度=1
Cost_io: 2.00 Cost_cpu: 19001
Resp_io: 2.00 Resp_cpu: 19001
Access Path: index (FullScan) 索引全扫描
Index: IDX_LEO4
resc_io: 1.00 resc_cpu: 26921 串行访问代价=1，这个最低，就是它了
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 1.00 Resp: 1.00 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_LEO4
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 99.00 Bytes: 0
关联查询—驱动表的选择
OPTIMIZER STATISTICS AND COMPUTATIONS 优化器的统计和计算
***************************************
GENERAL PLANS 选择执行计划
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: LEO4[LEO4]#0 LEO3[LEO3]#1 关联的对象
***************
Now joining: LEO3[LEO3]#1 现在要用leo4小表关联leo3大表，leo4做驱动表
***************
NL Join嵌套循环关联 leo4表中有99条，小表为驱动表
驱动表 Outer table: Card: 99.00 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes: 3
Access path analysis for LEO3
Inner table: LEO3 Alias: LEO3
Access Path: TableScan 全表扫描-嵌套循环关联COST=28253.17
NL Join: Cost: 28253.17 Resp: 28253.17 Degree: 1
Cost_io: 28183.00 Cost_cpu: 2237241142
Resp_io: 28183.00 Resp_cpu: 2237241142 并行访问代价
Access Path: index (index (FFS)) 索引快速全扫描
Index: IDX_LEO3
resc_io: 43.08 resc_cpu: 9768589 串行访问代价
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Inner table: LEO3 Alias: LEO3
Access Path: index (FFS)
NL Join: Cost: 4296.33 Resp: 4296.33 Degree: 1 并行访问
Cost_io: 4266.00 Cost_cpu: 967117228
Resp_io: 4266.00 Resp_cpu: 967117228
Access Path: index (AllEqJoinGuess)
Index: IDX_LEO3
resc_io: 1.00 resc_cpu: 8171
ix_sel: 0.000014 ix_sel_with_filters: 0.000014
NL Join (ordered): Cost: 100.03 Resp: 100.03 Degree: 1
Cost_io: 100.00 Cost_cpu: 835894
Resp_io: 100.00 Resp_cpu: 835894
Best NL cost: 100.03 leo4为驱动表，小表为驱动表，最后代价100.03
resc: 100.03 resc_io: 100.00 resc_cpu: 835894 串行方式的代价 IO代价+CPU代价
resp: 100.03 resp_io: 100.00 resc_cpu: 835894 并行方式的代价
Outer table: LEO4 Alias: LEO4
SM Join 先排序后合并关联
SM cost: 268.06 代价268.06
resc: 268.06 resc_io: 265.00 resc_cpu: 97470464
resp: 268.06 resp_io: 265.00 resp_cpu: 97470464
HA Join 哈希关联
HA cost: 47.03 代价47.03，最好是哈希代价最小
resc: 47.03 resc_io: 46.00 resc_cpu: 32949334
resp: 47.03 resp_io: 46.00 resp_cpu: 32949334
Best:: JoinMethod: Hash 最后关联方法选择：哈希hash
Cost: 47.03 Degree: 1 Resp: 47.03 Card: 99.00 Bytes: 8 返回记录数+字节
***************
Now joining: LEO4[LEO4]#0 现在要用leo3大表关联leo4小表，leo3做驱动表
***************
NL Join 嵌套循环关联 leo3表中有71969条，大表为驱动表
Outer table: Card: 71969.00 Cost: 45.31 Resp: 45.31 Degree: 1 Bytes: 5
Access path analysis for LEO4
Inner table: LEO4 Alias: LEO4
Access Path: TableScan
NL Join: Cost: 97632.61 Resp: 97632.61 Degree: 1
Cost_io: 97505.00 Cost_cpu: 4068618676
Resp_io: 97505.00 Resp_cpu: 4068618676
Access Path: index (index (FFS))
Index: IDX_LEO4
resc_io: 0.27 resc_cpu: 19001
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Inner table: LEO4 Alias: LEO4
Access Path: index (FFS)
NL Join: Cost: 19581.20 Resp: 19581.20 Degree: 1
Cost_io: 19538.00 Cost_cpu: 1377283224
Resp_io: 19538.00 Resp_cpu: 1377283224
Access Path: index (AllEqJoinGuess)
Index: IDX_LEO4
resc_io: 0.00 resc_cpu: 1050
ix_sel: 0.010101 ix_sel_with_filters: 0.010101
NL Join (ordered): Cost: 47.68 Resp: 47.68 Degree: 1
Cost_io: 45.00 Cost_cpu: 85336039
Resp_io: 45.00 Resp_cpu: 85336039
Best NL cost: 47.68 嵌套循环关联最后代价47.68
resc: 47.68 resc_io: 45.00 resc_cpu: 85336039
resp: 47.68 resp_io: 45.00 resc_cpu: 85336039
SM Join 先排序后合并关联
SM cost: 269.06 代价269.06
resc: 269.06 resc_io: 265.00 resc_cpu: 129384180
resp: 269.06 resp_io: 265.00 resp_cpu: 129384180
Hash join: Resc: 106.17 Resp: 106.17 [multiMatchCost=0.00] 哈希关联，代价=106.17
Final cost for query block SEL$1 (#0) - All Rows Plan:
Best join order: 1 最终代价选择47.0334，用leo4小表驱动表
Cost: 47.0334 Degree: 1 Card: 99.0000 Bytes: 792
Resc: 47.0334 Resc_io: 46.0000 Resc_cpu: 32949334
Resp: 47.0334 Resp_io: 46.0000 Resc_cpu: 32949334
SQL执行计划的选择
============
Plan Table
============
------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 47 | |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | HASH JOIN | | 99 | 792 | 47 | 00:00:01 |
| 3 | INDEX FULL SCAN | IDX_LEO4| 99 | 297 | 1 | 00:00:01 |
| 4 | INDEX FAST FULL SCAN | IDX_LEO3| 70K | 351K | 45 | 00:00:01 |
------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("LEO3"."OBJECT_ID"="LEO4"."OBJECT_ID")
选择的执行计划和上面分析结果是相匹配的
来看看我们真实的执行计划的样子
LEO1@LEO1> set autotrace trace exp
LEO1@LEO1> select count(*) from leo3,leo4 where leo3.object_id=leo4.object_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 172281424
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 47 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | HASH JOIN | | 99 | 792 | 47 (3)| 00:00:01 |
| 3 | INDEX FULL SCAN | IDX_LEO4 | 99 | 297 | 1 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN | IDX_LEO3 | 71969 | 351K| 45 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
小结：一模一样对吧，这说明我们的优化器在对比完不同代价后选择的执行计划是最优的，如果我们在实际工作中，遇到了执行计划选择错误的情景，我们可以通过10053事件来做详细的分析。
4.当统计信息不准确时，CBO可能产生错误的执行计划，请给出这样的一个例子，在10053 trace中找到CBO出错的位置，并给出必要的文字说明。
LEO1@LEO1> drop table leo5 purge; 清空环境
Table dropped.
LEO1@LEO1> create table leo5 as select * from dba_objects; 创建leo5表
Table created.
LEO1@LEO1> create index idx_leo5 on leo5(object_id); 创建B-tree索引
Index created.
为了让CBO产生错误的执行计划，我把leo5数据分布变的倾斜一些
LEO1@LEO1> select count(*) from leo5; 总记录数是72010
COUNT(*)
----------
72010
LEO1@LEO1> update leo5 set object_id=1 where object_id<70000; 我们更改了68840行，现在object_id=1 占 96%
68840 rows updated.
LEO1@LEO1> commit; 提交
LEO1@LEO1> update leo5 set object_id=2 where object_id>1;
3170 rows updated.
LEO1@LEO1> select count(*) from leo5 where object_id=1; object_id等于1的有68840
COUNT(*)
----------
68840
LEO1@LEO1> select count(*) from leo5 where object_id=2; object_id等于2的有3170
COUNT(*)
----------
3170
LEO1@LEO1> begin
dbms_stats.gather_table_stats( 对leo5进行表分析
wnname=>'leo1',
tabname=>'leo5',
cascade=>true,
estimate_percent=>null,
method_opt=>'for all columns size 254');
end;
/
2 3 4 5 6 7 8 9
PL/SQL procedure successfully completed.
LEO1@LEO1> select count(object_name) from leo5 where object_id=1; 查看执行计划信息
Execution Plan
----------------------------------------------------------
Plan hash value: 2750404108
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 287 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 28 | | |
|* 2 | TABLE ACCESS FULL| LEO5 | 68840 | 1882K| 287 (1)| 00:00:04 |
---------------------------------------------------------------------------
全表扫描68840，还是比较准确的，说明表分析生效了
LEO1@LEO1> select count(object_name) from leo5 where object_id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2542459021
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 57 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 28 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| LEO5 | 3170 | 88760 | 57 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_LEO5 | 3170 | | 11 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
对于比较少的行走索引也是正确的
LEO1@LEO1> update leo5 set object_id=3 where rownum<60000; 修改了一下object_id分布
59999 rows updated.
LEO1@LEO1> select count(*) from leo5 where object_id=1; object_id的值从68840变成了8857
COUNT(*)
----------
8857
LEO1@LEO1> commit; 提交
Commit complete.
LEO1@LEO1> select count(object_name) from leo5 where object_id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2750404108
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 287 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 28 | | |
|* 2 | TABLE ACCESS FULL| LEO5 | 68840 | 1882K| 287 (1)| 00:00:04 |
---------------------------------------------------------------------------
此时CBO依然选择走全表扫描，我们从记录数的变化上就可以知道应该走索引效率更高些，就像object_id=2的执行计划一样INDEX RANGE SCAN代价更小些，为什么CBO会选择了错误的执行计划呢？这是因为我们虽然修改了记录值但没有及时更新leo5表的对象统计信息，CBO还是使用了当初最早的统计信息，所以在计算COST的时候还是认为走全表扫描的代价最优。下面我们再把对象统计信息重新统计一下，得出最新的代价列表进行筛选。
LEO1@LEO1> begin
dbms_stats.gather_table_stats(
wnname=>'leo1',
tabname=>'leo5',
cascade=>true,
estimate_percent=>null,
method_opt=>'for all columns size 254');
end;
/
2 3 4 5 6 7 8 9
PL/SQL procedure successfully completed.
LEO1@LEO1> alter session set events '10053 trace name context forever,level 1'; 启动10053事件
Session altered.
LEO1@LEO1> select count(object_name) from leo5 where object_id=1; 执行SQL语句
COUNT(OBJECT_NAME)
------------------
8857
LEO1@LEO1> alter session set events '10053 trace name context off'; 关闭10053事件
Session altered.
LEO1@LEO1> select value from v$diag_info where name='Default Trace File'; 查看trace文件
VALUE
----------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_22298.trc
[oracle@leonarding1 trace]$ vim LEO1_ora_22298.trc 查看生成的trace文件内容
Table Stats::
Table: LEO5 Alias: LEO5
#Rows: 72010 #Blks: 1051 AvgRowLen: 75.00
Index Stats::
Index: IDX_ LEO5 Col#: 1
LVLS: 0 #LB: 1 #DK: 3 LB/K: 1.00 DB/K: 1.00 CLUF: 2.00
Access path analysis for LEO5
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for LEO5[LEO5]
Table: LEO5 Alias: LEO5
Card: Original: 72010.000000 Rounded: 72010 Computed: 72010.00 Non Adjusted: 72010.00
Access Path: TableScan
Cost: 287.55 Resp: 287.55 Degree: 0
Cost_io: 287.00 Cost_cpu: 22598123
Resp_io: 287.00 Resp_cpu: 22598123
Access Path: index (AllEqRange)
Index: IDX_LEO5
resc_io: 31.00 resc_cpu: 12862199
ix_sel: 0.333333 ix_sel_with_filters: 0.333333
Cost: 31.33 Resp: 31.33 Degree: 1
LEO1@LEO1> select count(object_name) from leo5 where object_id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2542459021
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 158 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 28 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| LEO5 | 8857 | 242K| 158 (0)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | IDX_LEO5 | 8857 | | 31 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
小结：经过对比CBO最终选择了索引，当我们更新完统计信息，CBO选择了正确的执行计划
10053 cost 执行计划 CBO 计算公式 10046 选择执行计划
为何不产生10053的trace文件？
如果一个SQL语句已经被解析过，那么就不会生成10053的trace文件，但10046的trace文件可以重复生成。
点击(此处)折叠或打开
- [oracle@rhel6lhr ~]$ sqlplus / as sysdba
-
- SQL*Plus: Release 11.2.0.3.0 Production on Fri May 26 20:24:43 2017
-
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
-
-
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
-
- SYS@orclasm > SELECT /*+ RULE */ * FROM SCOTT.EMP E,SCOTT.DEPT D WHERE D.DEPTNO >10 AND E.DEPTNO =23;
-
- no rows selected
-
- SYS@orclasm > alter session set events '10053 trace name context forever, level 1';
-
- Session altered.
-
- SYS@orclasm > SELECT /*+ RULE */ * FROM SCOTT.EMP E,SCOTT.DEPT D WHERE D.DEPTNO >10 AND E.DEPTNO =23;
-
- no rows selected
-
- SYS@orclasm > alter session set events '10053 trace name context OFF';
-
- Session altered.
-
- SYS@orclasm > SELECT VALUE FROM V$DIAG_INFO;
-
- VALUE
- --------------------------------------------------------------------------------
- TRUE
- /u01/app/oracle
- /u01/app/oracle/diag/rdbms/orclasm/orclasm
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/alert
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/incident
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/cdump
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/hm
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace/orclasm_ora_51012.trc
- 25
- 328
-
- 11 rows selected.
-
- SYS@orclasm > ! ls /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace/orclasm_ora_51012.trc
- ls: cannot access /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace/orclasm_ora_51012.trc: No such file or directory
-
- SYS@orclasm > exit
- Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- [oracle@rhel6lhr ~]$ sqlplus / as sysdba
-
- SQL*Plus: Release 11.2.0.3.0 Production on Fri May 26 20:25:55 2017
-
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
-
-
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
-
- SYS@orclasm > conn lhr/lhr
- Connected.
- LHR@orclasm > alter session set events '10053 trace name context forever, level 1';
-
- Session altered.
-
- LHR@orclasm > SELECT /*+ RULE */ * FROM SCOTT.EMP E,SCOTT.DEPT D WHERE D.DEPTNO >10 AND E.DEPTNO =23;
-
- no rows selected
-
- LHR@orclasm > alter session set events '10053 trace name context OFF';
-
- Session altered.
-
- LHR@orclasm > SELECT VALUE FROM V$DIAG_INFO;
-
- VALUE
- --------------------------------------------------------------------------------
- TRUE
- /u01/app/oracle
- /u01/app/oracle/diag/rdbms/orclasm/orclasm
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/alert
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/incident
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/cdump
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/hm
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace/orclasm_ora_51632.trc
- 25
- 328
-
- 11 rows selected.
-
- LHR@orclasm >
- LHR@orclasm > ! ls /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace/orclasm_ora_51632.trc
- ls: cannot access /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace/orclasm_ora_51632.trc: No such file or directory
-
-
- LHR@orclasm > set autot traceonly
- LHR@orclasm > SELECT /*+ RULE */ * FROM SCOTT.EMP E,SCOTT.DEPT D WHERE D.DEPTNO >10 AND E.DEPTNO =23;
-
- no rows selected
-
-
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2723707427
-
- -----------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -----------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 117 | 6 (0)| 00:00:01 |
- | 1 | MERGE JOIN CARTESIAN| | 1 | 117 | 6 (0)| 00:00:01 |
- |* 2 | TABLE ACCESS FULL | EMP | 1 | 87 | 3 (0)| 00:00:01 |
- | 3 | BUFFER SORT | | 3 | 90 | 3 (0)| 00:00:01 |
- |* 4 | TABLE ACCESS FULL | DEPT | 3 | 90 | 3 (0)| 00:00:01 |
- -----------------------------------------------------------------------------
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 2 - filter("E"."DEPTNO"=23)
- 4 - filter("D"."DEPTNO">10)
-
- Note
- -----
- - dynamic sampling used for this statement (level=2)
-
-
- Statistics
- ----------------------------------------------------------
- 46 recursive calls
- 6 db block gets
- 7 consistent gets
- 0 physical reads
- 2620 redo size
- 999 bytes sent via SQL*Net to client
- 509 bytes received via SQL*Net from client
- 1 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 0 rows processed
-
-
- LHR@orclasm > set autot off
- LHR@orclasm > SELECT ADDRESS,HASH_VALUE FROM V$SQLAREA a WHERE a.SQL_TEXT LIKE '%SELECT /*+ RULE */ * FROM SCOTT.EMP E,SCOTT.DEPT D WHERE D.DEPTNO >10 AND E.DEPTNO =23%' AND a.PLAN_HASH_VALUE=2723707427 AND a.SQL_TEXT NOT LIKE '%EXPLAIN PLAN%' ;
-
- ADDRESS HASH_VALUE
- ---------------- ----------
- 00000000AA1AA9F8 650753756
-
- LHR@orclasm > EXEC sys.DBMS_SHARED_POOL.PURGE('00000000AA1AA9F8,650753756','C');
-
- PL/SQL procedure successfully completed.
-
- LHR@orclasm >
- LHR@orclasm >
- LHR@orclasm >
- LHR@orclasm > exit
- Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- [oracle@rhel6lhr ~]$ sqlplus / as sysdba
-
- SQL*Plus: Release 11.2.0.3.0 Production on Fri May 26 20:45:05 2017
-
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
-
-
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
-
- SYS@orclasm >
- SYS@orclasm > alter session set events '10053 trace name context forever, level 1';
-
- Session altered.
-
- SYS@orclasm > SELECT /*+ RULE */ * FROM SCOTT.EMP E,SCOTT.DEPT D WHERE D.DEPTNO >10 AND E.DEPTNO =23;
- alter session set events '10053 trace name context OFF';
-
- no rows selected
-
- SYS@orclasm >
- Session altered.
-
- SYS@orclasm > SELECT VALUE FROM V$DIAG_INFO;
-
- VALUE
- --------------------------------------------------------------------------------
- TRUE
- /u01/app/oracle
- /u01/app/oracle/diag/rdbms/orclasm/orclasm
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/alert
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/incident
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/cdump
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/hm
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace/orclasm_ora_55619.trc
- 25
- 329
-
- 11 rows selected.
-
- SYS@orclasm > ! ls /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace/orclasm_ora_55619.trc
- /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace/orclasm_ora_55619.trc
-
- SYS@orclasm >
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
