Oracle 10g(10.1.0.2)中的OPTIMIZER_INDEX_COST_ADJ(轉)

Oracle 10g(10.1.0.2)中的OPTIMIZER_INDEX_COST_ADJ

Tom Kyte的新書Effective Oracle by Design的第6章 Getting the Most Out of the Cost-Based Optimizer中介紹了引數OPTIMIZER_INDEX_COST_ADJ，並認為可以理解為Oracle執行多塊（MultiBlock）I/O（比如全表掃描）的代價與執行單塊（Single-block）I/O代價的相對比例。Tom指出，9i下該引數預設值為100，意即二者擁有相同的代價，若減小，則CBO傾向於使用索引（即單塊I/O），反之則傾向於全表掃描（多塊I/O）。Tom同樣給出了一個不錯的例子。
在自己的手提上試驗了一下，硬碟大小原因，只裝了一個Oracle 10g(10.1.0.2)，誰知就發現該引數在10g下取值發生了改變。我們先來看一下參照Tom的實驗：
A. 建立資料表。
SQL> drop table t1;

表已刪除。

SQL> drop table t2;

表已刪除。

SQL> create table t1
2 as
3 select mod(rownum,1000) id,rpad('x',300,'x') data
4 from all_objects
5 where rownum<=5000;

表已建立。

SQL> ed
已寫入 file afiedt.buf

1 create table t2
2 as
3 select rownum id,rpad('x',300,'x') data
4 from all_objects
5* where rownum<=1000
SQL> /

表已建立。

B.建立索引並分析。
SQL> create index idx_t1 on t1(id);

索引已建立。

SQL> create index idx_t2 on t2(id);

索引已建立。

SQL> ed
已寫入 file afiedt.buf

1 begin
2 dbms_stats.gather_table_stats
3 (user,'T1',method_opt=>'for all indexed columns',cascade=>true);
4 dbms_stats.gather_table_stats
5 (user,'T2',method_opt=>'for all indexed columns',cascade=>true);
6* end;
SQL> /

PL/SQL 過程已成功完成。

C.查詢預設值，並設定好環境。
SQL> set autot off
SQL> show parameters optimizer_index_cost_adj;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
SQL> set autot traceonly exp stat;

D.在預設值下查詢的結果。
SQL> ed
已寫入 file afiedt.buf

1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /

已選擇30行。


執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=5 Bytes=1000
)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=2 Card
=1 Bytes=100)

2 1 NESTED LOOPS (Cost=13 Card=5 Bytes=1000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=3
Card=5 Bytes=500)

4 3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=
5)

5 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)




統計資訊
----------------------------------------------------------
367 recursive calls
0 db block gets
101 consistent gets
0 physical reads
0 redo size
1507 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
30 rows processed

E.修改引數值。

SQL> alter session set optimizer_index_cost_adj=1;

會話已更改。

SQL> ed
已寫入 file afiedt.buf

1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /

已選擇30行。


執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=1000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=1 Card
=1 Bytes=100)

2 1 NESTED LOOPS (Cost=2 Card=5 Bytes=1000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=1
Card=5 Bytes=500)

4 3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=
5)

5 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)




統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
1507 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed

SQL> alter session set optimizer_index_cost_adj=50;

會話已更改。

SQL> ed
已寫入 file afiedt.buf

1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /

已選擇30行。


執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=5 Bytes=1000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=1 Card
=1 Bytes=100)

2 1 NESTED LOOPS (Cost=7 Card=5 Bytes=1000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=2
Card=5 Bytes=500)

4 3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=
5)

5 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)




統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
1507 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed

F.繼續修改引數值，改大。
SQL> alter session set optimizer_index_cost_adj=200;

會話已更改。

SQL> ed
已寫入 file afiedt.buf

1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /

已選擇30行。


執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=5 Bytes=1000
)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=4 Card
=1 Bytes=100)

2 1 NESTED LOOPS (Cost=26 Card=5 Bytes=1000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=6
Card=5 Bytes=500)

4 3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=
5)

5 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)




統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
1507 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed

SQL> alter session set optimizer_index_cost_adj=500;

會話已更改。

SQL> ed
已寫入 file afiedt.buf

1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /

已選擇30行。


執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=63 Card=5 Bytes=1000
)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=10 Car
d=1 Bytes=100)

2 1 NESTED LOOPS (Cost=63 Card=5 Bytes=1000)
3 2 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 By
tes=500)

4 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)




統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
90 consistent gets
0 physical reads
0 redo size
1507 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed

SQL> alter session set optimizer_index_cost_adj=1000;

會話已更改。

SQL> ed
已寫入 file afiedt.buf

1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /

已選擇30行。


執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=5 Bytes=1000
)

1 0 HASH JOIN (Cost=66 Card=5 Bytes=1000)
2 1 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 Byte
s=500)

3 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=52 Card=26 Byt
es=2600)





統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
271 consistent gets
213 physical reads
0 redo size
1651 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed

SQL> alter session set optimizer_index_cost_adj=10000;

會話已更改。

SQL> ed
已寫入 file afiedt.buf

1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /

已選擇30行。


執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=5 Bytes=1000
)

1 0 HASH JOIN (Cost=66 Card=5 Bytes=1000)
2 1 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 Byte
s=500)

3 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=52 Card=26 Byt
es=2600)





統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
271 consistent gets
0 physical reads
0 redo size
1651 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed

G.現在看看對效能的影響。
SQL> set timing on
SQL> alter session set optimizer_index_cost_adj=100;

會話已更改。

已用時間: 00: 00: 00.00
SQL> ed
已寫入 file afiedt.buf

1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /

已選擇30行。

已用時間: 00: 00: 00.02

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=5 Bytes=1000
)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=2 Card
=1 Bytes=100)

2 1 NESTED LOOPS (Cost=13 Card=5 Bytes=1000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=3
Card=5 Bytes=500)

4 3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=
5)

5 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)




統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
1507 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed

SQL> alter session set optimizer_index_cost_adj=1;

會話已更改。

已用時間: 00: 00: 00.00
SQL> ed
已寫入 file afiedt.buf

1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /

已選擇30行。

已用時間: 00: 00: 00.02

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=1000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=1 Card
=1 Bytes=100)

2 1 NESTED LOOPS (Cost=2 Card=5 Bytes=1000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=1
Card=5 Bytes=500)

4 3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=
5)

5 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)




統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
1507 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed

SQL> alter session set optimizer_index_cost_adj=10000;

會話已更改。

已用時間: 00: 00: 00.00
SQL> ed
已寫入 file afiedt.buf

1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /

已選擇30行。

已用時間: 00: 00: 00.03

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=5 Bytes=1000
)

1 0 HASH JOIN (Cost=66 Card=5 Bytes=1000)
2 1 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 Byte
s=500)

3 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=52 Card=26 Byt
es=2600)





統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
271 consistent gets
0 physical reads
0 redo size
1651 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed

來詳細分析一下。
首先，由於測試環境的不同，Tom的測試結果是在預設值（100）的環境下，就已經和上面取值500時一樣了，即對T2全表掃描而T1使用索引。Tom試驗中，減小取值直至0，訪問路徑就變成使用兩個索引，而並不會出現均不使用索引的情況。另一方面，正如Tom的結論所說，OPTIMIZER_INDEX_COST_ADJ的取值越大，優化器越傾向於使用全表掃描，取值越小，優化器越傾向於使用索引。
再次，我們對比相同訪問路徑下的不同點。在取值從1變化到200（1-50-100-200）的過程中，優化器計算出的代價是持續增長的，而從1000到10000則是不變的。這說明這個引數與索引I/O的代價有關，而和全表掃描並無關係，這與Tom所說的並不矛盾，不過顯然更精確一點。
最後我們其實應該看到，雖然有如上所說的代價變化問題，同一訪問路徑下實際的執行效能並無區別，由於資料量比較小，上面的例子也許不能很好的說明這一點，不過想想Oracle用相同的路徑去執行，也沒有理由不同效能吧。
好，來看看官方文件吧。10G的官方文件（Reference）中對這個引數描述如下：
OPTIMIZER_INDEX_COST_ADJ
Parameter type Integer 引數型別為整數
Default value 100 預設值為100
Modifiable ALTER SESSION, ALTER SYSTEM 可通過ALTER SESSION, ALTER SYSTEM來修改
Range of values 1 to 10000 取值範圍是1-10000
OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, TO MAKE THE OPTIMIZER MORE OR LESS PRONE TO SELECTING AN INDEX ACCESS PATH OVER A FULL TABLE SCAN.
The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.
Note:
The adjustment does not apply to user-defined cost functions for domain indexes.
注意：調整對使用者為域索引自定義的代價函式無效。
描述的第一段正證實了引數的作用。第二段值得注意，正如Tom所說（我覺得如果他把原文versus前後顛倒一下會更好，即單塊I/O代價比之多塊I/O），參數列達了索引訪問代價對比
普通（表掃描）代價的比值。不過還有一個疑問我暫時還沒辦法想通，什麼條件下索引I/O居然比掃描慢100倍（取值10000）？

結論：
OPTIMIZER_INDEX_COST_ADJ通過指明索引I/O代價與掃描全表I/O代價的相對比值來影響CBO的行為，取值越小，CBO越傾向於使用索引，取值越大，越傾向於全表掃描。而預設值100，指明預設下，二者的代價是相等。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242323/，如需轉載，請註明出處，否則將追究法律責任。