Oracle 10g(10.1.0.2)中的OPTIMIZER_INDEX_COST_ADJ Tom Kyte的新書(shū)Effective Oracle by Design的第6章 Getting the Most Out of the Cost-Based Optimizer中介紹了參數(shù)OPTIMIZER_INDEX_COST_ADJ，并認(rèn)為可以理解為Oracle執(zhí)行多塊（MultiBlock）I/O（比如全表掃描）的代價(jià)與執(zhí)行單塊（Single-block）I/O代價(jià)的相對(duì)比例。Tom指出，9i下該參數(shù)缺省值為100，意即二者擁有相同的代價(jià)，若減小，則CBO傾向于使用索引（即單塊I/O），反之則傾向于全表掃描（多塊I/O）。Tom同樣給出了一個(gè)不錯(cuò)的例子。 在自己的手提上試驗(yàn)了一下，硬盤(pán)大小原因，只裝了一個(gè)Oracle 10g(10.1.0.2)，誰(shuí)知就發(fā)現(xiàn)該參數(shù)在10g下取值發(fā)生了改變。我們先來(lái)看一下參照Tom的實(shí)驗(yàn)： A. 創(chuàng)建數(shù)據(jù)表。 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; 表已創(chuàng)建。 SQL> ed 已寫(xiě)入 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> / 表已創(chuàng)建。 B.創(chuàng)建索引并分析。 SQL> create index idx_t1 on t1(id); 索引已創(chuàng)建。 SQL> create index idx_t2 on t2(id); 索引已創(chuàng)建。 SQL> ed 已寫(xiě)入 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 過(guò)程已成功完成。 C.查詢?nèi)笔≈?，并設(shè)置好環(huán)境。 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.在缺省值下查詢的結(jié)果。 SQL> ed 已寫(xiě)入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 執(zhí)行計(jì)劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=5 Bytes=1000; );; 1;0TABLE 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); 統(tǒng)計(jì)信息 ----------------------------------------------------------;;; 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.修改參數(shù)值，注重，引用Tom的結(jié)論，9i中這個(gè)值在0-100之間，而10g呢？ SQL> alter session set optimizer_index_cost_adj=0; ERROR: ORA-00068: 值 0 對(duì)參數(shù) optimizer_index_cost_adj 無(wú)效, 必須在 1 和 10000 之間 SQL> alter session set optimizer_index_cost_adj=1; 會(huì)話已更改。 SQL> ed 已寫(xiě)入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 執(zhí)行計(jì)劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=1000); 1;0TABLE 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); 統(tǒng)計(jì)信息 ----------------------------------------------------------;;; 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; 會(huì)話已更改。 SQL> ed 已寫(xiě)入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 執(zhí)行計(jì)劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=5 Bytes=1000); 1;0TABLE 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); 統(tǒng)計(jì)信息 ----------------------------------------------------------;;; 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.繼續(xù)修改參數(shù)值，改大。 SQL> alter session set optimizer_index_cost_adj=200; 會(huì)話已更改。 SQL> ed 已寫(xiě)入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 執(zhí)行計(jì)劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=5 Bytes=1000; );; 1;0TABLE 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); 統(tǒng)計(jì)信息 ----------------------------------------------------------;;; 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; 會(huì)話已更改。 SQL> ed 已寫(xiě)入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 執(zhí)行計(jì)劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=63 Card=5 Bytes=1000; );; 1;0TABLE 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); 統(tǒng)計(jì)信息 ----------------------------------------------------------;;; 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; 會(huì)話已更改。 SQL> ed 已寫(xiě)入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 執(zhí)行計(jì)劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=5 Bytes=1000; );; 1;0HASH 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);;; 統(tǒng)計(jì)信息 ----------------------------------------------------------;;; 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; 會(huì)話已更改。 SQL> ed 已寫(xiě)入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 執(zhí)行計(jì)劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=5 Bytes=1000; );; 1;0HASH 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);;; 統(tǒng)計(jì)信息 ----------------------------------------------------------;;; 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.現(xiàn)在看看對(duì)性能的影響。 SQL> set timing on SQL> alter session set optimizer_index_cost_adj=100; 會(huì)話已更改。 已用時(shí)間:; 00: 00: 00.00 SQL> ed 已寫(xiě)入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 已用時(shí)間:; 00: 00: 00.02 執(zhí)行計(jì)劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=5 Bytes=1000; );; 1;0TABLE 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); 統(tǒng)計(jì)信息 ----------------------------------------------------------;;; 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; 會(huì)話已更改。 已用時(shí)間:; 00: 00: 00.00 SQL> ed 已寫(xiě)入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 已用時(shí)間:; 00: 00: 00.02 執(zhí)行計(jì)劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=1000); 1;0TABLE 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); 統(tǒng)計(jì)信息 ----------------------------------------------------------;;; 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; 會(huì)話已更改。 已用時(shí)間:; 00: 00: 00.00 SQL> ed 已寫(xiě)入 file afiedt.buf 1; select * from t1,t2 2; where t1.id=t2.id 3*and t2.id between 50 and 55 SQL> / 已選擇30行。 已用時(shí)間:; 00: 00: 00.03 執(zhí)行計(jì)劃 ----------------------------------------------------------;;; 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=5 Bytes=1000; );; 1;0HASH 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);;; 統(tǒng)計(jì)信息 ----------------------------------------------------------;;; 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;; 來(lái)具體分析一下。 首先，10g中OPTIMIZER_INDEX_COST_ADJ的取值范圍發(fā)生了改變。9i中為0-100，而10g中為1-10000。 其次，由于測(cè)試環(huán)境的不同，Tom的測(cè)試結(jié)果是在缺省值（100）的環(huán)境下，就已經(jīng)和上面取值500時(shí)一樣了，即對(duì)T2全表掃描而T1使用索引。Tom試驗(yàn)中，減小取值直至0，訪問(wèn)路徑就變成使用兩個(gè)索引，而并不會(huì)出現(xiàn)均不使用索引的情況。除去系統(tǒng)的不同（可能導(dǎo)致取缺省值時(shí)訪問(wèn)路徑是否一致），只看變化趨勢(shì)，顯然10g中靈活性更高，1-10000的取值使得CBO可以覆蓋所有的訪問(wèn)路徑。另一方面，正如Tom的結(jié)論所說(shuō)，OPTIMIZER_INDEX_COST_ADJ的取值越大，優(yōu)化器越傾向于使用全表掃描，取值越小，優(yōu)化器越傾向于使用索引。 再次，我們對(duì)比相同訪問(wèn)路徑下的不同點(diǎn)。在取值從1變化到200（1-50-100-200）的過(guò)程中，優(yōu)化器計(jì)算出的代價(jià)是持續(xù)增長(zhǎng)的，而從1000到10000則是不變的。這說(shuō)明這個(gè)參數(shù)與索引I/O的代價(jià)有關(guān)，而和全表掃描并無(wú)關(guān)系，這與Tom所說(shuō)的并不矛盾，不過(guò)顯然更精確一點(diǎn)。 最后我們其實(shí)應(yīng)該看到，雖然有如上所說(shuō)的代價(jià)變化問(wèn)題，同一訪問(wèn)路徑下實(shí)際的運(yùn)行性能并無(wú)區(qū)別，由于數(shù)據(jù)量比較小，上面的例子也許不能很好的說(shuō)明這一點(diǎn)，不過(guò)想想Oracle用相同的路徑去執(zhí)行，也沒(méi)有理由不同性能吧。 好，來(lái)看看官方文檔吧。10G的官方文檔（Reference）中對(duì)這個(gè)參數(shù)描述如下： OPTIMIZER_INDEX_COST_ADJ Parameter type Integer;參數(shù)類型為整數(shù) Default value 100 缺省值為100 Modifiable ALTER SESSION, ALTER SYSTEM; 可通過(guò)ALTER SESSION, ALTER SYSTEM來(lái)修改 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. 注重：調(diào)整對(duì)用戶為域索引自定義的代價(jià)函數(shù)無(wú)效。 描述的第一段正證實(shí)了參數(shù)的作用。第二段值得注重，正如Tom所說(shuō)（我覺(jué)得假如他把原文versus前后顛倒一下會(huì)更好，即單塊I/O代價(jià)比之多塊I/O），參數(shù)表達(dá)了索引訪問(wèn)代價(jià)對(duì)比普通（表掃描）代價(jià)的比值。不過(guò)還有一個(gè)疑問(wèn)我暫時(shí)還沒(méi)辦法想通，什么條件下索引I/O居然比掃描慢100倍（取值10000）？ 結(jié)論： OPTIMIZER_INDEX_COST_ADJ通過(guò)指明索引I/O代價(jià)與掃描全表I/O代價(jià)的相對(duì)比值來(lái)影響CBO的行為，取值越小，CBO越傾向于使用索引，取值越大，越傾向于全表掃描。而缺省值100，指明缺省下，二者的代價(jià)是相等。與9i不同的是，10g中OPTIMIZER_INDEX_COST_ADJ的取值范圍從0-100改為1-10000，相信是更合理的取值（至少按照文檔的說(shuō)明，作為代價(jià)的比值，取0是不合適的，除非說(shuō)索引I/O的代價(jià)相對(duì)于全表掃描I/O代價(jià)是無(wú)窮小的）。