深入解析 oracle 10053事件.docx
《深入解析 oracle 10053事件.docx》由会员分享，可在线阅读，更多相关《深入解析 oracle 10053事件.docx（20页珍藏版）》请在冰豆网上搜索。
深入解析oracle10053事件
深入解析10053事件
你是否想知道一句sql语句如何执行，它是否走索引，是否采用不同得驱动表，是否用nestloopjoin,hashjoin…..?
这一切对你是否很神秘呢？
或许你会说executionplan能看到这些东西，但是你是否清楚executionplan是如何得到？
这篇文章就是给出了隐藏在executionplan底下的具体实现。
10053事件
10053事件是oracle提供的用于跟踪sql语句成本计算的内部事件，它能记载CBO模式下oracle优化器如何计算sql成本，生成相应的执行计划。
如何设置10053事件
设置本session的10053
开启：
Altersessionsetevents’10053tracenamecontextforever[,level{1/2}]’;
关闭：
Altersessionsetevents’10053tracenamecontextoff’;
设置其他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语句必须被hardparse并且必须使用CBO优化器模式。
如果sql语句已经被parse过，那么10053不生成跟踪信息。
如果你使用RULE优化器，那么10053也不会生成跟踪信息。
跟踪内容
跟踪文件包括6部分：
Sql语句
优化器相关参数
基本统计信息
基本表访问成本
综合计划
特殊功能的成本重计算
这篇文章将会涉及到前4项和一部分第5项的内容，我们将会用以下语句作为例子：
selectdname,enamefromemp,dept
whereemp.deptno=dept.deptno
andename=:
b1
sql语句：
这部分是整个跟踪文件里最容易理解的部分，包括了所执行的sql语句，如果你采用RULE模式优化器，那么除了这一部分外将不会有多余信息出现在跟踪文件里。
优化器相关参数：
记载了所有影响成本计算的参数
***************************************
PARAMETERSUSEDBYTHEOPTIMIZER
********************************
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
基本统计信息:
下一部分是所有表和索引的基本统计信息
基本统计信息包括
表:
Tracelabeldba_tablescolumn
CDNNUM_ROWS表记录数
NBLKSBLOCKS高水位以下的block数
TABLE_SCAN_CST全表扫描的I/O成本
AVG_ROW_LENAVG_ROW_LEN平均行长
索引:
Tracelabeldba_indexescolumn
Index#,col#索引号及表列号
LVLSBLEVELBTREE索引高度
#LBLEAF_BLOCKS索引叶块数
#DKDISTINCT_KEYS不重复索引关键字
LB/KAVG_LEAF_BLOCKS_PER_KEY叶块/关键字
DB/KAVG_DATA_BLOCKS_PER_KEY数据块/关键字
CLUFCLUSTERING_FACTOR索引聚合因子
***************************************
BASESTATISTICALINFORMATION
***********************
TablestatsTable:
DEPTAlias:
DEPT
TOTAL:
:
CDN:
16NBLKS:
1TABLE_SCAN_CST:
1AVG_ROW_LEN:
20
--Indexstats
INDEX#:
23577COL#:
1
TOTAL:
:
LVLS:
0#LB:
1#DK:
16LB/K:
1DB/K:
1CLUF:
1
***********************
TablestatsTable:
EMPAlias:
EMP
TOTAL:
:
CDN:
7213NBLKS:
85TABLE_SCAN_CST:
6AVG_ROW_LEN:
36
--Indexstats
INDEX#:
23574COL#:
1
TOTAL:
:
LVLS:
1#LB:
35#DK:
7213LB/K:
1DB/K:
1CLUF:
4125
INDEX#:
23575COL#:
2
TOTAL:
:
LVLS:
1#LB:
48#DK:
42LB/K:
1DB/K:
36CLUF:
1534
INDEX#:
23576COL#:
8
TOTAL:
:
LVLS:
1#LB:
46#DK:
12LB/K:
3DB/K:
34CLUF:
418
***************************************
基本表访问成本：
这里开始CBO将会计算单表访问的成本
单表访问路径
SINGLETABLEACCESSPATH.........................................................................................................................................1
Column:
ENAMECol#:
2Table:
EMPAlias:
EMP.....................................................................2
NDV:
42NULLS:
0DENS:
2.3810e-002...........................................................................3
TABLE:
EMPORIGCDN:
7213CMPTDCDN:
172........................................................................................4
Accesspath:
tscResc:
6Resp:
6............................................................................................................5
Accesspath:
index（equal）...............................................................................................................................6
INDEX#:
23575TABLE:
EMP...........................................................................................................................7
CST:
39IXSEL:
0.0000e+000TBSEL:
2.3810e-002.......................................................................8
BEST_CST:
6.00PATH:
2Degree:
1..............................................................................................................9
我们看一下上面是什么意思。
首先CBO列出了ename列的统计信息（第2，3行）,这些统计信息来自dba_tab_columns。
列的统计信息和dba_tab_columns中对应的列名如下
Tracelabeldba_tablescolumn
NDVNUM_DISTINCT列的不重复值数
NULLSNUM_NULLS列的空行数
DENSDENSITY列密度，没有直方图的情况下=1/NDV
LOLOW_VALUE列的最小值（只对数字列）
HIHIGH_VALUE列的最大值（只对数字列）
第4行出现了表的行数ORIGCDN和计算过的行数CMPTDCDN（computedcardinality）.计算公司如下，
CMPTDCDN=ORIGCDN*FF
在这里FF表示过滤因子（FilterFactor）。
我们稍后再来看FF是什么及如何计算的。
第5行表示了全表扫描的成本。
这里的成本是62,是由NBLKS和db_file_multi_block_read_count初始化参数计算出来的。
.
第6－8行是索引访问的成本。
第9行是总结了以上信息并选出了最优的访问路径为全表扫描，成本为6。
表扫描成本
让我们来看一下全表扫描成本（tsc）是如何计算的这里有其他两个大表的基本统计信息。
TOTAL:
:
CDN:
115630NBLKS:
4339TABLE_SCAN_CST:
265AVG_ROW_LEN:
272
TOTAL:
:
CDN:
454503NBLKS:
8975TABLE_SCAN_CST:
548AVG_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
再来看一下过滤因子和查询条件的关系
不使用绑定变量的情况:
predicateFilterfactor
c1=value1/c1.num_distinct4
c1likevalue1/c1.num_distinct
c1>value（Hi-value）/（Hi-Lo）
c1>=value（Hi-value）/（Hi-Lo）+1/c1.num_distinct
c1c1<=value（value-Lo）/（Hi-Lo）+1/c1.num_distinct
c1betweenval1andval2（val2–val1）/（Hi-Lo）+2*1/c1.num_distinct
使用绑定变量的情况（8i）:
predicateFilterfactor
col1=:
b1col1.density
col1{like|>|>=|<|<=}:
b1{5.0000e-02|col1.density}5
col1between:
b1and:
b25.0000e-02*5.0000e-02
包含and和or的情况:
predicateFilterfactor
predicate1andpredicate2FF1*FF2
predicate1orpredicate2FF1+FF2–FF1*FF2
包含直方图的列：
如果一个列包含了直方图信息，那么它的density就来自于直方图。
关于直方图的内容请参考官方手册，这里不在细述。
由于直方图的存在FF并不是简单的等于1/NDV,而是来自于直方图中各个列的density，所有有直方图的话CBO将可能采取不一样的执行路径。
索引访问成本：
现在我们知道了聚合因子的概念，我们再来看一看索引访问的成本
SINGLETABLEACCESSPATH.........................................................................................................................................1
Column:
ENAMECol#:
2Table:
EMPAlias:
EMP.....................................................................2
NDV:
42NULLS:
0DENS:
2.3810e-002...........................................................................3
TABLE:
EMPORIGCDN:
7213CMPTDCDN:
172........................................................................................4
Accesspath:
tscResc:
6Resp:
6............................................................................................................5
Accesspath:
index（equal）...............................................................................................................................6
INDEX#:
23575TABLE:
EMP...........................................................................................................................7
CST:
39IXSEL:
0.0000e+000TBSEL:
2.3810e-002.......................................................................8
BEST_CST:
6.00PATH:
2Degree:
1..............................................................................................................9
我们来看6-8行，这里表示了索引访问的成本。
第6行表示这里采取索引equal的方法来访问，再来回忆一下索引的基本统计信息
INDEX#:
23575COL#:
2
TOTAL:
:
LVLS:
1#LB:
48#DK:
42LB/K:
1DB/K:
36CLUF:
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，
其实索引访问方式的成本计算公式
•Uniquescanblevel+1
•Fastfullscanleaf_blocks/k（k=1.6765x0.6581）
•Index-onlyblevel+FF*leaf_blocks
让我们用别的例子证明一下索引成本计算，语句为
select…fromtbla
wherea.col#1=:
b1
anda.col#12=:
b2
anda.col#8=:
b3
索引和列的基本统计数据如下
INDEX#COL#LVLS#LB#DKLB/KDB/KCLUF
841727,1113100665001221469200
84181,12,7219000747001151176500
84193,1,4,22310004970012118000
157551,12,8112600188001301890275
84161,2,33,4,5,622580018903001183900
Col#:
1NDV:
10NULLS:
0DENS:
1.0000e-001-1
Col#:
12NDV:
8N
升级会员 