深入解析 oracle 10053事件.docx
- 文档编号:9089505
- 上传时间:2023-02-03
- 格式:DOCX
- 页数:20
- 大小:59.81KB
深入解析 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(
关闭:
SYS.DBMS_SYSTEM.SET_EV(
跟其他跟踪事件不同,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
c1 c1<=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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 深入解析 oracle 10053事件 深入 解析 10053 事件