深入理解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在将来势必取消这个参数