深入解析10053事件-CSDN博客

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

优化器相关参数：

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

***************************************

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

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

基本统计信息:

下一部分是所有表和索引的基本统计信息

基本统计信息包括

表:

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

单表访问路径

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

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

横轴为db_file_multiblock_read_count，纵轴为K。

注意参数K仅仅用在全表扫描或快速索引扫描上，实际的I/O成本还与其他因数有关，比如说需要访问的表已经在内存中的块及块的数量。

过滤因子(FF)

为了理解索引访问成本我们需要了解一下过滤因子。过滤因子是一个介于0和1之间的数字，反映了记录的可选择性。如果一个列有10种不同的值，我们需要查询等于其中某一个值的记录时，如果这10种值平均分布的话，你将得到1/10的行数。如果没有直方图，过滤因子为FF = 1/NDV = density

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

不使用绑定变量的情况:

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) + 1/c1.num_distinct

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

c1 <= value (value - Lo) / (Hi - Lo) + 1/c1.num_distinct

c1 between val1 and val2 (val2 – val1) / (Hi - Lo) + 2 * 1/c1.num_distinct

使用绑定变量的情况（8i）:

predicate Filter factor

col1 = :b1 col1.density

col1 {like | > | >= | < | <=} :b1 {5.0000e-02 | col1.density }5

col1 between :b1 and :b2 5.0000e-02 * 5.0000e-02

包含and和or的情况:

predicate Filter factor

predicate 1 and predicate 2 FF1 * FF2

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

包含直方图的列：

如果一个列包含了直方图信息，那么它的density就来自于直方图。关于直方图的内容请参考官方手册，这里不在细述。由于直方图的存在FF并不是简单的等于1/NDV,而是来自于直方图中各个列的density，所有有直方图的话CBO将可能采取不一样的执行路径。

索引访问成本：

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