Oracle 綁定變數窺探

     Bind Peeking是Oracle 9i中引入的新特性，一直持續到Oracle 10g R2。它的作用就是在SQL語句硬分析的時候，查看一下當前SQL謂詞的值
，以便產生最佳的執行計畫。而在oracle 9i之前的版本中，Oracle 只根據統計資訊來做出執行計畫。

一、綁定變數窺探
    使用SQL首次運行時的值來產生執行計畫。後續再次運行該SQL語句則使用首次執行計畫來執行。
    影響的版本：Oracle 9i, Oracle 10g
    對於綁定變數列中的特殊值或非均勻分布列上的綁定變數會造成非高效的執行計畫被選擇並執行。
   
        要注意的是，Bind Peeking只發生在硬分析的時候，即SQL被第一次執行的時候，之後的變數將不會在做peeking。我們可以看出，Bind
    peeking並不能最終解決不同謂詞導致選擇不同執行計畫的問題，它只能讓SQL第一次執行的時候，執行計畫選擇更加準確，並不能協助OLAP
    系統解決綁定變數導致執行計畫選擇錯誤的問題。這也是OLAP不應該使用綁定變數的一個原因。

        更確切地說，綁定變數窺探是在SQL解析的物理階段，查詢最佳化工具將會窺探綁定變數的值並將其作為字面量來使用。即ORACLE首次解析
    SQL時會將變數的真實值代入產生執行計畫，後續對所有使用該綁定變數SQL語句都採用首次生存的執行計畫。如此這般？那效能究竟如何？
    結果是並非最佳的執行計畫的使用。此問題在Oracle 11g中得以解決。  
        請參考：Oracle自適應共用遊標

二、樣本綁定變數窺探
    1、建立示範環境       

SQL> select * from v$version where rownum<2; -->查看當前資料庫版本 </p><p>BANNER<br />----------------------------------------------------------------<br />Oracle Database 10g Release 10.2.0.3.0 - 64bit Production </p><p>SQL> create table t(id,owner,object_id) as -->建立測試表t<br /> 2 select rownum,owner,object_id from all_objects where rownum<=1000; </p><p>SQL> alter table t add constraint t_pk primary key(id); -->為表t添加主鍵 </p><p>SQL> begin -->收集統計資訊,此處未產生長條圖資訊<br /> 2 dbms_stats.gather_table_stats(<br /> 3 ownname=>'SCOTT',<br /> 4 tabname=>'T',<br /> 5 estimate_percent=>100,<br /> 6 method_opt=>'for all columns size 1');<br /> 7 end;<br /> 8 / </p><p>PL/SQL procedure successfully completed. </p><p>SQL> select count(id),count(distinct id),min(id),max(id) from t; -->查看值的分布情況 </p><p> COUNT(ID) COUNT(DISTINCTID) MIN(ID) MAX(ID)<br />---------- ----------------- ---------- ----------<br /> 1000 1000 1 1000<br />

    2、未使用綁定變數情形下SQL語句的執行計畫       

SQL> select sum(object_id) from t where id<900; -->發布SQL 查詢語句 </p><p>SUM(OBJECT_ID)<br />--------------<br /> 446549 </p><p>SQL> select * from table(dbms_xplan.display_cursor()); -->由其執行計畫可知，當前的SQL語句使用了全表掃描<br />/**************************************************/<br />/* Author: Robinson Cheng */<br />/* Blog: http://blog.csdn.net/robinson_0612 */<br />/* MSN: robinson_0612@hotmail.com */<br />/* QQ: 645746311 */<br />/**************************************************/ </p><p>PLAN_TABLE_OUTPUT<br />---------------------------------------------------------------------------<br />SQL_ID bz6h6fdsxgjka, child number 0<br />-------------------------------------<br />select sum(object_id) from t where id<900 </p><p>Plan hash value: 2966233522 </p><p>---------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />---------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 3 (100)| |<br />| 1 | SORT AGGREGATE | | 1 | 8 | | |<br />|* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |<br />--------------------------------------------------------------------------- </p><p>Predicate Information (identified by operation id):<br />--------------------------------------------------- </p><p> 2 - filter("ID"<900) </p><p>SQL> select sum(object_id) from t where id<10; -->發布另一條SQL 查詢語句 </p><p>SQL> select * from table(dbms_xplan.display_cursor()); -->此時的查詢產生的執行計畫走索引範圍掃描<br /> -->由於字面量不同，因此兩條SQL語句產生了不同的SQL_ID與執行計畫<br />PLAN_TABLE_OUTPUT<br />--------------------------------------------------------------------------<br />SQL_ID 6y2280pyvacfq, child number 0<br />-------------------------------------<br />select sum(object_id) from t where id<10 </p><p>Plan hash value: 4270555908 </p><p>-------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />-------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 3 (100)| |<br />| 1 | SORT AGGREGATE | | 1 | 8 | | |<br />| 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 |<br />|* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 |<br />------------------------------------------------------------------------------------- </p><p>Predicate Information (identified by operation id):<br />--------------------------------------------------- </p><p> 3 - access("ID"<10)<br />

    3、使用綁定變數情形下的執行計畫             

SQL> variable v_id number; -->定義綁定變數<br />SQL> exec :v_id:=900; -->給綁定變數賦值 </p><p>PL/SQL procedure successfully completed. </p><p>SQL> select sum(object_id) from t where id<:v_id; </p><p>SUM(OBJECT_ID)<br />--------------<br /> 446549 </p><p>SQL> select * from table(dbms_xplan.display_cursor()); -->此時上一條SQL語句走了全表掃描，其SQL_ID 為7qcp6urqh7d2j </p><p>PLAN_TABLE_OUTPUT<br />------------------------------------------------------------------------------<br />SQL_ID 7qcp6urqh7d2j, child number 0<br />-------------------------------------<br />select sum(object_id) from t where id<:v_id </p><p>Plan hash value: 2966233522 </p><p>---------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />---------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 3 (100)| |<br />| 1 | SORT AGGREGATE | | 1 | 8 | | |<br />|* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |<br />--------------------------------------------------------------------------- </p><p>Predicate Information (identified by operation id):<br />--------------------------------------------------- </p><p> 2 - filter("ID"<:V_ID) -->謂詞資訊表明此時使用了綁定變數 </p><p>SQL> exec :v_id:=10; -->對綁定變數重新賦值 </p><p>PL/SQL procedure successfully completed. </p><p>SQL> select sum(object_id) from t where id<:v_id; -->再次執行SQL語句 </p><p>SUM(OBJECT_ID)<br />--------------<br /> 254 </p><p>SQL> select * from table(dbms_xplan.display_cursor()); -->此時執行計畫中依然選擇的是全表掃描<br /> -->其SQL_ID同上一次執行的SQL語句相同，即實現了完全共用<br />PLAN_TABLE_OUTPUT -->對於未使用綁定變數時id<10的情形則為走索引範圍掃描<br />----------------------------------------------- -->由此可知，並非最佳的執行計畫被執行<br />SQL_ID 7qcp6urqh7d2j, child number 0<br />-------------------------------------<br />select sum(object_id) from t where id<:v_id </p><p>Plan hash value: 2966233522 </p><p>---------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />---------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 3 (100)| |<br />| 1 | SORT AGGREGATE | | 1 | 8 | | |<br />|* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |<br />--------------------------------------------------------------------------- </p><p>Predicate Information (identified by operation id):<br />--------------------------------------------------- </p><p> 2 - filter("ID"<:V_ID) </p><p>SQL> alter system flush shared_pool; -->清空共用池，此時共用的父遊標與子遊標全部釋放 </p><p>SQL> print v_id; </p><p> V_ID<br />----------<br /> 10 </p><p>SQL> select round(avg(object_id)) from t where id<:v_id; -->使用id<10來執行SQL語句 </p><p>ROUND(AVG(OBJECT_ID))<br />---------------------<br /> 28 </p><p>SQL> select * from table(dbms_xplan.display_cursor()); -->此時該SQL語句使用了最佳的執行計畫，即走索引範圍掃描 </p><p>PLAN_TABLE_OUTPUT<br />---------------------------------------------------------------------------------------<br />SQL_ID 0bx53mgt4qqnt, child number 0<br />-------------------------------------<br />select round(avg(object_id)) from t where id<:v_id </p><p>Plan hash value: 4270555908 </p><p>-------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />-------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 3 (100)| |<br />| 1 | SORT AGGREGATE | | 1 | 8 | | |<br />| 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 |<br />|* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 |<br />------------------------------------------------------------------------------------- </p><p>Predicate Information (identified by operation id):<br />--------------------------------------------------- </p><p> 3 - access("ID"<:V_ID) </p><p>SQL> exec :v_id:=900; -->為變數賦新值 </p><p>PL/SQL procedure successfully completed. </p><p>SQL> select round(avg(object_id)) from t where id<:v_id; </p><p>ROUND(AVG(OBJECT_ID))<br />---------------------<br /> 497 </p><p>SQL> select * from table(dbms_xplan.display_cursor()); -->此次啟動並執行SQL語句本該使用全表掃描，而此時選擇了索引範圍掃描</p><p>PLAN_TABLE_OUTPUT<br />--------------------------------------------------------------------------------------<br />SQL_ID 0bx53mgt4qqnt, child number 0<br />-------------------------------------<br />select round(avg(object_id)) from t where id<:v_id </p><p>Plan hash value: 4270555908 </p><p>-------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />-------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 3 (100)| |<br />| 1 | SORT AGGREGATE | | 1 | 8 | | |<br />| 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 |<br />|* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 |<br />------------------------------------------------------------------------------------- </p><p>Predicate Information (identified by operation id):<br />--------------------------------------------------- </p><p> 3 - access("ID"<:V_ID) </p><p>SQL> drop table t;<br />

三、總結
    從上面的示範可以，由於綁定變數窺探特性，對於後續產生的執行計畫，不僅套用了首次產生的執行計畫，而且執行計畫中的Row,Bytes,
Cost(%CPU)等都與首次生存執行計畫得值相同。由此可知，儘管可以使用綁定變數解決OLTP系統中大量重複SQL的反覆解析的問題。但綁定變數
可能會導致SQL語句選擇非最佳的執行計畫。尤其是對於存在資料扭曲的列，且產生了長條圖更不宜於使用綁定變數。在Oracle 11g 中，自適
應特性從一定程度解決了綁定變數窺探所導致的問題。

四、延伸參考
    Oracle自適應共用遊標
    綁定變數及其優缺點 
   
父遊標、子遊標及共用遊標 
   
dbms_xplan之display_cursor函數的使用 
   
dbms_xplan之display函數的使用 
   
執行計畫中各欄位各模組描述