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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 繫結變數窺視測試案例變數
- oracle 11g 新特性之動態繫結變數窺視(一)Oracle變數
- oracle 11g 新特性之動態繫結變數窺視(二)Oracle變數
- 【ORACLE】Oracle繫結變數知識梳理Oracle變數
- Oracle中如何查詢未使用繫結變數的SQL語句？Oracle變數SQL
- Oracle資料傾斜導致的問題-無繫結變數Oracle變數
- Oracle資料傾斜導致的問題-有繫結變數Oracle變數
- 在繫結變數下使用outline變數
- MySQL高階特性——繫結變數MySql變數
- [20180930]in list與繫結變數.txt變數
- [20180912]PLSLQ與繫結變數.txt變數
- Vue select 繫結動態變數Vue變數
- 窺探NSObjectObject
- 【CURSOR】Oracle繫結變數、執行計劃對遊標的影響Oracle變數
- [20180930]in list與繫結變數個數.txt變數
- [20210120]in list與繫結變數個數.txt變數
- Oracle 變數窺視引起執行計劃異常故障分析Oracle變數
- [20200326]繫結變數抓取與NULL值.txt變數Null
- 如何用FGA得到繫結變數的值變數
- 如何在對in操作使用變數繫結(轉)變數
- [20220414]toad與繫結變數peek.txt變數
- (譯)窺探Blocks(2)BloC
- Oracle面對“資料傾斜列使用繫結變數”場景的解決方案Oracle變數
- [20231210]執行計劃與繫結變數.txt變數
- [20190506]檢視巢狀與繫結變數.txt巢狀變數
- 窺探React – 原始碼分析React原始碼
- [20191213]共享池繫結變數的值在哪裡.txt變數
- [20221103]繫結變數的分配長度11.txt變數
- [20221030]繫結變數的分配長度10.txt變數
- Oracle 9i變數窺視引起執行計劃異常故障報告Oracle變數
- Vue render深入窺探之謎Vue
- Azure DevOps 的架構窺探dev架構
- 透過v$sql_bind_capture 檢視繫結變數。SQLAPT變數
- V$sql查詢未使用繫結變數的語句SQL變數
- Oracle-繫結執行計劃Oracle
- Oracle 替代變數Oracle變數
- [20191216]共享池繫結變數的值在哪裡2.txt變數
- [20211227]抽取跟蹤檔案中的繫結變數值.txt變數
- [20210112]完善查詢繫結變數指令碼bind_cap.txt變數指令碼