深入解析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/