ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 深入解析10053事件

深入解析10053事件

原创 Linux操作系统 作者:syzxlyx_cu 时间:2010-02-20 09:29:07 0 删除 编辑

   深入解析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将可能采取不一样的执行路径。

 

索引访问成本:

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

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

 

 

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

select … from tbl a

where a.col#1 = :b1

and a.col#12 = :b2

and a.col#8 = :b3

 

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

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) ...............................................................................................................................3

INDEX#: 15755 CST: 7209 IXSEL: 0.0000e+000 TBSEL: 3.7879e-003 ......................................4

Access path: index (scan) .................................................................................................................................5

INDEX#: 8416 CST: 10972 IXSEL: 1.0000e-001 TBSEL: 1.0000e-001 ........................................6

5个索引中,索引(#8417 and #8419) 将不会被考虑因为他们的首列不出现在查询条件中。.

 

INDEX# 8418

索引包含的3个列中只有2列出现在查询条件中,所以只用2列的DENS来计算过滤因子

FF = 1.0000e-001 * 1.2500e-001= 1.2500e-002

cost = lvl + FF*#LB + FF*clustering factor

= 2 + 19,000*1.2500e-002 + 1176500*1.2500e-002

= 2 + 237.5 + 14706.25 = 14945.75

INDEX# 15755

索引包含的3列都出现在查询条件中,用3列的DENS计算过滤因子

FF = 1.0000e-001 * 1.2500e-001 * 3.0303e-001 = 3.7879e-003

cost = lvl + FF*#LB + FF*clustering factor

= 1 + 12,600*3.7879e-003 + 1,890,275*3.7879e-003

= 2 + 47.73 + 7160.13 = 7208.86

INDEX# 8416

索引包含的3个列中只有1列出现在查询条件中,所以只用1列的DENS来计算过滤因子

FF = 1.0000e-001

cost = lvl + FF*#LB + FF*clustering factor

= 2 + 25,800*1.0000e-001+ 83,900*1.0000e-001

= 2 + 2580 + 8390 = 10972

虽然索引8416只有一列出现在查询条件中,但是它的成本还是低于索引8418,因为它的聚合因子(clustering factor)比较低,所以统计出来成本也比较低。关于聚合因子可以参考oracle官方文档。

 

 

 

 

 

 

 

 

综合计划:

这一部分开始是10053最大的一部分,在这里CBO会评估各种JOIN方式及顺序的成本。

 

1. NL - NESTED LOOP JOIN

join cost = cost of accessing outer table

+ (row number of outer table * cost of accessing inner table )

2. SM – SORT MERGE JOIN

join cost = (cost of accessing outer table + outer sort cost)

+ (cost of accessing inner table + inner sort cost)

3. HA – HASH JOIN

join cost = (cost of accessing outer table)

+ (cost of building hash table)

+ (cost of accessing inner table )

 

 

JOIN ORDER [N]

 

Join order[1]: DEPT [DEPT] EMP [EMP]

Now joining: EMP [EMP] *******

JOINS – NL

NL Join ..............................................................................................................................................................................1

Outer table: cost: 1 cdn: 16 rcz: 13 resp: 1..................................................................................2

Inner table: EMP ......................................................................................................................................................3

Access path: tsc Resc: 6 ...............................................................................................................................4

Join resc: 97 Resp: 97 ...............................................................................................................................5

Access path: index (join stp) ...........................................................................................................................6

INDEX#: 23575 TABLE: EMP ...........................................................................................................................7

CST: 39 IXSEL: 0.0000e+000 TBSEL: 2.3810e-002.......................................................................8

Join resc: 625 resp:625 .............................................................................................................................9

Access path: index (join index).....................................................................................................................10

INDEX#: 23576 TABLE: EMP .........................................................................................................................11

CST: 37 IXSEL: 0.0000e+000 TBSEL: 8.3333e-002.....................................................................12

Join resc: 593 resp:593 ...........................................................................................................................13

Access path: and-equal...................................................................................................................................14

CST: 19 ...............................................................................................................................................................15

Join resc: 305 resp:305 ...........................................................................................................................16

Join cardinality: 172 = outer (16) * inner (172) * sel (6.2500e-002) [flag=0].................17

Best NL cost: 97 resp: 97...............................................................................................................................18

第1行为JOIN方式

第2行为驱动表的成本,行数,行大小。这里的行数为16,平均行长原本为20,但是因为DEPT表包含(DEPTNO, DEPT, and LOC)3列但仅有DEPTNO,DEPT等2列需要被join,所以计算后平均行长为16,所以在这里也被称为low row size.

第3行到16行通过NL JOIN的成本计算公式,计算出几种不同join方法的成本。

1. Tablescan of EMP at a cost of 6:

cost = cost of outer + cardinality of outer * cost of inner = 1 + 16 * 6 = 97 lines 3 to 5

2. Scan of index 23575 on ENAME at a cost of 39:

cost = 1 + 16 * 39 = 625 lines 6 to 9

3. Scan of index 23576 on DEPTNO at a cost of 37:

cost = 1 + 16 * 37 = 593 lines 10 to 13

4. An “and-equal” access at a cost of 19:

cost = 1 + 16 * 19 = 305 lines 14 to 16

第17行CBO估算出这个JOIN结果集的记录数,它将被最为下一次join的输入。它的计算公式为

Join cardinality:= outer  * inner  * join selectivity

而join selectivity为

join selectivity = 1/max[ NDV(t1.c1), NDV(t2.c2) ]

* [ (card t1 - # t1.c1 NULLs) / card t1 ]

* [ (card t2 - # t2.c2 NULLs) / card t2 ]

Join cardinality只会被用于NL JOIN中,其他JOIN会采取不同办法。

最后在18行,CBO将会列出成本最低的NL JOIN的方法。

 

JOINS - SM

SM Join

Outer table:

resc: 1 cdn: 16 rcz: 13 deg: 1 resp: 1

Inner table: EMP

resc: 6 cdn: 172 rcz: 9 deg: 1 resp: 6

SORT resource Sort statistics

Sort width: 3 Area size: 43008 Degree: 1

Blocks to Sort: 1 Row size: 25 Rows: 16

Initial runs: 1 Merge passes: 1 Cost / pass: 2

Total sort cost: 2

SORT resource Sort statistics

Sort width: 3 Area size: 43008 Degree: 1

Blocks to Sort: 1 Row size: 20 Rows: 172

Initial runs: 1 Merge passes: 1 Cost / pass: 2

Total sort cost: 2

Merge join Cost: 10 Resp: 10

SM Join (with index on outer)

Access path: index (no sta/stp keys)

INDEX#: 23577 TABLE: DEPT

CST: 2 IXSEL: 1.0000e+000 TBSEL: 1.0000e+000

Outer table:

resc: 2 cdn: 16 rcz: 13 deg: 1 resp: 2

Inner table: EMP

resc: 6 cdn: 172 rcz: 9 deg: 1 resp: 6

SORT resource Sort statistics

Sort width: 3 Area size: 43008 Degree: 1

Blocks to Sort: 1 Row size: 20 Rows: 172

Initial runs: 1 Merge passes: 1 Cost / pass: 2

Total sort cost: 2

Merge join Cost: 10 Resp: 10

在SM JOIN中成本为

Cost of outer + cost of inner + sort cost for outer + sort cost for inner = 1+ 6 + 2 + 2 = 11.

在这里CBO减去1所以最终等于10。在第2个SM JOIN的方法下通过了已经排序的索引,所以成本为 2 + 6 + 0 (no sort on outer) + 2 = 10.

JOINS – HA

HA Join

Outer table:

resc: 1 cdn: 16 rcz: 13 deg: 1 resp: 1

Inner table: EMP

resc: 6 cdn: 172 rcz: 9 deg: 1 resp: 6

Hash join one ptn: 1 Deg: 1

hash_area: 32 buildfrag: 33 probefrag: 1 ppasses: 2

Hash join Resc: 8 Resp: 8

Join result: cost: 8 cdn: 172 rcz: 22

根据HA JOIN公式,计算出成本为

(cost of accessing outer table)+ (cost of building hash table)+ (cost of accessing inner table )

=1+6+1=8

所以在这里HA JOIN会被选做最优化的执行路径,SQL语句将会最终走HA JOIN.

多重JOIN:

如果出现大于两个表进行JOIN的情况,那么会有更多的join顺序被考虑,4个表join的话会有24种join顺序,5个表的话会有120个join顺序,n个表会有n!个join顺序。由于估算每种join顺序都会耗费cpu,所以oracle用一个初始化参数optimizer_max_permutations来限制最大计算join顺序。若想了解多重join的更多信息,请搜索相关sql调整的资料。

结论:

10053是一个很好的理解CBO工作机制的工具,如果辅以10046事件查看执行计划,那么整个sql语句从解析到执行的过程都一目了然了。
 

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

上一篇: 安装11gr2遭遇bug
请登录后发表评论 登录
全部评论

注册时间:2009-09-28

  • 博文量
    125
  • 访问量
    124464