oracle optimizer_index_cost_adj,对参数 optimizer_index_cost_adj 的一点理解_041009-数据库专栏,SQL Server...

参数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的决策过程。