OPTIMIZER_INDEX_COST_ADJ與成本計算
2024-07-21 02:33:26
供稿：網(wǎng)友
OPTIMIZER_INDEX_COST_ADJ
這個初始化參數(shù)代表一個百分比,取值范圍在1到10000之間.
該參數(shù)表示索引掃描和全表掃描成本的比較。缺省值100表示索引掃描成本等價轉(zhuǎn)換與全表掃描成本。
這些參數(shù)對于CBO的執(zhí)行具有重大影響，其缺省值對于數(shù)據(jù)庫來說通常需要調(diào)整。
一般來說對于OPTIMIZER_INDEX_CACHING可以設置為90左右
對于大多數(shù)OLTP系統(tǒng)，OPTIMIZER_INDEX_COST_ADJ可以設置在10到50之間。對于數(shù)據(jù)倉庫和DSS系統(tǒng)，可能不能簡單的把OPTIMIZER_INDEX_COST_ADJ設置為50，通常我們需要反復調(diào)整取得一個合理值.
更為具體的可以根據(jù)統(tǒng)計信息,db file scattered reads/db file sequential reads來計算.
本文通過實驗對該參數(shù)的使用作出探討和說明.
我們看到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>
創(chuàng)建測試表:
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 owner='EYGLE';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=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 owner='EYGLE';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=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會把索引掃描的成本轉(zhuǎn)換為全表掃描的成本，和全表掃描的COST進行比較.這個轉(zhuǎn)換需要一個轉(zhuǎn)換因子.
就是optimizer_index_cost_adj:
optimizer_index_cost_adj * (Index Scan Cost) = 等價的 Full Scan Cost
這個 等價的 Full Scan Cost 就是來和全表掃描成本進行比較的.
而這個轉(zhuǎn)換因子的臨界值實際上就是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.
我們通過調(diào)整optimizer_index_cost_adj來看一下執(zhí)行計劃的變化:
SQL> set autotrace traceonly
SQL> alter session set optimizer_index_cost_adj = 87;
Session altered.
SQL> select * from t where owner='EYGLE';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=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 owner='EYGLE';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=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選擇了全表掃描.
