关于Optimizer_index_cost_adj参数的设置

Thomas建议:

对于许多系统,应到考虑设置这两个参数为非默认值,至少测试一下两种极端情形:

1. optimizer_index_caching=0 和 optimizer_index_cost_adj=100的默认值. 他们一般适用于许多数据仓库/报表系统

2. otpimizer_index_caching=90和optimizer_index_cost_adj=25的设置,他们一般适用于许多事物处理系统/oltp系统.

对于数据仓库和DSS系统要反复调整来取一个合理值。

Oracle在选择不同的访问路径时，会对全表扫描和索引扫描进行比较评估，在比较的时候，

Oracle会把索引扫描的成本转换为全表扫描的成本，和全表扫描的COST进行比较。这个转换需要一个转换因子，就是Optimizer_index_cost_adj;

Optimizer_index_cost_adj*(index scan cost)=等价的Full Scan cost

所以 optimizer_index_cost_adj = Full Scan Cost / Index Scan Cost

在缺省情况下：Optimizer_index_cost_adj=100

SQL> show parameter optimizer_index_cost_adj

NAME                                 TYPE        VALUE

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

optimizer_index_cost_adj             integer     100

SQL> create table t3 as select * from dba_objects;

Table created

SQL>

SQL> create index ind_owner on t3(owner);

Index created

SQL> analyze table t3 compute statistics;

SQL> set autotrace on

SQL> set timing on

SQL> set autotrace traceonly

SQL> alter session set optimizer_index_cost_adj=43;

Session altered.

select  /*+ FULL(t3)*/* from bia_stg.t3 t3 where owner='BIA_STG';

322 rows selected.

Execution Plan

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

Plan hash value: 2574254479

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

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

|   0 | SELECT STATEMENT  |      |  3044 |   255K|   130   (2)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T3   |  3044 |   255K|   130   (2)| 00:00:02 |

Predicate Information (identified by operation id):

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

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

Statistics

          0  recursive calls

          0  db block gets

        739  consistent gets

          0  physical reads

          0  redo size

      23185  bytes sent via SQL*Net to client

        700  bytes received via SQL*Net from client

         23  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        322  rows processed

SQL> select /*+ index(t3 ind_owner)*/* from bia_stg.t3 t3 where owner='BIA_STG';

Plan hash value: 2790462862

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

---------

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

ime     |

|   0 | SELECT STATEMENT            |           |  3044 |   255K|    89   (0)| 0

0:00:02 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T3        |  3044 |   255K|    89   (0)| 0

|*  2 |   INDEX RANGE SCAN          | IND_OWNER |  3044 |       |     8   (0)| 0

0:00:01 |

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

         65  consistent gets

optimizer_index_cost_adj = Full Scan Cost / Index Scan Cost* 100=130/89 * 100=1.460 * 100

=146

及optimizer_index_cost_adj调整为147