深入解析10053事件

 深入解析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来测试。

图请见WORD版本

横轴为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.
col1 between :b1 and :b2 5.0000e-02 * 5.0000e-
包含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语句从解析到执行的过程都一目了然了。

reference:
A LOOK UNDER THE HOOD OF CBO: THE 10053 EVENT
Wolfgang Breitling, Centrex Consulting Corporation

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20542911/viewspace-621866/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20542911/viewspace-621866/