OPTIMIZER_INDEX_COST_ADJ与成本计算

这个初始化参数代表一个百分比,取值范围在1到10000之间.

该参数表示
索引扫描 和
全表扫描成本的比较。缺省值100表示索引扫描成本等价转换与全表扫描成本。

这些参数对于CBO的执行具有重大影响，其缺省值对于数据库来说通常需要调整。

一般来说对于OPTIMIZER_INDEX_CACHING可以设置为90左右

对于大多数OLTP系统，OPTIMIZER_INDEX_COST_ADJ可以设置在10到50之间。对于数据仓库和DSS系统，

可能不能简单的把OPTIMIZER_INDEX_COST_ADJ设置为50，通常我们需要反复调整取得一个合理值.

更为具体的可以根据统计信息,db file scattered reads/db file sequential reads来计算.

本文通过实验对该参数的使用作出探讨和说明.

我们看到optimizer_index_cost_adj的缺省值为100.

PHP code:

---

[oracle@jumper udump]$ sqlplus eygle/eygle

SQL*Plus: Release 9.2.0.3.0 - Production on Mon Jun 28 17:11:15 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.3.0 - Production

SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE

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

optimizer_index_cost_adj integer 100

SQL> 
创建测试表:

PHP code:



SQL> create table t as select * from dba_objects;

Table created.

SQL> create index ind_owner on t(owner);

Index created.

SQL> analyze table t compute statistics;

Table analyzed.

.

PHP code:

---

SQL> set autotrace traceonly

SQL> select * from t where owner='EYGLE';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=476 Bytes=36652)

1 0 TABLE ACCESS (FULL) OF 'T' (Cost=14 Card=476 Bytes=36652)

SQL> select /*+ index(t ind_owner) */ * from t where owner='EYGLE';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=476 Bytes=36652)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=16 Card=476 Bytes=36652)

2 1 INDEX (RANGE SCAN) OF 'IND_OWNER' (NON-UNIQUE) (Cost=2 Card=476)

我们通过调整optimizer_index_cost_adj来看一下执行计划的变化:

PHP code:

---

SQL> set autotrace traceonly

SQL> alter session set optimizer_index_cost_adj = 87;

Session altered.

SQL> select * from t where owner='EYGLE';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=476 Bytes=36652)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=14 Card=476 Bytes=36652)

2 1 INDEX (RANGE SCAN) OF 'IND_OWNER' (NON-UNIQUE) (Cost=2 Card=476)

'此时使用索引成本较低.等价全表扫描成本为:

87% * (Index Scan Cost) < Full Scan Cost

此时Oracle选择了索引.'

SQL> alter session set optimizer_index_cost_adj = 88;

Session altered.

SQL> select * from t where owner='EYGLE';

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=476 Bytes=36652)

1 0 TABLE ACCESS (FULL) OF 'T' (Cost=14 Card=476 Bytes=36652)

'此时使用索引成本较高.等价全表扫描成本为:

88% * (Index Scan Cost) > Full Scan Cost

所以Oracle选择了全表扫描.'