Oracle 繫結變數窺探
Bind Peeking是Oracle 9i中引入的新特性，一直持續到Oracle 10g R2。它的作用就是在SQL語句硬分析的時候，檢視一下當前SQL謂詞的值
，以便生成最佳的執行計劃。而在oracle 9i之前的版本中，Oracle 只根據統計資訊來做出執行計劃。
，以便生成最佳的執行計劃。而在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; -->檢視當前資料庫版本
- BANNER
- ----------------------------------------------------------------
- Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
- SQL> create table t(id,owner,object_id) as -->建立測試表t
- 2 select rownum,owner,object_id from all_objects where rownum<=1000;
- SQL> alter table t add constraint t_pk primary key(id); -->為表t新增主鍵
- SQL> begin -->收集統計資訊,此處未生成直方圖資訊
- 2 dbms_stats.gather_table_stats(
- 3 ownname=>'SCOTT',
- 4 tabname=>'T',
- 5 estimate_percent=>100,
- 6 method_opt=>'for all columns size 1');
- 7 end;
- 8 /
- PL/SQL procedure successfully completed.
- SQL> select count(id),count(distinct id),min(id),max(id) from t; -->檢視值的分佈情況
- COUNT(ID) COUNT(DISTINCTID) MIN(ID) MAX(ID)
- ---------- ----------------- ---------- ----------
- 1000 1000 1 1000
2、未使用繫結變數情形下SQL語句的執行計劃
- SQL> select sum(object_id) from t where id<900; -->釋出SQL 查詢語句
- SUM(OBJECT_ID)
- --------------
- 446549
- SQL> select * from table(dbms_xplan.display_cursor()); -->由其執行計劃可知，當前的SQL語句使用了全表掃描
- /**************************************************/
- /* Author: Robinson Cheng */
- /* Blog: http://blog.csdn.net/robinson_0612 */
- /* MSN: robinson_0612@hotmail.com */
- /* QQ: 645746311 */
- /**************************************************/
- PLAN_TABLE_OUTPUT
- ---------------------------------------------------------------------------
- SQL_ID bz6h6fdsxgjka, child number 0
- -------------------------------------
- select sum(object_id) from t where id<900
- Plan hash value: 2966233522
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- |* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("ID"<900)
- SQL> select sum(object_id) from t where id<10; -->釋出另一條SQL 查詢語句
- SQL> select * from table(dbms_xplan.display_cursor()); -->此時的查詢生成的執行計劃走索引範圍掃描
- -->由於字面量不同，因此兩條SQL語句生成了不同的SQL_ID與執行計劃
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------
- SQL_ID 6y2280pyvacfq, child number 0
- -------------------------------------
- select sum(object_id) from t where id<10
- Plan hash value: 4270555908
- -------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 |
- |* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - access("ID"<10)
3、使用繫結變數情形下的執行計劃
- SQL> variable v_id number; -->定義繫結變數
- SQL> exec :v_id:=900; -->給繫結變數賦值
- PL/SQL procedure successfully completed.
- SQL> select sum(object_id) from t where id<:v_id>
- SUM(OBJECT_ID)
- --------------
- 446549
- SQL> select * from table(dbms_xplan.display_cursor()); -->此時上一條SQL語句走了全表掃描，其SQL_ID 為7qcp6urqh7d2j
- PLAN_TABLE_OUTPUT
- ------------------------------------------------------------------------------
- SQL_ID 7qcp6urqh7d2j, child number 0
- -------------------------------------
- select sum(object_id) from t where id<:v_id>
- Plan hash value: 2966233522
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- |* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("ID"<:v_id>-->謂詞資訊表明此時使用了繫結變數
- SQL> exec :v_id:=10; -->對繫結變數重新賦值
- PL/SQL procedure successfully completed.
- SQL> select sum(object_id) from t where id<:v_id>-->再次執行SQL語句
- SUM(OBJECT_ID)
- --------------
- 254
- SQL> select * from table(dbms_xplan.display_cursor()); -->此時執行計劃中依然選擇的是全表掃描
- -->其SQL_ID同上一次執行的SQL語句相同，即實現了完全共享
- PLAN_TABLE_OUTPUT -->對於未使用繫結變數時id<10的情形則為走索引範圍掃描
- ----------------------------------------------- -->由此可知，並非最佳的執行計劃被執行
- SQL_ID 7qcp6urqh7d2j, child number 0
- -------------------------------------
- select sum(object_id) from t where id<:v_id>
- Plan hash value: 2966233522
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- |* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |
- ---------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("ID"<:v_id>
- SQL> alter system flush shared_pool; -->清空共享池，此時共享的父遊標與子游標全部釋放
- SQL> print v_id;
- V_ID
- ----------
- 10
- SQL> select round(avg(object_id)) from t where id<:v_id>-->使用id<10來執行SQL語句
- ROUND(AVG(OBJECT_ID))
- ---------------------
- 28
- SQL> select * from table(dbms_xplan.display_cursor()); -->此時該SQL語句使用了最佳的執行計劃，即走索引範圍掃描
- PLAN_TABLE_OUTPUT
- ---------------------------------------------------------------------------------------
- SQL_ID 0bx53mgt4qqnt, child number 0
- -------------------------------------
- select round(avg(object_id)) from t where id<:v_id>
- Plan hash value: 4270555908
- -------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 |
- |* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - access("ID"<:v_id>
- SQL> exec :v_id:=900; -->為變數賦新值
- PL/SQL procedure successfully completed.
- SQL> select round(avg(object_id)) from t where id<:v_id>
- ROUND(AVG(OBJECT_ID))
- ---------------------
- 497
- SQL> select * from table(dbms_xplan.display_cursor()); -->此次執行的SQL語句本該使用全表掃描，而此時選擇了索引範圍掃描
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------------
- SQL_ID 0bx53mgt4qqnt, child number 0
- -------------------------------------
- select round(avg(object_id)) from t where id<:v_id>
- Plan hash value: 4270555908
- -------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 3 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 8 | | |
- | 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 |
- |* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - access("ID"<:v_id>
- SQL> drop table t;
三、總結
從上面的演示可以，由於繫結變數窺探特性，對於後續生成的執行計劃，不僅套用了首次生成的執行計劃，而且執行計劃中的Row,Bytes,
Cost(%CPU)等都與首次生存執行計劃得值相同。由此可知，儘管可以使用繫結變數解決OLTP系統中大量重複SQL的反覆解析的問題。但繫結變數
可能會導致SQL語句選擇非最佳的執行計劃。尤其是對於存在資料傾斜的列，且生成了直方圖更不宜於使用繫結變數。在Oracle 11g 中，自適
應特性從一定程度解決了繫結變數窺探所導致的問題。
四、延伸參考
Oracle自適應共享遊標
繫結變數及其優缺點
父遊標、子游標及共享遊標
dbms_xplan之display_cursor函式的使用
dbms_xplan之display函式的使用
執行計劃中各欄位各模組描述
-->>轉載於:http://blog.csdn.net/leshami/article/details/6923627
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29119536/viewspace-1243957/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- 10g繫結變數窺探變數
- 繫結變數優缺點、使用、繫結變數窺探、 Oracle自適應共享遊標變數Oracle
- oracle繫結變數窺視(zt)Oracle變數
- Oracle 變數繫結與變數窺視合集Oracle變數
- 繫結變數窺測變數
- 【原創】Oracle 變數繫結與變數窺視合集Oracle變數
- oracle bind value peeking繫結變數窺視Oracle變數
- 繫結變數窺測的演變變數
- 繫結變數窺視測試案例變數
- 【SQL 調優】繫結變數窺測SQL變數
- Oracle 繫結變數Oracle變數
- 使用繫結變數窺探後的cardinality和selectivity的計算方法變數
- Oracle之繫結變數Oracle變數
- oracle 11g 新特性之動態繫結變數窺視(二)Oracle變數
- oracle 11g 新特性之動態繫結變數窺視(一)Oracle變數
- ORACLE 繫結變數用法總結Oracle變數
- oracle 繫結變數（bind variable）Oracle變數
- Oracle 繫結變數 詳解Oracle變數
- 【ORACLE】Oracle繫結變數知識梳理Oracle變數
- ORACLE 獲取繫結變數值Oracle變數
- oracle繫結變數的測試Oracle變數
- 繫結變數變數
- ORACLE優化實戰（繫結變數）Oracle優化變數
- Oracle繫結變數分級（Bind Graduation）Oracle變數
- [SQL]oracle 繫結變數(bind variable)[轉載]SQLOracle變數
- 檢視繫結變數變數
- PLSQL使用繫結變數SQL變數
- 關於繫結變數變數
- Oracle獲取繫結變數的各種方法Oracle變數
- oracle 查詢未使用繫結變數的sqlOracle變數SQL
- 【優化】使用繫結變數 OR 不使用繫結變數，這不是問題！優化變數
- 【最佳化】使用繫結變數 OR 不使用繫結變數，這不是問題！變數
- 繫結變數引數關閉之後，oracle會如何操作變數Oracle
- 在oracle的plsql中為cursor使用繫結變數OracleSQL變數
- oracle中使用繫結變數的好處的例子Oracle變數
- 繫結變數的測試變數
- 如何獲取繫結變數變數