对参数 optimizer_index_cost_adj 的一点理解_041009

参数optimizer_index_cost_adj设定了通过索引扫描和全表扫描之间的代价消耗关系，其默认值为100，表示ORACLE在计算查询成本时，通过索引查找和全表扫描成本相等。该参数值越小，表明通过索引查找的代价越小，反之则越大。

    通过以下试验，演示该参数的用法。

1) 查看参数optimizer_index_cost_adj的当前值

SQL> conn / as sysdba
Connected.
SQL> show parameter optimizer_index_cost_adj
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
optimizer_index_cost_adj             integer     100 --参数当前的默认值为100
SQL>
SQL> select isses_modifiable,issys_modifiable
2  from v$parameter
3  where name='optimizer_index_cost_adj';
ISSES ISSYS_MOD
----- ---------
TRUE  FALSE --说明该参数可以在SESSION级别动态改变，   但不能在SYSTEM级别动态改变

2) 创建表及索引，并对表进行分析

SQL> conn scott/tiger
Connected.
SQL> create table test(a number,b char(10));
Table created.
SQL>
SQL> begin
2    for i in 1..10000 loop
3      insert into test values(i,to_char(i));
4    end loop;
5    commit;
6  end;
7  /
PL/SQL procedure successfully completed.
SQL>
SQL> create index idx_test_a on test(a);
Index created.
SQL>
SQL> analyze table test compute statistics
2  for table
3  for all indexes
4  for all indexed columns;
Table analyzed.
SQL>

3) 将参数 optimizer_index_cost_adj 设为 100 ，查看相应的执行计划

SQL> set autotrace traceonly explain
SQL> alter session set optimizer_index_cost_adj=100;
Session altered.
SQL> select * from test where a=1;
Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=15)
2    1     INDEX (RANGE SCAN) OF 'IDX_TEST_A' (NON-UNIQUE) (Cost=1
Card=1)

    

在以上部分中，ORACLE采用了正确的查找方法，使用索引来查找数据。

    下面演示如何让 ORACLE 采用全表扫描的方式来查找数据，虽然我们明知这种方式效率不高。

4) 改变参数 optimizer_index_cost_adj 为 1000 ，改变 ORACLE 的决策过程

SQL> alter session set optimizer_index_cost_adj=1000;
Session altered.
SQL> select * from test where a=1;
Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=15)
1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=4 Card=1 Bytes=15)

    在以上演示中，通过改变 optimizer_index_cost_adj 参数， ORACLE 采用全表扫描来执行同样的查询。

5)总结
     在 OLTP 系统中，可以考虑将 optimizer_index_cost_adj 参数值设小，使系统倾向于使用索引；在 DSS 系统中，则可以考虑适当将该参数调大，影响 ORACLE 的决策过程。