技术开发 频道

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

  优化器相关参数:

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

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

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

  单表访问路径 

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

  过滤因子(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) +

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

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

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

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

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

predicate Filter factor

  predicate
1 and predicate 2 FF1 * FF2

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

  索引访问成本

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

 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

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

 selectfrom 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) ...........................................................
0
相关文章