繫結變數和BIND PEEKING
繫結變數和BIND PEEKING
使用繫結變數可以減少SQL PARSE，但是使用繫結變數有一個不好的地方，就是對於訪問具有傾斜的列，可能使用錯誤的執行計劃。
在Oracle 9i之前，如果WHERE 條件裡面全部使用繫結變數，那麼只能使用固定的選擇性引數來確定執行計劃。
=操作和>=操作的選擇性為5%，範圍掃描的選擇性為25%。預設值的方式可能生成不好的執行計劃。所以Oracle 9i就出現了一個新的技術，
bind peeking。什麼是bind peeking呢？當SQL第一次執行的時候，最佳化器會根據繫結變數來確定執行計劃（如果存在柱狀圖）。
BIND PEEKING只有當該SQL第一次執行的時候，進行HARD PARSE的時候才進行，第二次呼叫該SQL，就不會再次進行BIND PEEKING。
這種情況下，就存在另外一個風險，如果某個列的傾斜性很厲害，那麼使用BIND PEEKING就是不安全的，因為不同的引數代入，
只能走第一次執行時的執行計劃，那麼執行計劃就像擲色子一樣，要靠運氣了。碰到這種情況，應用就不應該使用繫結變數，而應該改為直接值了。
下面是一個實驗：
1、建立一個表create table tt2 as select * from dba_objects; 也可以再執行幾次，把記錄數搞大一些
2、update tt2 set status='INVALID' where rownum<100;
3、update tt2 set status='IND' where status='VALID' and rownum<3;
4、經過上述操作，就會產生幾萬valid，89條 invalid和2條ind的記錄。下面進行分析
5、create index ind_tt2_status on tt2 (status);
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','TT2',method_opt => 'for all indexed columns size skewonly');
PL/SQL 過程已成功完成。
SQL> select table_name,COLUMN_NAME,endpoint_number,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE from user_tab_histograms where table_name='T
T2' ORDER BY COLUMN_nAME;
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
------------------------------ ------------------------------ --------------- -------------- --------------------
TT2 STATUS 21 3.8063E+35
TT2 STATUS 29819 4.4786E+35
TT2 STATUS 2 3.8063E+35
6、執行：
var a varchar2(10);
exec :a:='VALID';
select object_id,status from tt2 where status=:a;
7、檢視執行計劃（這個時候最好不要用AUTOTRACE，由於BUG，AUTOTRACE顯示的執行計劃是錯誤的），可以透過v$sql_plan，或者乾脆用sql_trace
檢視sql_trace的時候，如果看裸檔案，那麼parse #1 。。。mis=1說明是硬解析，否則就是軟解析
我們使用v$_sql_plan來看：
Plan Table
--------------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| TABLE ACCESS FULL |TT2 | 0 | 123K| 123K| 0 | 0 | 0 | 0 |
| SELECT STATEMENT | | 0 | | 123K| 0 | 0 | 0 | 0 |
------------------------------------------------------------------------------------------------------------------------
8、然後在執行：
exec :a:='INVALID';
select object_id,status from tt2 where status=:a;
檢視執行計劃和上面的相同
Plan Table
--------------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| TABLE ACCESS FULL |TT2 | 0 | 123K| 123K| 0 | 0 | 0 | 0 |
| SELECT STATEMENT | | 0 | | 123K| 0 | 0 | 0 | 0 |
------------------------------------------------------------------------------------------------------------------------
如果生成了TRACE檔案，可以看到：
SELECT OBJECT_NAME,STATUS
FROM
TT2 WHERE STATUS=:A
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.01 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 8213 0.56 0.59 0 18352 0 123154
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8217 0.56 0.60 0 18352 0 123154
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
4 TABLE ACCESS FULL TT2
從這裡可以看出執行了2次parse，其中misses in library cache during parse:1,說明其中一次是硬分析（從裸檔案也可以看出mis=1的有一次）
9、重新整理一下共享池alter system flush shared_pool;
10、然後再執行
exec :a:='INVALID';
select object_id,status from tt2 where status=:a;
看到的執行計劃如下：
Plan Table
-------------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| INDEX RANGE SCAN |IDX_TT2_STATUS | 0 | 178 | 123K| 0 | 0 | 0 | 0 |
| TABLE ACCESS BY INDEX ROWID |TT2 | 0 | 178 | 123K| 0 | 0 | 0 | 0 |
| SELECT STATEMENT | | 0 | | 123K| 0 | 0 | 0 | 0 |
------------------------------------------------------------------------------------------------------------------------
11、再執行：
exec :a:='VALID';
select object_id,status from tt2 where status=:a;
看到的執行計劃還是和上面的相同，走索引範圍掃描。
12、這就是bind peeking的含義，使用繫結變數的時候，能夠共享SQL，其執行計劃是在第一次parse的時候做peeking生成的。根據當前的繫結變數的值。
今後再次執行，就認為SQL是安全的，不再進行peeking，也不會生成新的執行計劃
13、如果刪除柱狀圖，那麼還是否會產生PEEKING呢？
SQL> exec dbms_stats.delete_column_stats(ownname=>'SCOTT',TABNAME=>'TT2',COLNAME=>'STATUS');
PL/SQL 過程已成功完成。
SQL> exec :a:='VALID';
PL/SQL 過程已成功完成。
SQL> select object_id,status from tt2 where status=:a;
已選擇123150行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1233 Bytes=22
194)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TT2' (Cost=2 Card=1233 B
ytes=22194)
2 1 INDEX (RANGE SCAN) OF 'IDX_TT2_STATUS' (NON-UNIQUE) (Cos
t=1 Card=493)
Statistics
----------------------------------------------------------
303 recursive calls
0 db block gets
18388 consistent gets
1955 physical reads
0 redo size
2185342 bytes sent via SQL*Net to client
90802 bytes received via SQL*Net from client
8211 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
123150 rows processed
Plan Table
---------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| INDEX RANGE SCAN |IDX_TT2_STATUS | | 493 | | | | | |
| TABLE ACCESS BY INDEX ROWID |TT2 | | 1K| | | | | |
| SELECT STATEMENT | | | | | | | | |
------------------------------------------------------------------------------------------------------------------------
在沒有柱狀圖的情況下，最佳化器採用了預設值，認為選擇性是5%，毫不猶豫就走了索引。
14、以上測試是在CURSOR_SHARING=EXACT情況的，再來看看在CURSOR_SHARING=SIMILAR的情況下是什麼樣的？
SQL> exec :a:='VALID';
PL/SQL 過程已成功完成。
SQL> select object_id,status from tt2 where status=:a;
已選擇123150行。
檢視執行計劃，發現是走全表掃描，再執行：
SQL> exec :a:='INVALID';
PL/SQL 過程已成功完成。
SQL> select object_id,status from tt2 where status=:a;
已選擇123150行。
設定:a='INVALID';
執行還是全表掃描，說明第二次執行的時候BIND PEEKING沒有出現。而第一次執行出現了bind peeking,再次確認：
Plan Table
------------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| INDEX RANGE SCAN |IDX_TT2_STATUS | | 178 | | | | | |
| TABLE ACCESS BY INDEX ROWID |TT2 | | 178 | | | | | |
| SELECT STATEMENT | | | | | | | | |
------------------------------------------------------------------------------------------------------------------------
走了索引，確認了第一次執行是進行了bind peeking的。
15、下面測試cursor_sharing=force的情況
SQL> alter session set cursor_sharing=force;
會話已更改。
SQL> alter system flush shared_pool;
系統已更改。
SQL> exec :a:='INVALID';
PL/SQL 過程已成功完成。
SQL> select object_id,status from tt2 where status=:a;
已選擇178行。
Plan Table
-----------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| INDEX RANGE SCAN |IDX_TT2_STATUS | | 178 | | | | | |
| TABLE ACCESS BY INDEX ROWID |TT2 | | 178 | | | | | |
| SELECT STATEMENT | | | | | | | | |
------------------------------------------------------------------------------------------------------------------------
第一次執行也是做了BIND PEEKING的，
16、結論：
BIND PEEKING對於使用繫結變數的情況下，選擇較優的執行計劃有一定的作用
BIND PEEKING只有在存在柱狀圖的情況下才能工作
BIND PEEKING只在做HARD PARSE的時候才產生，隨後的執行如果不需要HARD PARSE就不會進行BIND PEEKING，這種情況和CURSOR_SHARING無關
由於以上原因，使用繫結變數的時候可以有效的減少PARSE
對於使用不同繫結變數執行計劃應該不同的情況，建議不要使用繫結變數，否則可能會產生隨機的執行計劃（硬分析後的所有執行都使用第一個執行計劃，
執行計劃和第一次執行的引數有關）
[@more@]
使用繫結變數可以減少SQL PARSE，但是使用繫結變數有一個不好的地方，就是對於訪問具有傾斜的列，可能使用錯誤的執行計劃。
在Oracle 9i之前，如果WHERE 條件裡面全部使用繫結變數，那麼只能使用固定的選擇性引數來確定執行計劃。
=操作和>=操作的選擇性為5%，範圍掃描的選擇性為25%。預設值的方式可能生成不好的執行計劃。所以Oracle 9i就出現了一個新的技術，
bind peeking。什麼是bind peeking呢？當SQL第一次執行的時候，最佳化器會根據繫結變數來確定執行計劃（如果存在柱狀圖）。
BIND PEEKING只有當該SQL第一次執行的時候，進行HARD PARSE的時候才進行，第二次呼叫該SQL，就不會再次進行BIND PEEKING。
這種情況下，就存在另外一個風險，如果某個列的傾斜性很厲害，那麼使用BIND PEEKING就是不安全的，因為不同的引數代入，
只能走第一次執行時的執行計劃，那麼執行計劃就像擲色子一樣，要靠運氣了。碰到這種情況，應用就不應該使用繫結變數，而應該改為直接值了。
下面是一個實驗：
1、建立一個表create table tt2 as select * from dba_objects; 也可以再執行幾次，把記錄數搞大一些
2、update tt2 set status='INVALID' where rownum<100;
3、update tt2 set status='IND' where status='VALID' and rownum<3;
4、經過上述操作，就會產生幾萬valid，89條 invalid和2條ind的記錄。下面進行分析
5、create index ind_tt2_status on tt2 (status);
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','TT2',method_opt => 'for all indexed columns size skewonly');
PL/SQL 過程已成功完成。
SQL> select table_name,COLUMN_NAME,endpoint_number,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE from user_tab_histograms where table_name='T
T2' ORDER BY COLUMN_nAME;
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
------------------------------ ------------------------------ --------------- -------------- --------------------
TT2 STATUS 21 3.8063E+35
TT2 STATUS 29819 4.4786E+35
TT2 STATUS 2 3.8063E+35
6、執行：
var a varchar2(10);
exec :a:='VALID';
select object_id,status from tt2 where status=:a;
7、檢視執行計劃（這個時候最好不要用AUTOTRACE，由於BUG，AUTOTRACE顯示的執行計劃是錯誤的），可以透過v$sql_plan，或者乾脆用sql_trace
檢視sql_trace的時候，如果看裸檔案，那麼parse #1 。。。mis=1說明是硬解析，否則就是軟解析
我們使用v$_sql_plan來看：
Plan Table
--------------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| TABLE ACCESS FULL |TT2 | 0 | 123K| 123K| 0 | 0 | 0 | 0 |
| SELECT STATEMENT | | 0 | | 123K| 0 | 0 | 0 | 0 |
------------------------------------------------------------------------------------------------------------------------
8、然後在執行：
exec :a:='INVALID';
select object_id,status from tt2 where status=:a;
檢視執行計劃和上面的相同
Plan Table
--------------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| TABLE ACCESS FULL |TT2 | 0 | 123K| 123K| 0 | 0 | 0 | 0 |
| SELECT STATEMENT | | 0 | | 123K| 0 | 0 | 0 | 0 |
------------------------------------------------------------------------------------------------------------------------
如果生成了TRACE檔案，可以看到：
SELECT OBJECT_NAME,STATUS
FROM
TT2 WHERE STATUS=:A
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.01 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 8213 0.56 0.59 0 18352 0 123154
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8217 0.56 0.60 0 18352 0 123154
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
4 TABLE ACCESS FULL TT2
從這裡可以看出執行了2次parse，其中misses in library cache during parse:1,說明其中一次是硬分析（從裸檔案也可以看出mis=1的有一次）
9、重新整理一下共享池alter system flush shared_pool;
10、然後再執行
exec :a:='INVALID';
select object_id,status from tt2 where status=:a;
看到的執行計劃如下：
Plan Table
-------------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| INDEX RANGE SCAN |IDX_TT2_STATUS | 0 | 178 | 123K| 0 | 0 | 0 | 0 |
| TABLE ACCESS BY INDEX ROWID |TT2 | 0 | 178 | 123K| 0 | 0 | 0 | 0 |
| SELECT STATEMENT | | 0 | | 123K| 0 | 0 | 0 | 0 |
------------------------------------------------------------------------------------------------------------------------
11、再執行：
exec :a:='VALID';
select object_id,status from tt2 where status=:a;
看到的執行計劃還是和上面的相同，走索引範圍掃描。
12、這就是bind peeking的含義，使用繫結變數的時候，能夠共享SQL，其執行計劃是在第一次parse的時候做peeking生成的。根據當前的繫結變數的值。
今後再次執行，就認為SQL是安全的，不再進行peeking，也不會生成新的執行計劃
13、如果刪除柱狀圖，那麼還是否會產生PEEKING呢？
SQL> exec dbms_stats.delete_column_stats(ownname=>'SCOTT',TABNAME=>'TT2',COLNAME=>'STATUS');
PL/SQL 過程已成功完成。
SQL> exec :a:='VALID';
PL/SQL 過程已成功完成。
SQL> select object_id,status from tt2 where status=:a;
已選擇123150行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1233 Bytes=22
194)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TT2' (Cost=2 Card=1233 B
ytes=22194)
2 1 INDEX (RANGE SCAN) OF 'IDX_TT2_STATUS' (NON-UNIQUE) (Cos
t=1 Card=493)
Statistics
----------------------------------------------------------
303 recursive calls
0 db block gets
18388 consistent gets
1955 physical reads
0 redo size
2185342 bytes sent via SQL*Net to client
90802 bytes received via SQL*Net from client
8211 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
123150 rows processed
Plan Table
---------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| INDEX RANGE SCAN |IDX_TT2_STATUS | | 493 | | | | | |
| TABLE ACCESS BY INDEX ROWID |TT2 | | 1K| | | | | |
| SELECT STATEMENT | | | | | | | | |
------------------------------------------------------------------------------------------------------------------------
在沒有柱狀圖的情況下，最佳化器採用了預設值，認為選擇性是5%，毫不猶豫就走了索引。
14、以上測試是在CURSOR_SHARING=EXACT情況的，再來看看在CURSOR_SHARING=SIMILAR的情況下是什麼樣的？
SQL> exec :a:='VALID';
PL/SQL 過程已成功完成。
SQL> select object_id,status from tt2 where status=:a;
已選擇123150行。
檢視執行計劃，發現是走全表掃描，再執行：
SQL> exec :a:='INVALID';
PL/SQL 過程已成功完成。
SQL> select object_id,status from tt2 where status=:a;
已選擇123150行。
設定:a='INVALID';
執行還是全表掃描，說明第二次執行的時候BIND PEEKING沒有出現。而第一次執行出現了bind peeking,再次確認：
Plan Table
------------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| INDEX RANGE SCAN |IDX_TT2_STATUS | | 178 | | | | | |
| TABLE ACCESS BY INDEX ROWID |TT2 | | 178 | | | | | |
| SELECT STATEMENT | | | | | | | | |
------------------------------------------------------------------------------------------------------------------------
走了索引，確認了第一次執行是進行了bind peeking的。
15、下面測試cursor_sharing=force的情況
SQL> alter session set cursor_sharing=force;
會話已更改。
SQL> alter system flush shared_pool;
系統已更改。
SQL> exec :a:='INVALID';
PL/SQL 過程已成功完成。
SQL> select object_id,status from tt2 where status=:a;
已選擇178行。
Plan Table
-----------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| INDEX RANGE SCAN |IDX_TT2_STATUS | | 178 | | | | | |
| TABLE ACCESS BY INDEX ROWID |TT2 | | 178 | | | | | |
| SELECT STATEMENT | | | | | | | | |
------------------------------------------------------------------------------------------------------------------------
第一次執行也是做了BIND PEEKING的，
16、結論：
BIND PEEKING對於使用繫結變數的情況下，選擇較優的執行計劃有一定的作用
BIND PEEKING只有在存在柱狀圖的情況下才能工作
BIND PEEKING只在做HARD PARSE的時候才產生，隨後的執行如果不需要HARD PARSE就不會進行BIND PEEKING，這種情況和CURSOR_SHARING無關
由於以上原因，使用繫結變數的時候可以有效的減少PARSE
對於使用不同繫結變數執行計劃應該不同的情況，建議不要使用繫結變數，否則可能會產生隨機的執行計劃（硬分析後的所有執行都使用第一個執行計劃，
執行計劃和第一次執行的引數有關）
[@more@]
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9907339/viewspace-1050971/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- oracle bind value peeking繫結變數窺視Oracle變數
- 繫結變數、BIND PEEKING、histogram(柱狀圖)的使用變數Histogram
- 繫結變數、BIND PEEKING、histogram(柱狀圖)的使用(zt)變數Histogram
- Event 10053 執行計劃 繫結變數 Bind peeking變數
- 繫結變數的正確使用，bind peeking，Adaptive Cursor Sharing變數APT
- 如何解決執行計劃繫結變數bind peeking重新硬解析變數
- oracle 繫結變數（bind variable）Oracle變數
- 索引失效系列——繫結變數引起的peeking索引變數
- Oracle繫結變數分級（Bind Graduation）Oracle變數
- [SQL]oracle 繫結變數(bind variable)[轉載]SQLOracle變數
- v$sql_bind_capture 獲取繫結變數SQLAPT變數
- v$sql_bind_capture 獲取繫結變數資訊SQLAPT變數
- 透過v$sql_bind_capture 檢視繫結變數。SQLAPT變數
- 通過v$sql_bind_capture 檢視繫結變數。SQLAPT變數
- 繫結變數變數
- Oracle 繫結變數Oracle變數
- 繫結變數和cursor_sharing變數
- v$sql_bind_capture與timestamp型別的繫結變數的數值SQLAPT型別變數
- 檢視繫結變數變數
- 繫結變數窺測變數
- PLSQL使用繫結變數SQL變數
- Oracle之繫結變數Oracle變數
- 關於繫結變數變數
- 【優化】使用繫結變數 OR 不使用繫結變數，這不是問題！優化變數
- ORACLE 繫結變數用法總結Oracle變數
- 【cbo計算公式】No Bind Peeking(五)公式
- Oracle 中bind peeking的處理Oracle
- 【最佳化】使用繫結變數 OR 不使用繫結變數，這不是問題！變數
- zt_繫結變數和cursor_sharing變數
- 繫結變數窺測的演變變數
- Oracle 變數繫結與變數窺視合集Oracle變數
- 繫結變數的測試變數
- Oracle 繫結變數窺探Oracle變數
- 如何獲取繫結變數變數
- Oracle 繫結變數 詳解Oracle變數
- 關於繫結變數的SQL繫結什麼值變數SQL
- MySQL高階特性——繫結變數MySql變數