ORACLE诊断事件及深入解析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 模式优化器，那么除了这一部分外将不会有多余信息出现在跟踪文件里。

　　优化器相关参数：

　　记载了所有影响成本计算的参数　

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt ***************************************

　　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

　　_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

　　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

　　_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 索引聚合因子　

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt ***************************************

　　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 将会计算单表访问的成本

　　单表访问路径　

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt 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 中对应的列名如下　

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt 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 来测试。

　　过滤因子(FF)

　　为了理解索引访问成本我们需要了解一下过滤因子。 过滤因子是一个介于0 和1 之

　　间的数字，反映了记录的可选择性。如果一个列有10 种不同的值，我们需要查询等

　　于其中某一个值的记录时，如果这10 种值平均分 布的话，你将得到1/10 的行数。

　　如果没有直方图，过滤因子为FF = 1/NDV = density

　　再来看一下过滤因子和查询条件的关系

　　不使用绑定变量的情况:　

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt 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) +

　　c1 < value (value - Lo) / (Hi - Lo)

　　c1 <= value (value - Lo) / (Hi - Lo) +

　　c1 between val1 and val2 (val2 – val1) / (Hi - Lo) +

　　使用绑定变量的情况(8i):　

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt 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 的情况:　

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt predicate Filter factor

　　predicate 1 and predicate 2 FF1 * FF2

　　predicate 1 or predicate 2 FF1 + FF2 – FF1 * FF2

　　索引访问成本

　　现在我们知道了聚合因子的概念，我们再来看一看索引访问的成本

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt 　 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

　　让我们用别的例子证明一下索引成本计算，语句为

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt 　 select … from tbl a

　　 where a.col# 1 = :b1

　　 and a.col# 12 = :b2

　　 and a.col# 8 = :b3

　　索引和列的基本统计数据如下

　　

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt 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) ...........................................................

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

转载于:http://blog.itpub.net/11134237/viewspace-666886/