oracle繫結變數窺視(zt)

tolywang發表於2007-12-21
Oracle變數
使用繫結變數一定是正確的嗎？
先來看一個實驗
SQL> drop table w_1;
Table dropped
SQL> create table w_1
2 as
3 select decode(mod(rownum,100),0,'N','Y') processed ,a.*
4 from all_objects a ;

Table created
SQL> create index idx_w_1 on w_1 (processed);
Index created
SQL> analyze table w_1 compute statistics
2 for table
3 for all indexes
4 for all indexed columns;
Table analyzed
SQL> set autotrace traceonly
Cannot SET AUTOTRACE
SQL> explain plan for select count(*) from w_1 tt where tt.processed='N' ;
Explained
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | |
|* 2 | INDEX RANGE SCAN | IDX_W_1 | 310 | 310 | 1 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TT"."PROCESSED"='N')
Note: cpu costing is off
15 rows selected
SQL> explain plan for select count(*) from w_1 tt where tt.processed='Y' ;
Explained
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 7 |
| 1 | SORT AGGREGATE | | 1 | 1 | |
|* 2 | INDEX FAST FULL SCAN| IDX_W_1 | 30748 | 30748 | 7 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TT"."PROCESSED"='Y')
Note: cpu costing is off
15 rows selected
從上面的執行計劃我們可以看出當processed='Y' ，走的是INDEX FAST FULL SCAN掃描，當processed='N' ， 走的是INDEX RANGE SCAN 索引。這是正確的，因為等於‘N’的值很少，走索引顯然很划算，‘Y’的值佔大部分，走INDEX FAST FULL SCAN或是全表掃描代價會更小一點。
好，我們來驗證一下“使用繫結變數”是否會選擇正確的執行計劃？
先執行processed :='Y'，再執行processed :='N'
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> /
variable processed varchar2(1);
exec :processed :='Y';
select count(*) from w_1 t1 where t1.processed=:processed ;
exec :processed :='N';
Session altered.
SQL> SQL>
PL/SQL procedure successfully completed.
SQL> select count(*) from w_1 t1 where t1.processed=:processed ;
alter session set events '10046 trace name context off';
/
COUNT(*)
----------
30748
SQL> SQL>
PL/SQL procedure successfully completed.
SQL>
COUNT(*)
----------
310
SQL> SQL>
Session altered.
執行後，我們來看看這兩條語句的執行計劃
BEGIN :processed :='Y'; END;

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 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 63
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
select count(*)
from
w_1 t1 where t1.processed=:processed

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.01 0.03 59 62 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.04 59 62 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 63
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
30748 INDEX FAST FULL SCAN IDX_W_1 (object id 34959)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 1 0.00 0.00
db file scattered read 8 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
BEGIN :processed :='N'; END;

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 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 63
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
select count(*)
from
w_1 t1 where t1.processed=:processed

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 62 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 62 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 63
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
310 INDEX FAST FULL SCAN IDX_W_1 (object id 34959)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
不論processed :='Y'還是='N',走的都是INDEX FAST FULL SCAN IDX_W_1， 顯然='N'走錯了執行計劃。當processed='N' ， 應該使用INDEX RANGE SCAN 索引。
為什麼會選擇錯誤的執行計劃呢？
先從sql語句內部是怎麼執行說起，oracle收到一條sql語句後，開始進行一系列的語法和語義檢查，然後去pga或是shared_pool裡面檢查這條語句是否被解析過，如果已經被解析過（稱為軟分析），那麼會跳過去選擇更最佳化的執行計劃的機會，直接選擇已有的執行計劃執行。 如果在共享池沒有找到這條sql解析的資訊（硬分析），oracle先去最佳化，選擇正確的執行計劃，然後才是執行。
顯然，使用繫結執行sql語句只執行一次硬解析，而後都是軟解析，這就導致了所有sql都在共用一個執行計劃。這就有可能選擇錯誤執行計劃的情況。
如果執行計劃有多個（processed :='Y' 'N'各有自己的執行計劃），繫結變數是怎麼選擇執行計劃呢？
最佳化程式在只有在硬解析的時候會進行窺視，軟解析的時候不會。也就是說最佳化程式不會每回都根據你傳遞的值去選擇正確的執行計劃 執行，只在做硬解析的時候，也就是第一次執行語句才會。
如：
exec :processed :='Y';
select count(*) from w_1 t1 where t1.processed=:processed ;（第一次執行）
因為是硬解析，那麼oracle在選擇執行計劃的時候會把=:processed 替換為='Y',然後選擇正確的執行計劃。。。
exec :processed :='N';
select count(*) from w_1 t1 where t1.processed=:processed ;
第二...次執行,因為pga或是shared_pool裡已經有這條語句的執行計劃，那麼oracle就不會再去窺視了，而是選擇已有的執行計劃，也就是='Y'的執行計劃。
結論：
繫結變數窺視只發生在硬解析，執行計劃是根據第一次傳遞進來的實際引數來確定的，這樣會存在繫結變數選擇錯誤執行計劃的可能。
引申一下：
如果我們先執行processed :='N'再執行processed :='Y'，會是什麼樣的情況呢？
variable processed varchar2(1);
exec :processed :='N';
select count(*) from w_1 t1 where t1.processed=:processed ;
exec :processed :='Y';
select count(*) from w_1 t1 where t1.processed=:processed ;

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

相關文章