深入解析 oracle 10053事件.docx

1、深入解析 oracle 10053事件 深入解析10053事件你是否想知道一句sql语句如何执行，它是否走索引，是否采用不同得驱动表，是否用nestloop join,hash join.?这一切对你是否很神秘呢？或许你会说execution plan能看到这些东西，但是你是否清楚execution plan是如何得到？这篇文章就是给出了隐藏在execution plan底下的具体实现。10053事件10053事件是oracle提供的用于跟踪sql语句成本计算的内部事件，它能记载CBO模式下oracle优化器如何计算sql成本，生成相应的执行计划。如何设置10053事件设置本session的1

2、0053开启：Alter session set events10053 trace name context forever,level 1/2;关闭：Alter session set events10053 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跟踪级别越高信息越多），跟踪信

3、息将被记录到user_dump_dest目录底下。注意，要实现跟踪必须满足两个条件：sql语句必须被hard parse并且必须使用CBO优化器模式。如果sql语句已经被parse过，那么10053不生成跟踪信息。如果你使用RULE优化器，那么10053也不会生成跟踪信息。跟踪内容跟踪文件包括6部分：Sql语句优化器相关参数基本统计信息基本表访问成本综合计划特殊功能的成本重计算这篇文章将会涉及到前4项和一部分第5项的内容，我们将会用以下语句作为例子：select dname, ename from emp, deptwhere emp.deptno = dept.deptnoand ename

4、 = :b1sql语句：这部分是整个跟踪文件里最容易理解的部分，包括了所执行的sql语句，如果你采用RULE模式优化器，那么除了这一部分外将不会有多余信息出现在跟踪文件里。优化器相关参数：记载了所有影响成本计算的参数*PARAMETERS USED BY THE OPTIMIZER*OPTIMIZER_FEATURES_ENABLE = 8.1.6OPTIMIZER_MODE/GOAL = ChooseOPTIMIZER_PERCENT_PARALLEL = 0HASH_AREA_SIZE = 131072HASH_JOIN_ENABLED = TRUEHASH_MULTIBLOCK_IO_C

5、OUNT = 0OPTIMIZER_SEARCH_LIMIT = 5PARTITION_VIEW_ENABLED = FALSE_ALWAYS_STAR_TRANSFORMATION = FALSE_B_TREE_BITMAP_PLANS = FALSESTAR_TRANSFORMATION_ENABLED = FALSE_COMPLEX_VIEW_MERGING = FALSE_PUSH_JOIN_PREDICATE = FALSEPARALLEL_BROADCAST_ENABLED = FALSEOPTIMIZER_MAX_PERMUTATIONS = 80000OPTIMIZER_IND

6、EX_CACHING = 0OPTIMIZER_INDEX_COST_ADJ = 100QUERY_REWRITE_ENABLED = TRUEQUERY_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_SUBQ

7、UERY = 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 = TRUEQUERY_REWRITE_EXPRESSION = TRUE_IMPROVED_ROW_LENGTH_ENABLED = TRUE_USE_NOSEGMENT_INDEXES =

8、 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

9、_EQUALITY = FALSE_TABLE_SCAN_COST_PLUS_ONE = FALSE_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUEDB_FILE_MULTIBLOCK_READ_COUNT = 32SORT_AREA_SIZE = 131072基本统计信息:下一部分是所有表和索引的基本统计信息基本统计信息包括表:Trace label dba_tables columnCDN NUM_ROWS 表记录

10、数NBLKS BLOCKS 高水位以下的block数TABLE_SCAN_CST 全表扫描的I/O成本AVG_ROW_LEN AVG_ROW_LEN 平均行长索引:Trace label dba_indexes columnIndex#, 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 索引聚合因子

11、*BASE STATISTICAL INFORMATION*Table stats Table: DEPT Alias: DEPTTOTAL : CDN: 16 NBLKS: 1 TABLE_SCAN_CST: 1 AVG_ROW_LEN: 20- Index statsINDEX#: 23577 COL#: 1TOTAL : LVLS: 0 #LB: 1 #DK: 16 LB/K: 1 DB/K: 1 CLUF: 1*Table stats Table: EMP Alias: EMPTOTAL : CDN: 7213 NBLKS: 85 TABLE_SCAN_CST: 6 AVG_ROW_L

12、EN: 36- Index statsINDEX#: 23574 COL#: 1TOTAL : LVLS: 1 #LB: 35 #DK: 7213 LB/K: 1 DB/K: 1 CLUF: 4125INDEX#: 23575 COL#: 2TOTAL : LVLS: 1 #LB: 48 #DK: 42 LB/K: 1 DB/K: 36 CLUF: 1534INDEX#: 23576 COL#: 8TOTAL : LVLS: 1 #LB: 46 #DK: 12 LB/K: 3 DB/K: 34 CLUF: 418*基本表访问成本：这里开始CBO将会计算单表访问的成本单表访问路径SINGLE T

13、ABLE ACCESS PATH .1Column: ENAME Col#: 2 Table: EMP Alias: EMP.2NDV: 42 NULLS: 0 DENS: 2.3810e-002 .3TABLE: EMP ORIG CDN: 7213 CMPTD CDN: 172 .4Access path: tsc Resc: 6 Resp: 6.5Access path: index (equal) .6INDEX#: 23575 TABLE: EMP .7CST: 39 IXSEL: 0.0000e+000 TBSEL: 2.3810e-002.8BEST_CST: 6.00 PATH

14、: 2 Degree: 1.9我们看一下上面是什么意思。首先CBO列出了ename列的统计信息(第2，3行),这些统计信息来自dba_tab_columns。列的统计信息和dba_tab_columns中对应的列名如下Trace label dba_tables columnNDV NUM_DISTINCT 列的不重复值数NULLS NUM_NULLS 列的空行数DENS DENSITY 列密度，没有直方图的情况下= 1/NDVLO LOW_VALUE 列的最小值 (只对数字列)HI HIGH_VALUE 列的最大值 (只对数字列)第4行出现了表的行数ORIG CDN和计算过的行数 CMPTD

15、 CDN (computed cardinality). 计算公司如下，CMPTD CDN = ORIG CDN * FF在这里 FF 表示过滤因子（Filter Factor）。我们稍后再来看FF是什么及如何计算的。第5行表示了全表扫描的成本。 这里的成本是62, 是由NBLKS和db_file_multi_block_read_count初始化参数计算出来的。.第68行是索引访问的成本。第9行是总结了以上信息并选出了最优的访问路径为全表扫描，成本为6。表扫描成本让我们来看一下全表扫描成本(tsc)是如何计算的 这里有其他两个大表的基本统计信息。TOTAL : CDN: 115630 NBL

16、KS: 4339 TABLE_SCAN_CST: 265 AVG_ROW_LEN: 272TOTAL : 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 d

17、b_file_multi_block_read_count(这里的值是32，可以看前面参数那一页)另外一个表为NBLKS / TABLE_SCAN_CST = 8975 / 548 = 16.377全表扫描成本和db_file_multi_block_read_countCBO将会根据NBLKS和db_file_multiblock_read_count来估计全表扫描成本，但是db_file_multiblock_read_count通常会被打上折扣。实际上我们可以认为等式会是TABLE_SCAN_CST = NBLKS / k我们来看一下k和db_file_multiblock_read_c

18、ount 究竟有什么规律可寻。我们来做一个实验，使用不同的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的

19、行数。如果没有直方图，过滤因子为FF = 1/NDV = density再来看一下过滤因子和查询条件的关系不使用绑定变量的情况:predicate Filter factorc1 = value 1/c1.num_distinct4c1 like value 1/c1.num_distinctc1 value (Hi - value) / (Hi - Lo)c1 = value (Hi - value) / (Hi - Lo) + 1/c1.num_distinctc1 value (value - Lo) / (Hi - Lo)c1 | = | | = :b1 5.0000e-02 | co

20、l1.density 5col1 between :b1 and :b2 5.0000e-02 * 5.0000e-02包含and和or的情况:predicate Filter factorpredicate 1 and predicate 2 FF1 * FF2predicate 1 or predicate 2 FF1 + FF2 FF1 * FF2包含直方图的列：如果一个列包含了直方图信息，那么它的density就来自于直方图。关于直方图的内容请参考官方手册，这里不在细述。由于直方图的存在FF并不是简单的等于1/NDV,而是来自于直方图中各个列的density，所有有直方图的话CBO将可

21、能采取不一样的执行路径。索引访问成本：现在我们知道了聚合因子的概念，我们再来看一看索引访问的成本SINGLE TABLE ACCESS PATH .1Column: ENAME Col#: 2 Table: EMP Alias: EMP.2NDV: 42 NULLS: 0 DENS: 2.3810e-002 .3TABLE: EMP ORIG CDN: 7213 CMPTD CDN: 172 .4Access path: tsc Resc: 6 Resp: 6.5Access path: index (equal) .6INDEX#: 23575 TABLE: EMP .7CST: 39 IX

22、SEL: 0.0000e+000 TBSEL: 2.3810e-002.8BEST_CST: 6.00 PATH: 2 Degree: 1.9我们来看6-8行，这里表示了索引访问的成本。第6行表示这里采取索引equal的方法来访问，再来回忆一下索引的基本统计信息INDEX#: 23575 COL#: 2TOTAL : LVLS: 1 #LB: 48 #DK: 42 LB/K: 1 DB/K: 36 CLUF: 1534根据索引成本计算公式blevel + FF*leaf_blocks + FF*clustering_factor1 + 2.3810e-002-2*48 + 2.3810e-00

23、2-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 awhere a.col#1 = :b1and a.

24、col#12 = :b2and a.col#8 = :b3索引和列的基本统计数据如下INDEX# COL# LVLS #LB #DK LB/K DB/K CLUF8417 27,1 1 13100 66500 1 22 14692008418 1,12,7 2 19000 74700 1 15 11765008419 3,1,4,2 2 31000 49700 1 2 11800015755 1,12,8 1 12600 18800 1 30 18902758416 1,2,33,4,5,6 2 25800 1890300 1 1 83900Col#: 1 NDV: 10 NULLS: 0 DENS: 1.0000e-001-1Col#: 12 NDV: 8 N