深入理解Oracle索引(4)：optimizer_index_cost_adj详解

如 果哪天你遇到特殊的情况，比如系统数据字典的索引坏了，但是你去没有办法去修改这个索引，因为有些系统内部的东西你是动不了的
你就会说“我要是能绕过这个索引，全部走全表扫描，把库里的数据全部导出来该多好啊”
那么，这个时候，optimizer_index_cost_adj就很有用了



该参数表示全表扫描和索引扫描的百分比，取值范围∈[1,10000]，缺省为100
告诉优化器：走索引和走全表扫描的代价的比值是多少

默认值100意味着索引访问与全表扫描是完全等价的

sys@ORCL> show parameter optimizer_index

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100

OPTIMIZER_INDEX_CACHING参数是用来告诉Oracle一个索引被高速缓存的百分比的参数
OPTIMIZER_INDEX_COST_ADJ参数是用来告诉Oracle高速缓存多少表数据的参数
这个数越小，表访问单个块的成本就越低；反之，这个数越大，成本就越高
理解这个参数的一种办法是想像这个表反映多块I/O（与全表扫描）的成本与执行单块I/O（与索引读取有关）的成本
如果保持这个参数为默认的设置100，则多块I/O与单块I/O的成本相同
设置这个值为50导致优化程序考虑单块表访问的成本为多块I/O的一半，有效地把表访问的成本减为原来的一半

对Oracle而言，这个缺省值通常需要调整
一般来说，optimizer_index_caching可以设置为90左右
对于大多数OLTP,optimizer_index_cost_adj可以设置在10~50之间
对于OLAP和DSS系统，可能无法简单的把optimizer_index_cost_adj设置为50
通常我们可以根据统计信息，db file scattered reads/db file sequential reads来计算

--创建测试表

sys@ORCL> drop table t purge;

Table dropped.

sys@ORCL> create table t as select * from dba_objects;

Table created.

sys@ORCL> create index idx_t on t (owner);

Index created.

sys@ORCL> analyze table t compute statistics;

Table analyzed.

--分别观察全表扫和索引访问的成本

sys@ORCL> set autot trace exp

sys@ORCL> select /*+ full(t) */ * from t where owner='HR';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2098 |   178K|   155   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |  2098 |   178K|   155   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

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

sys@ORCL> select * from t where owner='HR';

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  2098 |   178K|    61   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  2098 |   178K|    61   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |  2098 |       |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

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

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

在评估的时候，Oracle会把索引扫描的成本转换为全表扫描的成本，和全表扫描的COST进行比较
这个转换需要一个转换因子，那便是：optimizer_index_cost_adj

optimizer_index_cost_adj*(Index Scan Cost)=等价的Full Table Scan Cost

这个等价的Full Table Scan Cost就是用来和全表扫描成本进行比较的

而这个转换因子的临界值实际上就是：

optimizer_index_cost_adj=Full Table Scan Cost/Index Scan Cost

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

sys@ORCL> set autot trace exp
sys@ORCL> alter session set optimizer_index_cost_adj=80;

Session altered.

sys@ORCL> select * from t where owner='HR';

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  2098 |   178K|    49   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  2098 |   178K|    49   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |  2098 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

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

sys@ORCL> select /*+ full(t) */ * from t where owner='HR';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2098 |   178K|   155   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |  2098 |   178K|   155   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

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

此时使用索引成本较低，等价的全表扫描成本为：

80%*（Index Scan Cost）= 81.6 < Table Full Scan Cost = 310

那么，此时Oracle选择索引

sys@ORCL> alter session set optimizer_index_cost_adj=280;

Session altered.

sys@ORCL> select * from t where owner='HR';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2098 |   178K|   155   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |  2098 |   178K|   155   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

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

sys@ORCL> select /*+ index(t idx_t) */ * from t where owner='HR';

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  2098 |   178K|   171   (0)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  2098 |   178K|   171   (0)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |  2098 |       |    14   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

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

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

280% * (Index Scan Cost) = 996.8 > Table Full Scan Cost=310

所以，此时Oracle选择了全表扫描


Instance级别优化器相关参数调整都需谨慎！！

optimizer_index_cost_adj越大，越偏向全表扫描，越小，越偏向使用索引

我们可以通过下面的方法模糊算出optimizer_index_cost_adj

sys@ORCL> ed
Wrote file afiedt.buf

  1  select a.average_wait a1,
  2         b.average_wait a2,
  3         round( ((a.average_wait/b.average_wait)*100) ) a3
  4  from
  5        (select d.kslednam EVENT,
  6                s.kslestim / (10000 * s.ksleswts) AVERAGE_WAIT
  7         from x$kslei s, x$ksled d
  8         where s.ksleswts != 0 and s.indx = d.indx) a,
  9        (select d.kslednam EVENT,
 10                s.kslestim / (10000 * s.ksleswts) AVERAGE_WAIT
 11         from x$kslei s, x$ksled d
 12         where s.ksleswts != 0 and s.indx = d.indx) b
 13  where a.event = 'db file sequential read'
 14* and b.event = 'db file scattered read'
 15  
sys@ORCL> /

        A1         A2         A3
---------- ---------- ----------
1.35507461  2.1632742         63

算出的A3就是该参数应该设置的值
但是这个公式却不可信,特别是在数据库运行时间不长或者运行时间太长的时候
在正常运行时,你可以抽一个时间间隔从v$system_event中抽取值出来计算,这样稍微准确一点
不过这个也只是一个参考值罢了

这个参数本来就是矛盾的，CBO根据它做成本计算，而这个参数又只是一个猜测，根本不准确
所以其实这个参数的存在，可以认为是Oracle不完善的地方，不得已让Oracle用户自己做一个假设值
Oracle在将来势必取消这个参数