在基于cbo的優化器模式下面,oracle會把sql語句的各種執行計劃的成本做比較,取其最小的作為其最優執行計劃,同時,在決定是用index
scan 還是full table scan 通路一個表的時候,oracle會把index
scan的執行成本轉化為fts的成本,然後再根據其成本大小來決定執行計劃,這個index
scan 和full table scan 的比值即為 optimizer_index_cost_adj,這個初始化參數代表一個百分比,取值範圍在1到10000之間.該參數表示索引掃描和全表掃描成本的比較。預設值100表示索引掃描成本等價轉換與全表掃描成本。
下面的例子具體說明了該參數的不同取值對執行計劃的影響(下面的例子不能做為性能調整的根據,隻是作為該參數的一個研究型文檔,實際在調整該參數的時候,還是參考應用的類型(oltp/olap),及v$system_event 中的db
file sequential read,db file scattered read的具體數值。
建立測試表
SQL> create table t as select * from dba_objects;
SQL> create index t_idx on t(owner);
SQL> SHOW PARAMETER OPTIMIZER_INDEX_COST_ADJ
NAME TYPE VALUE
------------------------------------ ----------- -----
optimizer_index_cost_adj integer 100
SQL> SHOW PARAMETER DB_FILE
db_file_multiblock_read_count integer 128
SQL> set autotrace traceonly explain;
SQL> select * from t where owner='HR';
Execution Plan
----------------------------------------------------------
Plan hash value: 1579008347
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT |
| 2500 |505K|92
(0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2500 |505K|92
|* 2 | INDEX RANGE SCAN | T_INX | 2500 |
| 8 (0)| 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='HR')
Note
-----
- dynamic sampling used for this statement (level=2)
專門走一次全表掃描,看下cost是多少:
SQL> select /*+ FULL(T) */ * FROM T WHERE owner='HR';
SQL> select /*+ FULL(T) */ * FROM T WHERE owner='HR';
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
| 0 | SELECT STATEMENT || 2500 | 505K| 1591
(1)| 00:00:20 |
|* 1 | TABLE ACCESS FULL| T| 2500 | 505K| 1591 (1)| 00:00:20 |
1 - filter("OWNER"='HR')
預設的optimizer_index_cost_adj
=100,全表掃描的成本是1591,索引範圍掃描的成本92,此時索引掃描的成本要比全表掃描的成本低,調整optimizer_index_cost_adj 參數,調整的基點: 全表掃描成本/索引掃描成本
SQL> SET AUTOTRACE OFF;
SQL> SELECT 1591/92*100 FROM DUAL;
1591/92*100
-----------
1729.34783
接下來,是重點,我們取一個比這個基數略大的數值:1731
SQL> ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=1731;
Session altered.
SQL>
SHOW PARAMETER OPTIMIZER_INDEX_COST_ADJ
NAME TYPE
VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer1731
set autotrace traceonly explain;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
| 0 | SELECT STATEMENT | | 2500 | 505K| 1591 (1)| 00:00:20 |
|* 1 | TABLE ACCESS FULL| T | 2500 | 505K| 1591 (1)| 00:00:20 |
可以看到,此時奇怪的事情發生了,這個查詢居然是走的全表掃描,而不是索引,這個參數到底是如何決定的呢?
原來: 在比較的時候,Oracle會把索引掃描的成本轉換為全表掃描的成本,和全表掃描的COST進行比較.這個轉換需要一個轉換因子.就是optimizer_index_cost_adj:
optimizer_index_cost_adj * (Index Scan Cost) = 等價的 Full Scan Cost(比如剛才1731*92=159252 > 1591*100,是以選擇了全表掃描,因為這個參數的權重讓oracle決定走全表掃描的cost更低)
這個 等價的 Full Scan Cost 就是來和全表掃描成本進行比較的.
而這個轉換因子的臨界值實際上就是Full Scan Cost 和 Index Scan Cost的比值.
即:
optimizer_index_cost_adj = Full Scan Cost / Index
Scan Cost(剛才1591/92 *100 =1731)
然後,我們把這個因子降低一點,看看是不是就會走索引呢?繼續實驗:
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=1725;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
| 0 | SELECT STATEMENT | | 2500 |
505K| 1589 (1)| 00:00:20 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2500 |505K| 1589 (1)| 00:00:20 |
|* 2 | INDEX RANGE SCAN | T_INX | 2500 | |
138 (0)| 00:00:02 |
果然,此時又走的索引,但是cost變成了1589(我們知道full table scan的成本是1591),是以選擇了成本較少的,那麼這個值是怎麼來的呢?
是實際的cost*optimizer_index_cost_adj 得到的(1589/1725=0.9211=92/100)
綜上,我們明白了這個參數可以了解為一個權重的值,這個值越高,index scan的權重越小,越傾向于使用full table scan。一般OLTP系統裡,建議設定較小的值10-50,這樣就會有更多的查詢走索引掃描,參考http://www.dba-oracle.com/oracle_tips_cost_adj.htm
For some OLTP systems, re-setting this parameter to a smaller value (between 10- to 30) may result in huge performance gains