【轉】OPTIMIZER_INDEX_COST_ADJ與成本計算

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.

 

[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>

建立測試表:

 

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.

 

我們分別觀察一下全表掃描和索引訪問的成本:

 

SQL> set autotrace traceonly


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

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=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 wner='EYGLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=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)

 

Oracle在選擇不同的訪問路徑時，會對全表掃描和索引掃描進行比較評估.

在比較的時候，Oracle會把索引掃描的成本轉換為全表掃描的成本，和全表掃描的COST進行比較.這個轉換需要一個轉換因子.
就是optimizer_index_cost_adj:

optimizer_index_cost_adj * (Index Scan Cost) = 等價的 Full Scan Cost

這個等價的 Full Scan Cost就是來和全表掃描成本進行比較的.

而這個轉換因子的臨界值實際上就是Full Scan Cost 和 Index Scan Cost的比值.

即:

optimizer_index_cost_adj = Full Scan Cost / Index Scan Cost

 

SQL> set autotrace off
SQL> select (14/16)*100 from dual;

(14/16)*100
-----------
87.5

1 row selected.

 

我們透過調整optimizer_index_cost_adj來看一下執行計劃的變化:

 

SQL> set autotrace traceonly

SQL> alter session set optimizer_index_cost_adj = 87;

Session altered.

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

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=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 wner='EYGLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=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選擇了全表掃描.