ITPub博客

首页 > Linux操作系统 > Linux操作系统 > optimizer_index_cost_adj和optimizer_index_caching对CBO的影响

optimizer_index_cost_adj和optimizer_index_caching对CBO的影响

原创 Linux操作系统 作者:myownstars 时间:2012-07-25 14:56:14 0 删除 编辑

DB10205

OS:  HP UX B.11.11

目的:

测试optimizer_index_cost_adjoptimizer_index_cachingCBO生成执行计划的影响,前者的取值范围0-10000,默认为100;后者则为0-100,默认为0

关于这两个参数的资料比较少,只找到如下一些;

oracle高效设计》大致记录如下:optimizer_index_caching告诉数据库buffer cache中索引块的百分比,值越高则意味cache中的索引块数越多,其相应的io cost越小,optimizer_index_cost_adj则相反;

由冯大辉等人翻译的《oracle性能诊断艺术》则有着更详细的描述,书中提供了两个计算索引io代价公式

io_cost = (blevel + (leaf_blocks + clustering_factor) * selectivity) * optimizer_index_cost_adj/100

io_cost = {(blevel + leaf_blocks*selectivity) * (1 – optimizer_index_caching/100) + clustering_factor * selectivity} * (optimizer_index_cost_adj/100)

很直观的描述了两个参数对CBO的影响,且optimizer_index_cost_adjCBO的影响非常大,而optimizer_index_caching则相对小很多

 

下面做试验测试一下

设置三组测试案例,参数分别设置为

1 optimizer_index_cost_adj            =100optimizer_index_caching             =0

2 optimizer_index_cost_adj            =10optimizer_index_caching             =0

3 optimizer_index_cost_adj            =10 optimizer_index_caching             =100

 

创建测试表

SQL> create table tmp_t as select object_id,owner from dba_objects;

 

Table created.

SQL> select count(*) from tmp_t;

 

  COUNT(*)

----------

     41187

SQL> select count(*) from tmp_t where wner='PUBLIC';

 

  COUNT(*)

----------

     16036

SQL> create index tmp_index on tmp_t(owner);

 

Index created.

SQL> exec dbms_stats.gather_table_stats('SYS','TMP_T',estimate_percent => 100);

 

PL/SQL procedure successfully completed.

 

设置10053跟踪

1

optimizer_index_cost_adj            = 100

optimizer_index_caching             = 0

 

SQL> alter session set events '10053 trace name context forever, level 12';

 

Session altered.

 

SQL> set autotrace traceonly

SQL> select * from tmp_t where wner='PUBLIC';

 

16036 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1035233137

 

---------------------------------------------------------------------------

| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |       | 16036 |   187K|    23  (14)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TMP_T | 16036 |   187K|    23  (14)| 00:00:01 |

---------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("OWNER"='PUBLIC')

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       1121  consistent gets

          0  physical reads

          0  redo size

     354186  bytes sent via SQL*Net to client

      12251  bytes received via SQL*Net from client

       1071  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      16036  rows processed

 

SQL> alter session set events '10053 trace name context off';

 

Session altered.

跟踪文件

SYSTEM STATISTICS INFORMATION

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

  Using NOWORKLOAD Stats—使用的非工作量统计信息

  CPUSPEED: 199 millions instruction/sec

  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

  IOSEEKTIM: 10 milliseconds (default is 10)

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

BASE STATISTICAL INFORMATION

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

Table Stats::

  Table: TMP_T  Alias: TMP_T

    #Rows: 41187  #Blks:  49  AvgRowLen:  12.00

Index Stats::

  Index: TMP_INDEX  Col#: 2

    LVLS: 1  #LB: 52  #DK: 57  LB/K: 1.00  DB/K: 5.00  CLUF: 290.00

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

SINGLE TABLE ACCESS PATH

  -----------------------------------------

  BEGIN Single Table Cardinality Estimation

  -----------------------------------------

  Column (#2): OWNER(VARCHAR2)

    AvgLen: 8.00 NDV: 57 Nulls: 0 Density: 1.2140e-05

    Histogram: Freq  #Bkts: 57  UncompBkts: 41187  EndPtVals: 57

  Table: TMP_T  Alias: TMP_T    

    Card: Original: 41187  Rounded: 16036  Computed: 16036.00  Non Adjusted: 16036.00

  -----------------------------------------

  END   Single Table Cardinality Estimation

  -----------------------------------------

  Access Path: TableScan

    Cost:  23.42  Resp: 23.42  Degree: 0

      Cost_io: 20.00  Cost_cpu: 9538541

      Resp_io: 20.00  Resp_cpu: 9538541

  Access Path: index (AllEqRange)

    Index: TMP_INDEX

    resc_io: 134.00  resc_cpu: 7239716

    ix_sel: 0.38935  ix_sel_with_filters: 0.38935

    Cost: 136.60  Resp: 136.60  Degree: 1

  Best:: AccessPath: TableScan

         Cost: 23.42  Degree: 1  Resp: 23.42  Card: 16036.00  Bytes: 0

当两个参数设为默认值时,sql选择了意料之中的全表扫描,依据上文提到的公式计算一下

io_cost = (blevel + (leaf_blocks + clustering_factor) * selectivity) * optimizer_index_cost_adj/100 = (1 + (52  + 290) * 0.38935 ) * 1= 134.1

io_cost = {(blevel + leaf_blocks*selectivity) * (1 – optimizer_index_caching/100) + clustering_factor * selectivity} * (optimizer_index_cost_adj/100)

              =(( 1 + 52 * 0.38935 ) * 1 + 290 * 0.38935 ) * 1 = 134.1

CBO计算结果大致一样

 

2

optimizer_index_cost_adj            = 10

optimizer_index_caching             = 0

optimizer_index_cost_adj100改为10,依据上述公式,indexScanio cost应该为案例1中的10%

SQL> set autotrace traceonly

SQL> alter session set events '10053 trace name context forever, level 12';

 

Session altered.

SQL> select * from tmp_t where wner='PUBLIC';

 

16036 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1018746618

 

-----------------------------------------------------------------------------------------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           | 16036 |   187K|    14   (8)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TMP_T     | 16036 |   187K|    14   (8)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TMP_INDEX | 16036 |       |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OWNER"='PUBLIC')

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       2192  consistent gets

          0  physical reads

          0  redo size

     354186  bytes sent via SQL*Net to client

      12251  bytes received via SQL*Net from client

       1071  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      16036  rows processed

SQL> alter session set events '10053 trace name context off';

 

Session altered.

跟踪文件

 

  Access Path: TableScan

    Cost:  23.42  Resp: 23.42  Degree: 0

      Cost_io: 20.00  Cost_cpu: 9538541

      Resp_io: 20.00  Resp_cpu: 9538541

  Access Path: index (AllEqRange)

    Index: TMP_INDEX

    resc_io: 134.00  resc_cpu: 7239716

    ix_sel: 0.38935  ix_sel_with_filters: 0.38935

    Cost: 13.66  Resp: 13.66  Degree: 1

  Best:: AccessPath: IndexRange  Index: TMP_INDEX

         Cost: 13.66  Degree: 1  Resp: 13.66  Card: 16036.00  Bytes: 0

将参数代入公式

io_cost = (blevel + (leaf_blocks + clustering_factor) * selectivity) * optimizer_index_cost_adj/100 = (1 + (52  + 290) * 0.38935 ) * 1/10= 13.4

io_cost = {(blevel + leaf_blocks*selectivity) * (1 – optimizer_index_caching/100) + clustering_factor * selectivity} * (optimizer_index_cost_adj/100)

              =(( 1 + 52 * 0.38935 ) * 1 + 290 * 0.38935 ) * 1/10 = 13.4

实际结果和预料的一样,indexScancost下降为原来的十分之一,此时低于tableScan,故CBO选择了索引扫描

 

3

optimizer_index_cost_adj            = 10

optimizer_index_caching             = 100

Table Stats::

  Table: TMP_T  Alias: TMP_T

    #Rows: 41187  #Blks:  49  AvgRowLen:  12.00

Index Stats::

  Index: TMP_INDEX  Col#: 2

LVLS: 1  #LB: 52  #DK: 57  LB/K: 1.00  DB/K: 5.00  CLUF: 290.00

…….

  Access Path: TableScan

    Cost:  23.42  Resp: 23.42  Degree: 0

      Cost_io: 20.00  Cost_cpu: 9538541

      Resp_io: 20.00  Resp_cpu: 9538541

  Access Path: index (AllEqRange)

    Index: TMP_INDEX

    resc_io: 134.00  resc_cpu: 7239716

    ix_sel: 0.38935  ix_sel_with_filters: 0.38935

    Cost: 13.66  Resp: 13.66  Degree: 1

  Best:: AccessPath: IndexRange  Index: TMP_INDEX

         Cost: 13.66  Degree: 1  Resp: 13.66  Card: 16036.00  Bytes: 0

选择索引所用的cost根案例2的一样,没有发生变化

计算cost

io_cost = (blevel + (leaf_blocks + clustering_factor) * selectivity) * optimizer_index_cost_adj/100 = (1 + (52  + 290) * 0.38935 ) * 1/10= 13.4

io_cost = {(blevel + leaf_blocks*selectivity) * (1 – optimizer_index_caching/100) + clustering_factor * selectivity} * (optimizer_index_cost_adj/100)

              =(( 1 + 52 * 0.38935 ) * 0 + 290 * 0.38935 ) * 1/10 = 11.2

CBO在计算索引的io时,似乎更倾向于公式1,更改optimizer_index_caching对其影响微乎其微

 

 

其实更改这两个参数唯一可能影响的是CBO生成执行计划时到底选择全表扫描还是索引扫描,并不能更改两种访问路径的实际逻辑读,因此使用时应该倍加小心才对,至少目前我还没见过生产库上的应用案例。

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

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3103564