| 當(dāng)我在做培訓(xùn)時(shí)，在解釋綁定變量的好處時(shí)，大家都比較容易理解。但是，對(duì)于并不是任何時(shí)候綁定變量都是最優(yōu)的。這一點(diǎn)很多人不是和理解。下面就討論一下在什么時(shí)候會(huì)出現(xiàn)綁定變量會(huì)使性能變差。 掃描成本和OPTIMIZER_INDEX_COST_ADJ 我們知道，在CBO模式下，Oracle會(huì)計(jì)算各個(gè)訪問(wèn)路徑的代價(jià)，采用最小代價(jià)的訪問(wèn)路徑作為語(yǔ)句的執(zhí)行計(jì)劃。而對(duì)于索引的訪問(wèn)代價(jià)的計(jì)算，需要根據(jù)一個(gè)系統(tǒng)參數(shù)OPTIMIZER_INDEX_COST_ADJ來(lái)轉(zhuǎn)換為與全表掃描代價(jià)等價(jià)的一個(gè)值。這是什么意思呢？我們先稍微解釋一下這個(gè)參數(shù)：OPTIMIZER_INDEX_COST_ADJ。它的值是一個(gè)百分比，默認(rèn)是100，取值范圍是1～10000。當(dāng)估算索引掃描代價(jià)時(shí)，會(huì)將索引的原始代價(jià)值乘以這個(gè)百分比，將換算后的值作為與全表掃描代價(jià)比較的值。也就是說(shuō)，當(dāng)這個(gè)值為100時(shí)，計(jì)算出的索引掃描代價(jià)就是它的原始代價(jià)： COST_COM = COST_ORG * OPTIMIZER_INDEX_COST_ADJ/100 看以下例子： SQL> create table T_PEEKING (a NUMBER, b char(1), c char(2000));
Table created.
SQL>
SQL> create index T_PEEKING_IDX1 on T_PEEKING(b);
Index created.
SQL> begin
2 for i in 1..1000 loop
3 insert into T_PEEKING values (i, ‘A‘, i);
4 end loop;
5
6 insert into T_PEEKING values (1001, ‘B‘, 1001);
7 insert into T_PEEKING values (1002, ‘B‘, 1002);
8 insert into T_PEEKING values (1003, ‘C‘, 1003);
9
10 commit;
11 end;
12 /
PL/SQL procedure successfully completed.
注意，我們給索引字段B插入的值中只有3個(gè)distinct值，記錄數(shù)是1003，它的集的勢(shì)很高（1003/3）=334。關(guān)于集的勢(shì)的計(jì)算，可以參考我的另外一篇文檔《關(guān)于集的勢(shì)的計(jì)算》。 SQL>
SQL> analyze table T_PEEKING compute statistics for table for all indexes for all indexed columns;
Table analyzed.
SQL>
我們看下索引掃描的代價(jià)是多少： SQL> show parameter OPTIMIZER_INDEX_COST_ADJ
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
SQL> delete from plan_table;
0 rows deleted.
SQL>
SQL> explain plan for select /*+index(a T_PEEKING_IDX1)*/ * from T_PEEKING a where b = :V;
Explained.
SQL> select lpad(‘ ‘, 2*(level-1))||operation||‘ ‘||options||‘ ‘||
2 object_name||‘ ‘||decode(id, 0, ‘Cost=‘||position) "Query
3 Plan_Table"
4 from plan_table
5 start with id = 0
6 connect by prior id = parent_id
7 ;
Query
Plan_Table
--------------------------------------------------------------------------------
SELECT STATEMENT Cost=113
TABLE ACCESS BY INDEX ROWID T_PEEKING
INDEX RANGE SCAN T_PEEKING_IDX1
SQL>
再看全表掃描的代價(jià)是多少： SQL> delete from plan_table;
3 rows deleted.
SQL>
SQL> explain plan for select /*+full(a)*/ * from T_PEEKING a where b = :V;
Explained.
SQL>
SQL> select lpad(‘ ‘, 2*(level-1))||operation||‘ ‘||options||‘ ‘||
2 object_name||‘ ‘||decode(id, 0, ‘Cost=‘||position) "Query
3 Plan_Table"
4 from plan_table
5 start with id = 0
6 connect by prior id = parent_id
7 ;
Query
Plan_Table
--------------------------------------------------------------------------------
SELECT STATEMENT Cost=75
TABLE ACCESS FULL T_PEEKING
SQL>
這時(shí)，我們可以計(jì)算得出讓優(yōu)化器使用索引（無(wú)提示強(qiáng)制）的OPTIMIZER_INDEX_COST_ADJ值應(yīng)該 < ROUND(COST_FTS/COST_IDX*100) = ROUND(75/113*100) = 66，而大于66則會(huì)使用全表掃描： SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=67;
System altered.
SQL>
SQL> delete from plan_table;
2 rows deleted.
SQL>
SQL> explain plan for select * from T_PEEKING a where b = :V;
Explained.
SQL>
SQL> select lpad(‘ ‘, 2*(level-1))||operation||‘ ‘||options||‘ ‘||
2 object_name||‘ ‘||decode(id, 0, ‘Cost=‘||position) "Query
3 Plan_Table"
4 from plan_table
5 start with id = 0
6 connect by prior id = parent_id;
Query
Plan_Table
--------------------------------------------------------------------------------
SELECT STATEMENT Cost=75
TABLE ACCESS FULL T_PEEKING
SQL>
SQL>
SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=66;
System altered.
SQL>
SQL> delete from plan_table;
2 rows deleted.
SQL>
SQL> explain plan for select * from T_PEEKING a where b = :V;
Explained.
SQL>
SQL> select lpad(‘ ‘, 2*(level-1))||operation||‘ ‘||options||‘ ‘||
2 object_name||‘ ‘||decode(id, 0, ‘Cost=‘||position) "Query
3 Plan_Table"
4 from plan_table
5 start with id = 0
6 connect by prior id = parent_id;
Query
Plan_Table
--------------------------------------------------------------------------------
SELECT STATEMENT Cost=75
TABLE ACCESS BY INDEX ROWID T_PEEKING
INDEX RANGE SCAN T_PEEKING_IDX1
可以看出，在使用綁定變量時(shí)，參數(shù)OPTIMIZER_INDEX_COST_ADJ對(duì)于是否選擇索引會(huì)有重要的影響。 這里我們暫且不討論索引掃描的原始成本是如何計(jì)算得出的。但是有一點(diǎn)很重要，在使用綁定變量時(shí)，計(jì)算出的成本是平均成本。在我們上面的例子中，字段B的值只有3個(gè)："A"、"B"、"C"，其中A最多，1003行中有1000行。因此，在索引上掃描值為A記錄的成本為1000/1003 * 索引全掃描成本 ≈索引全掃描成本，我們看下它的成本是多少： SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=100;
System altered.
SQL>
SQL> delete from plan_table;
2 rows deleted.
SQL>
SQL> explain plan for select /*+index(a T_PEEKING_IDX1)*/* from T_PEEKING a where b = ‘A‘;
Explained.
SQL>
SQL> select lpad(‘ ‘, 2*(level-1))||operation||‘ ‘||options||‘ ‘||
2 object_name||‘ ‘||decode(id, 0, ‘Cost=‘||position) "Query
3 Plan_Table"
4 from plan_table
5 start with id = 0
6 connect by prior id = parent_id;
Query
Plan_Table
--------------------------------------------------------------------------------
SELECT STATEMENT Cost=336
TABLE ACCESS BY INDEX ROWID T_PEEKING
INDEX RANGE SCAN T_PEEKING_IDX1
可以看到，它的成本是336。因此索引的平均成本是(336 * 1003/1000) / 3 ≈ 113，也就是使用綁定變量使的成本。而掃描其它兩個(gè)值"B"和"A"時(shí)代價(jià)就非常小。 SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=100;
System altered.
SQL>
SQL> delete from plan_table;
3 rows deleted.
SQL>
SQL> explain plan for select /*+index(a T_PEEKING_IDX1)*/* from T_PEEKING a where b = ‘B‘;
Explained.
SQL>
SQL> select lpad(‘ ‘, 2*(level-1))||operation||‘ ‘||options||‘ ‘||
2 object_name||‘ ‘||decode(id, 0, ‘Cost=‘||position) "Query
3 Plan_Table"
4 from plan_table
5 start with id = 0
6 connect by prior id = parent_id;
Query
Plan_Table
--------------------------------------------------------------------------------
SELECT STATEMENT Cost=2
TABLE ACCESS BY INDEX ROWID T_PEEKING
INDEX RANGE SCAN T_PEEKING_IDX1
因?yàn)橛?jì)算的成本是平均成本（相對(duì)實(shí)際掃描某個(gè)值的成本，平均成本更接近全表掃描成本），因此在創(chuàng)建查詢(xún)計(jì)劃時(shí)，使用綁定變量將更加容易受到參數(shù)OPTIMIZER_INDEX_COST_ADJ影響，特別是上面的這種情況（即索引字段的集的勢(shì)非常高時(shí)）下，平均代價(jià)與實(shí)際掃描某個(gè)值代價(jià)相差非常遠(yuǎn)。這種情況下，OPTIMIZER_INDEX_COST_ADJ對(duì)不使用綁定變量查詢(xún)影響就非常?。ㄒ?yàn)樗饕鷥r(jià)不是比全表掃描成本大很多就是小很多），不管掃描哪個(gè)值，不使用綁定變量將更加容易選擇到合理的查詢(xún)計(jì)劃。 綁定變量窺視 在了解了參數(shù)OPTIMIZER_INDEX_COST_ADJ的作用后。再了解一個(gè)對(duì)查詢(xún)計(jì)劃，特別是使用綁定變量時(shí)會(huì)產(chǎn)生重大影響的特性：綁定變量窺視（Bind Variables Peeking）。 綁定變量窺視是9i以后的一個(gè)新特性。它使CBO優(yōu)化器在計(jì)算訪問(wèn)代價(jià)時(shí)，將綁定變量傳入的值考慮進(jìn)去，從而計(jì)算出更合理的成本（否則，將會(huì)計(jì)算平均成本）。看下面例子： SQL> conn sys/sys as sysdba
Connected.
SQL>
SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=60;
System altered.
SQL> analyze table T_PEEKING compute statistics for table for all indexes for all indexed columns;
Table analyzed.
SQL>
SQL> set autot trace
SQL>
SQL> alter session set sql_trace = true;
Session altered.
SQL>
SQL> var v char(1)
SQL>
SQL> exec :v := ‘A‘;
PL/SQL procedure successfully completed.
SQL>
SQL> select * from T_PEEKING a where b = :V;
1000 rows selected.
SQL>
SQL> alter session set sql_trace = false;
Session altered.
用TKPROF處理生成的trace文件。因?yàn)樵诖嬖诮壎ㄗ兞扛Q視時(shí)，autotrace或者explain plan可能不會(huì)顯示正確的查詢(xún)計(jì)劃，需要tkprof來(lái)處理sql trace。 tkprof fuyuncat_ora_5352.trc aaa.txt
此時(shí)OPTIMIZER_INDEX_COST_ADJ是60，根據(jù)上面的結(jié)論，似乎查詢(xún)計(jì)劃應(yīng)該選擇掃描索引。但是，這里給綁定變量賦了值"A"，這時(shí)，優(yōu)化器會(huì)“窺視”到這個(gè)值，并且在計(jì)算掃描成本時(shí)按照這個(gè)值的成本來(lái)計(jì)算。因此，得出的查詢(xún)計(jì)劃是全表掃描，而不是掃描索引，靠tkprof分析的結(jié)果： select *
from
T_PEEKING a where b = :V
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.01 0.07 0 406 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.08 0 406 0 1000
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS FULL T_PEEKING (cr=406 pr=0 pw=0 time=5052 us)
*************************************************************************
但是，綁定變量窺視對(duì)一條語(yǔ)句只會(huì)使用一次。就是說(shuō)，在第一次解析語(yǔ)句時(shí)，將綁定變量值考慮進(jìn)去計(jì)算成本生成查詢(xún)計(jì)劃。以后在執(zhí)行該語(yǔ)句時(shí)都采用這個(gè)查詢(xún)計(jì)劃，而不再考慮以后綁定變量的值是什么了。 SQL> conn sys/sys as sysdba
Connected.
SQL>
SQL>
SQL> set autot trace
SQL>
SQL> alter session set sql_trace = true;
Session altered.
SQL>
SQL> var v char(1)
SQL>
SQL> exec :v := ‘B‘;
PL/SQL procedure successfully completed.
SQL>
SQL> select * from T_PEEKING a where b = :V;
1000 rows selected.
SQL>
SQL> alter session set sql_trace = false;
Session altered.
再用tkprof分析生成的trace文件，看到盡管這里的值是"B"，選擇索引掃描會(huì)更優(yōu)，但分析結(jié)果中查詢(xún)計(jì)劃還是使用全表掃描： select *
from
T_PEEKING a where b = :V
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 340 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 340 0 2
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS FULL T_PEEKING (cr=340 pr=0 pw=0 time=1005 us)
因此，這種情況下使用綁定變量也會(huì)導(dǎo)致無(wú)法選擇最優(yōu)的查詢(xún)計(jì)劃。 綜上，我們可以得出一個(gè)結(jié)論：在對(duì)建有索引的字段（包括字段集），且字段（集）的集的勢(shì)非常大時(shí)，使用綁定變量可能會(huì)導(dǎo)致查詢(xún)計(jì)劃錯(cuò)誤，因而會(huì)使查詢(xún)效率非常低。 | 
