optimizer_index_cost_adj参数的作用

optimizer_index_cost_adj
这个初始化参数optimizer_index_cost_adj用于改变通过索引扫描来访问表数据的成本.参数的有效值从1到10000.
缺省值是100.如果这个参数的值大于100那么使用索引扫描的成本更昂贵因而会选择执行全表扫描.如果这个参数值
小于100那么使用索引扫描的成本更低.

为了理解这个参数对成本计算公式的影响.下面将说明一下关于索引范围扫描的成本计算公式

索引范围扫描有几个关键步骤:
1.访问索引的根块
2.通过分支块来定位包含第一个键的叶子块
3.对于满足搜索条件的每一个索引健进行以下操作;
抽取rowid引用的数据块
通过rowid访问数据块.

一个索引范围扫描所有执行的物理读取的次数等于定位包含第一个键的叶子块所要访问的分支块的个数(命名为blevel)
加上要扫描的叶子块的个数(命名为leaf_blocks)乘以操作的选择性,加上通过rowid要访问的数据块的个数
(命名为clustering_factor)乘以操作的选择性.另外还有考虑初始化参数optimizer_index_cost_adj的影响
计算公式大致如下:

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

下面进行测试(查询语句为select * from test where object_id<200)

create table test as select * from dba_objects;

create index idx_object_id on test(object_id);

analyze table test compute statistics;

SQL> select LEAF_BLOCKS,BLEVEL ,clustering_factor from user_indexes where index_name='IDX_OBJECT_ID';

LEAF_BLOCKS     BLEVEL CLUSTERING_FACTOR
----------- ---------- -----------------
        161          1              1665

SQL> select low_value,high_value from user_tab_col_statistics where table_name='TEST' and   column_name='OBJECT_ID';

LOW_VALUE                                                        HIGH_VALUE
---------------------------------------------------------------- ----------------------------------------------- -----------------
C103                                                             C3083632

SQL>
SQL> select utl_raw.cast_to_number('C3083632') high_value from dual;

HIGH_VALUE
----------
     75349
SQL> select utl_raw.cast_to_number('C103') low_value from dual;

 LOW_VALUE
----------
         2

其实列的最大值与最小值可以直接查询

SQL>select min(object_id),max(object_id) from test;

MIN(OBJECT_ID) MAX(OBJECT_ID)
-------------- --------------
2              75349
计算选择性
<谓词的选择性=（limit- low_value）/（high_vlaue-low_value）
limit就是查询条件的值

SQL>select round((200-2)/(75349-2),5) selectivity from dual;

SELECTIVITY
-----------
0.00263

因为io_cost的计算方法如下:

io_cost=(blevel+(leaf_blocks+clustering_factor)*selectivity)*(optimizer_index_cost_adj/100)
SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
optimizer_index_cost_adj=100
blevel=1;
leaf_blocks=161
clustering_factor=1665
selecttivity=0.00263

SQL>select (1+ceil(161*(round((200-2)/(75349-2),5)))+ceil(1665*(round((200-2)/(75349-2),5))))*(100/100) cost
from dual;

COST
----------
7

SQL>conn jy/jy@jy_201
已连接。
SQL>set autotrace trace explain
SQL> select  * from test where object_id<200;

执行计划
----------------------------------------------------------
Plan hash value: 985375477

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

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

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

|   0 | SELECT STATEMENT            |               |   191 | 19100 |     7   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |   191 | 19100 |     7   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |   191 |       |     2   (0)| 00:00:01 |

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


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

   2 - access("OBJECT_ID"<200)

可以看到总的成本也和我们计算出来的一样也是7
当把optimizer_index_cost_adj设置为50时

SQL>alter session set optimizer_index_cost_adj=50;

Session altered.

SQL> show parameter optimizer_index_cost_adj;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 50

optimizer_index_cost_adj=50
blevel=1;
leaf_blocks=161
clustering_factor=1665
selecttivity=0.00263

SQL>select (1+ceil(161*(round((200-2)/(75349-2),5)))+ceil(1665*(round((200-2)/(75349-2),5))))*(50/100) cost from
dual;

COST
----------
3.5

我们计算出来是3.5四舍五入就是4与下面oracle计算的是一样

SQL> set autotrace trace explain
SQL> select  * from test where object_id<200;

Execution Plan
----------------------------------------------------------
Plan hash value: 985375477

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

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

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

|   0 | SELECT STATEMENT            |               |   191 | 19100 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |   191 | 19100 |     4   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |   191 |       |     1   (0)| 00:00:01 |

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


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

   2 - access("OBJECT_ID"<200)

初始化参数optimizer_index_cost_adj会直接影响索引访问的成本.当这个参数设置的值比缺省值小时,
所有成本按比例减小.在有些情况下可能出现问题因为查询优化器对评估结果做了round操作.这意味着即使
一些索引的对象统计不同,它们可能会有相同的成本.如果一些成本是相同的,那么查询优化器将会根据索引
的名字来选择使用的索引.也就是按索引的第一个字母进行排序.这个问题用一个例子来说明.当改变初始化
参数optimizer_index_cost_adj后索引范围扫描操作选择索引的变化.

drop table test purge;

SQL> create table test
  2  as
  3  select rownum as id,
  4         round(dbms_random.normal*10000) as val1,
  5         100+round(ln(rownum/3.25+2)) as val2,
  6         100+round(ln(rownum/3.25+2)) as val3,
  7         dbms_random.string('p',250) as pad
  8  from all_objects
  9  where rownum< =1000
 10  order by dbms_random.value;

SQL> create index idx_val2 on test (val2);

Index created.

Elapsed: 00:00:00.18
SQL> create index idx_val3 on test(val3);

Index created.

Elapsed: 00:00:00.09

SQL> show parameter optimizer_index_cost_adj

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj             integer     100

SQL> SELECT * FROM test WHERE val2 = 111 AND val3 = 111;
------------------------------------------------
| Id | Operation                   | Name     |
------------------------------------------------
|  0 | SELECT STATEMENT            |          |
|* 1 |  TABLE ACCESS BY INDEX ROWID| TEST     |
|* 2 |   INDEX RANGE SCAN          | IDX_VAL3 |
------------------------------------------------
1 - filter("VAL2"=11)
2 - access("VAL3"=11)

SQL> ALTER SESSION SET optimizer_index_cost_adj = 10;
SQL> SELECT * FROM test WHERE val1 = 111 AND val2 = 111;
------------------------------------------------
| Id | Operation                   | Name    |
------------------------------------------------
|  0 | SELECT STATEMENT            |         |
|* 1 |  TABLE ACCESS BY INDEX ROWID| TEST    |
|* 2 |   INDEX RANGE SCAN          | IDX_VAL2|
------------------------------------------------
1 - filter("VAL3"=111)
2 - access("VAL2"=111)

为了避免这种不稳定性,建议不要设置optimizer_index_cost_adj为一个很小的值.该参数是一个动态参数
可以在实例及会话级别进行修改.