深入解析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 (
关闭：
SYS.DBMS_SYSTEM.SET_EV (
跟其他跟踪事件不同，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
_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 索引聚合因子
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 将会计算单表访问的成本
单表访问路径
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 中对应的列名如下
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
再来看一下过滤因子和查询条件的关系
不使用绑定变量的情况:
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):
col1 = :b1 col1.density
col1 {like | > | >= | < | <=} :b1 {5.0000e-02 | col1.
col1 between :b1 and :b2 5.0000e-02 * 5.0000e-
包含and 和or 的情况:
predicate 1 and predicate 2 FF1 * FF2
predicate 1 or predicate 2 FF1 + FF2 – FF1 * FF2
索引访问成本
现在我们知道了聚合因子的概念，我们再来看一看索引访问的成本
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
让我们用别的例子证明一下索引成本计算，语句为
where a.col#1 = :b1
and a.col#12 = :b2
and a.col#8 = :b3
索引和列的基本统计数据如下
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) ...........................................................
