optimizer_index_caching和optimizer_index_cost_adj两个参数说明

一、optimizer_index_cost_adj参数

优化器计算通过索引扫描访问表数据的cost开销，可以通过这个参数进行调整。参数可用值的范围为1到10000。默认值为100，超过100后越大则越会使索引扫描的COST开销越高（计算的），从而导致查询优化器更加倾向于使用全表扫描。相反，值越小于100，计算出来的索引扫描的开销就越低。

注意：

1、这里描述的开销，仅仅为优化器评估出来的而已，而非实际执行的开销；例如同样的SQL语句，同样的执行路径，修改这个参数以后，计算出来的cost不同，但是，SQL语句执行的实际物理路径、时间、逻辑读都是一样的。

2、这个参数影响优化器评估索引访问的IO开销

3、可以参见后面的示例进一步理解这个参数

二、optimizer_index_caching参数

用于在执行in-list遍历和嵌套循环连接时，优化器评估已经存在于buffer cache中的索引块的数量（以百分比的方式）。参数的取值范围是0到100，默认值为0,取值越大就越减少优化器在评估In-list和嵌套循环连接的索引扫描的开销COST。

1、这里的描述仅仅为优化器的评估结果。换句话说，它不是用来指定数据库实际缓存的每个索引块的数量

2、可以参见后面的示例进一步理解这个参数

三、索引范围扫描的成本计算公式（与两个参数相结合）

cost = { (blevel+leaf_blocks * effective index selectivity)*(1-optimizer_index_caching/100)+

          cluster_factor * effective table selectivity)*(optimizer_index_cost_adj/100) }

说明

1、 blevel=索引的层数，即dba_indexes视图中的blevel列值

2、 leaf_blocks为索引的叶子块数量,即 dba_indexes视图中的leaf_blocks列值

3、 effective index selectivity指的是SQL语句中用于索引扫描的 谓词 驱动条件的列字段的选择率（ where条件中）

4、cluster_factor为索引的聚簇因子（表示索引数据与表数据排序后的相近程度）

5、 effective table selectivity指的是SQL where条件中能够在索引上进行过滤的所有谓词列字段的选择率（可以理解为通过索引扫描后（包含驱动和过滤），最后通过rowid定位表的数量）

6、一般是4、5两个部分往往是整个计算公式中权重最大的因素。

注意

1、从上面的成本计算公式和说明，我们知道， optimizer_index_cost_adj参数对优化器的影响要远远大于 optimizer_index_caching参数

2、随着优化器越来越智能这两个参数的作用已经慢慢被 系统统计信息 给替换了。

3、10gR2开始，这两个参数的值尽量保持为默认值。如果实在需要调整，请严格测试！

四、optimizer_index_cost_adj参数示例

SQL >  create  table t  as  select rownum rn ,object_name  name  from dba_objects a  where rownum < 5000;
Table created.
SQL >  create  index t_idx  on t(rn);
Index created.
SQL >  BEGIN
   2  
   3    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME = > 'SYS',
   4                                  TABNAME = > 'T',
   5                                  ESTIMATE_PERCENT = > 30,
   6                                  METHOD_OPT = > 'FOR ALL COLUMNS SIZE 1',
   7                                  NO_INVALIDATE = > FALSE,
   8                                   CASCADE = > TRUE,
   9                                  DEGREE  = >  4); 
  10   END; 
  11   /
PL / SQL  procedure successfully completed.
SQL >   explain plan  for  select  *  from t  where rn < 200;
Explained.
SQL >  select  *  from  table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value:  470836197

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation                   |  Name  |  Rows  | Bytes | Cost (%CPU)|  Time     |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0 |  SELECT  STATEMENT            |       |    199 |   4179 |      3   ( 0)| 00:00: 35 |
|    1 |   TABLE  ACCESS  BY  INDEX ROWID| T     |    199 |   4179 |      3   ( 0)| 00:00: 35 |
| *   2 |    INDEX RANGE SCAN          | T_IDX |    199 |       |      2   ( 0)| 00:00: 24 |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Predicate Information (identified  by  operation id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    2  -  access( "RN" < 200)

14  rows selected.

SQL >  alter  session  set optimizer_index_cost_adj = 50;
Session altered.
SQL >  explain plan  for  select  *  from t  where rn < 200;
Explained.
SQL >  select  *  from  table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value:  470836197
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation                   |  Name  |  Rows  | Bytes | Cost (%CPU)|  Time     |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0 |  SELECT  STATEMENT            |       |    199 |   4179 |      2   ( 0)| 00:00: 18 |
|    1 |   TABLE  ACCESS  BY  INDEX ROWID| T     |    199 |   4179 |      2   ( 0)| 00:00: 18 |
| *   2 |    INDEX RANGE SCAN          | T_IDX |    199 |       |      1   ( 0)| 00:00: 12 |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Predicate Information (identified  by  operation id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    2  -  access( "RN" < 200)
14  rows selected.

SQL >  alter  session  set optimizer_index_cost_adj = 500;
Session altered.
SQL >   explain plan  for  select  *  from t  where rn < 200;
Explained.
SQL >  select  *  from  table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value:  1601196873
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation         |  Name |  Rows  | Bytes | Cost (%CPU)|  Time     |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0 |  SELECT  STATEMENT  |      |    199 |   4179 |      6   ( 0)| 00: 01: 10 |
| *   1 |   TABLE  ACCESS  FULL| T    |    199 |   4179 |      6   ( 0)| 00: 01: 10 |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Predicate Information (identified  by  operation id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    1  - filter( "RN" < 200)
13  rows selected.

五、optimizer_index_caching参数示例

IN-LIST示例

SQL >  set linesize  200 pagesize  9999
SQL >  explain plan  for  select  *  from t  where rn  in ( 1, 2, 3, 4, 5, 6, 7, 100, 130, 200, 240, 2000);
Explained.
SQL >  select  *  from  table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value:  3506647781
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation                    |  Name  |  Rows  | Bytes | Cost (%CPU)|  Time     |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0 |  SELECT  STATEMENT             |       |     12 |    252 |      4   ( 0)| 00:00: 43 |
|    1 |  INLIST ITERATOR             |       |       |       |            |          |
|    2 |    TABLE  ACCESS  BY  INDEX ROWID| T     |     12 |    252 |      4   ( 0)| 00:00: 43 |
| *   3 |     INDEX RANGE SCAN          | T_IDX |     12 |       |      3   ( 0)| 00:00: 31 |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Predicate Information (identified  by  operation id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    3  -  access( "RN" = 1  OR  "RN" = 2  OR  "RN" = 3  OR  "RN" = 4  OR  "RN" = 5  OR  "RN" = 6  OR
               "RN" = 7  OR  "RN" = 100  OR  "RN" = 130  OR  "RN" = 200  OR  "RN" = 240  OR  "RN" = 2000)
16  rows selected.
SQL >  alter  session  set optimizer_index_caching = 10;
Session altered.
SQL >   explain plan  for  select  *  from t  where rn  in ( 1, 2, 3, 4, 5, 6, 7, 100, 130, 200, 240, 2000);

Explained.

SQL >   select  *  from  table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value:  3506647781

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation                    |  Name  |  Rows  | Bytes | Cost (%CPU)|  Time     |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0 |  SELECT  STATEMENT             |       |     12 |    252 |      3   ( 0)| 00:00: 31 |
|    1 |  INLIST ITERATOR             |       |       |       |            |          |
|    2 |    TABLE  ACCESS  BY  INDEX ROWID| T     |     12 |    252 |      3   ( 0)| 00:00: 31 |
| *   3 |     INDEX RANGE SCAN          | T_IDX |     12 |       |      2   (0)| 00:00: 19 |   --可以看到cost确实降低了
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Predicate Information (identified  by  operation id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    3  -  access( "RN" = 1  OR  "RN" = 2  OR  "RN" = 3  OR  "RN" = 4  OR  "RN" = 5  OR  "RN" = 6  OR
               "RN" = 7  OR  "RN" = 100  OR  "RN" = 130  OR  "RN" = 200  OR  "RN" = 240  OR  "RN" = 2000)

16  rows selected

嵌套循环连接示例：

SQL >  explain plan  for  select  / * + use_nl(a b)  * /  *  from t a,t b  where a.rn =b.rn  and b. name = 'sss';
Explained.
SQL >   select  *  from  table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value:  752965310
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation                    |  Name  |  Rows  | Bytes | Cost (%CPU)|  Time     |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0 |  SELECT  STATEMENT             |       |      1 |     42 |      8   ( 0)| 00: 01: 34 |
|    1 |  NESTED LOOPS                |       |       |       |            |          |
|    2 |   NESTED LOOPS               |       |      1 |     42 |      8   ( 0)| 00: 01: 34 |
| *   3 |     TABLE  ACCESS  FULL         | T     |      1 |     21 |      6   ( 0)| 00: 01: 10 |
| *   4 |     INDEX RANGE SCAN          | T_IDX |      1 |       |      1   ( 0)| 00:00: 12 |
|    5 |    TABLE  ACCESS  BY  INDEX ROWID| T     |      1 |     21 |      2   ( 0)| 00:00: 24 |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Predicate Information (identified  by  operation id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    3  - filter( "B". "NAME" = 'sss')
    4  -  access( "A". "RN" = "B". "RN")
18  rows selected.
SQL >  alter  session  set optimizer_index_caching = 10;
Session altered.
SQL >  explain plan  for  select  / * + use_nl(a b)  * /  *  from t a,t b  where a.rn =b.rn  and b. name = 'sss';
Explained.
SQL >  select  *  from  table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value:  752965310
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation                    |  Name  |  Rows  | Bytes | Cost (%CPU)|  Time     |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0 |  SELECT  STATEMENT             |       |      1 |     42 |      7   ( 0)| 00: 01: 22 |
|    1 |  NESTED LOOPS                |       |       |       |            |          |
|    2 |   NESTED LOOPS               |       |      1 |     42 |      7   (0)| 00: 01: 22 |  --可以看到cost确实降低了
| *   3 |     TABLE  ACCESS  FULL         | T     |      1 |     21 |      6   ( 0)| 00: 01: 10 |
| *   4 |     INDEX RANGE SCAN          | T_IDX |      1 |       |      0   ( 0)| 00:00: 01 |
|    5 |    TABLE  ACCESS  BY  INDEX ROWID| T     |      1 |     21 |      1   ( 0)| 00:00: 12 |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Predicate Information (identified  by  operation id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    3  - filter( "B". "NAME" = 'sss')
    4  -  access( "A". "RN" = "B". "RN")

18  rows selected.



非in-list和嵌套循环操作，调整这个参数时，不会影响oracle优化器成本的运算，如下
SQL >  set linesize  200 pagesize  999
SQL >  explain plan  for  select  *  from t  where rn < 200;
Explained.
SQL >  select  *  from  table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value:  470836197
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation                   |  Name  |  Rows  | Bytes | Cost (%CPU)|  Time     |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0 |  SELECT  STATEMENT            |       |    199 |   4179 |      3   ( 0)| 00:00: 35 |
|    1 |   TABLE  ACCESS  BY  INDEX ROWID| T     |    199 |   4179 |      3   ( 0)| 00:00: 35 |
| *   2 |    INDEX RANGE SCAN          | T_IDX |    199 |       |      2   ( 0)| 00:00: 24 |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Predicate Information (identified  by  operation id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    2  -  access( "RN" < 200)
14  rows selected.
SQL >  alter  session  set optimizer_index_caching = 1;
Session altered.
SQL >  explain plan  for  select  *  from t  where rn < 200;
Explained.
SQL >  select  *  from  table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value:  470836197
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation                   |  Name  |  Rows  | Bytes | Cost (%CPU)|  Time     |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0 |  SELECT  STATEMENT            |       |    199 |   4179 |      3   ( 0)| 00:00: 35 |
|    1 |   TABLE  ACCESS  BY  INDEX ROWID| T     |    199 |   4179 |      3   ( 0)| 00:00: 35 |
| *   2 |    INDEX RANGE SCAN          | T_IDX |    199 |       |      2   (0)| 00:00: 24 |  --可以看到cost确实没有改变
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Predicate Information (identified  by  operation id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    2  -  access( "RN" < 200)
14  rows selected.