关于参数optimizer_index_cost_adj的设置
概述:
参数optimizer_index_cost_adj的默认值是100，值域是0-10000的任意值。参数数值越低，越可能让优化器使用索引。

注意：
1）一般建议在会话级别设置该参数
2）在oltp系统建议使用很低的参数值，如5或者10，以强制优化器使用索引

数据库版本信息

SQL> select * from gv$version;

   INST_ID BANNER
---------- --------------------------------------------------------------------------------
         1 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
         1 PL/SQL Release 11.2.0.3.0 - Production
         1 CORE 11.2.0.3.0      Production
         1 TNS for Linux: Version 11.2.0.3.0 - Production
         1 NLSRTL Version 11.2.0.3.0 - Production

参数默认值

SQL> show parameter optimizer_index_cost_adj

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

关于该参数值的计算

SQL> select  event,average_wait from v$system_event where event like 'db file s%read';

EVENT                                                            AVERAGE_WAIT
---------------------------------------------------------------- ------------
db file sequential read                                                  1.28
db file scattered read                                                    2.2

SQL> select (1.28/2.2)*100 pct from dual;

       PCT
----------
58.1818182

故参数值应该设置为58左右

下边测试一下该参数的用途

SQL> create table yallonking as select rownum id,'yallonking' name from dual connect by rownum<101;

Table created.

SQL> create index idx_yallonking_id on yallonking(id);

Index created.

SQL> set autot traceonly
SQL> select * from yallonking where id<50;

49 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2582482548

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |    49 |  1225 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| YALLONKING |    49 |  1225 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter("ID"<50)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         10  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
       1587  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         49  rows processed

下边修改该参数

SQL> alter session set optimizer_index_cost_adj=58;

Session altered.

继续执行上边的sql，会发现此时该sql开始走索引，故该参数在这时起了作用。

SQL> select * from yallonking where id<50;

49 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2645553055

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |    49 |  1225 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| YALLONKING        |    49 |  1225 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_YALLONKING_ID |    49 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   2 - access("ID"<50)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
       2121  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         49  rows processed

以下是在内存中缓存数据后的一个对比的例子

SQL> alter session set optimizer_index_cost_adj=90;

Session altered.

SQL> select * from yallonking where id<50;

49 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2645553055

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |    49 |  1225 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| YALLONKING        |    49 |  1225 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_YALLONKING_ID |    49 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   2 - access("ID"<50)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
       2121  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         49  rows processed

SQL> alter session set optimizer_index_cost_adj=100;

Session altered.

SQL> select * from yallonking where id<50;

49 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2582482548

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |    49 |  1225 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| YALLONKING |    49 |  1225 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter("ID"<50)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1587  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         49  rows processed

该例子说明，在全表扫描的时候才需要7次一致性读，但是走索引确有16次一致性读以及9次递归调用。因为测试查询的内容不完全在索引中，所以在使用索引的时候，oracle不但要读取数据块，而且还得读取索引块，继而增加了块的读的次数。

小结：参数optimizer_index_cost_adj会影响数据库对索引的使用的选择性。但是，该参数的调整需要十分谨慎，因为在oracle更高的版本中基于cost的优化器的选择往往比人为做出的决定更加准确。