關於繫結變數
關於繫結變數一直理解的不是很清晰，最近看文件，基本瞭解。
什麼是繫結變數？
查詢通常只是因為改變where子句中的內容而產生不同的結果。為了在這種情況下避免硬解析，需要使用繫結變數(bind variable)。它是使用者放入查詢中的佔位符，它會告訴Oracle"我會隨後為這個變數提供一個值，現在需要生成一個方案，但我實際執行語句的時 候，我會為您提供應該使用的實際值"。
select * from emp where ename='KING'; //不使用繫結變數
select * from emp where ename=:bv //使用繫結變數
一般在 procedure or function 中使用，可以最佳化共享池的使用。
一、Oracle SQL語句共享區的機制
1、SGA 區結構：
Oracle 資料庫啟動時，在記憶體中分配了一大片空間，為系統全域性區(System Global Area)，其中包含 Sql 共享池及資料快取器(Data Buffer Cache)。SGA 區的共享池部分主要由三個區域組成: 庫快取, 字典快取, 控制結構。庫快取包括共享 SQL 區，私有SQL區，PL/SQL 過程及包, 及控制結構，如鎖及庫快取handles。使用者執行過的 Sql 語句存放於 Sql 共享池中，以便可以重用，提高其效率。
2、SQL語句在記憶體中的分佈：
Oracle 將其執行的每一條 SQL 語句存於共享SQL 區及私有 SQL 區中。當Oracle 發現兩個使用者執行相同的SQL語句時，則為這些使用者重用SQL共享區。但是，每一使用者必須在私有SQL區中擁有該語句的一份獨立複製。共享SQL 區包含單一SQL語句或相同的SQL語句的解析樹及執行計劃。透過為多個相同的DML語句使用一個共享SQL區，Oracle 節省了記憶體的使用, 特別是當許多使用者使用同一應用時。 共享SQL區永遠駐留在共享池中。
3、SQL語句解析時進行的記憶體分配操作：
當一個SQL語句被提交至Oracle 去執行時，Oracle 自動地執行以下記憶體分配步驟:
Oracle 檢查共享池，看是否在共享SQL區中已存在相同的語句。 若有，則該共享SQL區被用於執行該語句的新例項的後續操作。 相應地，若在共享池中無該語句，則Oracle在共享池中分配一新的共享SQL區,其尺寸決定於該語句的複雜性。 若一個SQL語句要求新的共享SQL區而整個共享池已被分配完畢，則 Oracle 可透過一個最近最少修改機理從共享池中釋放部分專案，直至可為新語句的共享SQL區提供足夠的空間。 若 Oracle釋放了一個共享 SQL 區，則與該區相關聯的SQL語句在下次重執行時，須重新解析並重新分配至另一共享SQL區。 在兩種情況下，使用者專用SQL區與包含該語句的共享SQL區相關聯。
因而，若能使語句得到共享，則其將減少記憶體的佔用，同時，減少了cpu 的佔用，加快了語句執行的速度。
即使一個游標仍處於開啟狀態，若其很久未被使用了，則其共享區也可能被從共享池中移出。若該游標以後又被用於執行其語句，則Oracle重解析該語句並且在共享池中分配一新的共享SQL區。
4、私有SQL區
私有SQL區包含繫結資訊及執行時緩衝等資料。 每一個提交一個SQL語句的會話均有一個私有SQL區。 每一提交相同SQL語句的使用者有其使用單一共享SQL區的私有SQL區。許多私有SQL區可以與同一共享SQL區相關聯
一個私有SQL區包括一個永久區和一個執行時區:
一個永久區包含在執行過程中保持的繫結資訊，資料型別轉換的程式碼(在定義的資料型別與查詢列的資料型別不一致時), 及其它狀態資訊(比如遞迴或遠端游標數或並行查詢的狀態)。 永久區的尺寸決定於繫結變數的數目及語句中指定的列數。 例如, 若一個查詢中指定了很多列，則永久區要大一些。
執行時區包含SQL語句被執行時使用的一些資訊。 執行時區的尺寸信賴於被執行的SQL語句的型別及其複雜性及被該語句處理的行的尺寸。 一般而言, 用於INSERT, UPDATE, 及 DELETE 的語句其執行區要比 SELECT 語句所需的執行區尺寸要小。
繫結變數和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
* 對於使用不同繫結變數執行計劃應該不同的情況，建議不要使用繫結變數，否則可能會產生隨機的執行計劃（硬分析後的所有執行都使用第一個執行計劃，執行計劃和第一次執行的引數有關）
什麼是繫結變數？
查詢通常只是因為改變where子句中的內容而產生不同的結果。為了在這種情況下避免硬解析，需要使用繫結變數(bind variable)。它是使用者放入查詢中的佔位符，它會告訴Oracle"我會隨後為這個變數提供一個值，現在需要生成一個方案，但我實際執行語句的時 候，我會為您提供應該使用的實際值"。
select * from emp where ename='KING'; //不使用繫結變數
select * from emp where ename=:bv //使用繫結變數
一般在 procedure or function 中使用，可以最佳化共享池的使用。
一、Oracle SQL語句共享區的機制
1、SGA 區結構：
Oracle 資料庫啟動時，在記憶體中分配了一大片空間，為系統全域性區(System Global Area)，其中包含 Sql 共享池及資料快取器(Data Buffer Cache)。SGA 區的共享池部分主要由三個區域組成: 庫快取, 字典快取, 控制結構。庫快取包括共享 SQL 區，私有SQL區，PL/SQL 過程及包, 及控制結構，如鎖及庫快取handles。使用者執行過的 Sql 語句存放於 Sql 共享池中，以便可以重用，提高其效率。
2、SQL語句在記憶體中的分佈：
Oracle 將其執行的每一條 SQL 語句存於共享SQL 區及私有 SQL 區中。當Oracle 發現兩個使用者執行相同的SQL語句時，則為這些使用者重用SQL共享區。但是，每一使用者必須在私有SQL區中擁有該語句的一份獨立複製。共享SQL 區包含單一SQL語句或相同的SQL語句的解析樹及執行計劃。透過為多個相同的DML語句使用一個共享SQL區，Oracle 節省了記憶體的使用, 特別是當許多使用者使用同一應用時。 共享SQL區永遠駐留在共享池中。
3、SQL語句解析時進行的記憶體分配操作：
當一個SQL語句被提交至Oracle 去執行時，Oracle 自動地執行以下記憶體分配步驟:
Oracle 檢查共享池，看是否在共享SQL區中已存在相同的語句。 若有，則該共享SQL區被用於執行該語句的新例項的後續操作。 相應地，若在共享池中無該語句，則Oracle在共享池中分配一新的共享SQL區,其尺寸決定於該語句的複雜性。 若一個SQL語句要求新的共享SQL區而整個共享池已被分配完畢，則 Oracle 可透過一個最近最少修改機理從共享池中釋放部分專案，直至可為新語句的共享SQL區提供足夠的空間。 若 Oracle釋放了一個共享 SQL 區，則與該區相關聯的SQL語句在下次重執行時，須重新解析並重新分配至另一共享SQL區。 在兩種情況下，使用者專用SQL區與包含該語句的共享SQL區相關聯。
因而，若能使語句得到共享，則其將減少記憶體的佔用，同時，減少了cpu 的佔用，加快了語句執行的速度。
即使一個游標仍處於開啟狀態，若其很久未被使用了，則其共享區也可能被從共享池中移出。若該游標以後又被用於執行其語句，則Oracle重解析該語句並且在共享池中分配一新的共享SQL區。
4、私有SQL區
私有SQL區包含繫結資訊及執行時緩衝等資料。 每一個提交一個SQL語句的會話均有一個私有SQL區。 每一提交相同SQL語句的使用者有其使用單一共享SQL區的私有SQL區。許多私有SQL區可以與同一共享SQL區相關聯
一個私有SQL區包括一個永久區和一個執行時區:
一個永久區包含在執行過程中保持的繫結資訊，資料型別轉換的程式碼(在定義的資料型別與查詢列的資料型別不一致時), 及其它狀態資訊(比如遞迴或遠端游標數或並行查詢的狀態)。 永久區的尺寸決定於繫結變數的數目及語句中指定的列數。 例如, 若一個查詢中指定了很多列，則永久區要大一些。
執行時區包含SQL語句被執行時使用的一些資訊。 執行時區的尺寸信賴於被執行的SQL語句的型別及其複雜性及被該語句處理的行的尺寸。 一般而言, 用於INSERT, UPDATE, 及 DELETE 的語句其執行區要比 SELECT 語句所需的執行區尺寸要小。
繫結變數和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
* 對於使用不同繫結變數執行計劃應該不同的情況，建議不要使用繫結變數，否則可能會產生隨機的執行計劃（硬分析後的所有執行都使用第一個執行計劃，執行計劃和第一次執行的引數有關）
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/79499/viewspace-159313/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Vue select 繫結動態變數Vue變數
- 在繫結變數下使用outline變數
- MySQL高階特性——繫結變數MySql變數
- [20180930]in list與繫結變數.txt變數
- [20180912]PLSLQ與繫結變數.txt變數
- [20210120]in list與繫結變數個數.txt變數
- [20180930]in list與繫結變數個數.txt變數
- 關於動態字串的繫結字串
- 關於JavaScript中this的軟繫結JavaScript
- 【ORACLE】Oracle繫結變數知識梳理Oracle變數
- 繫結變數窺視測試案例變數
- [20220414]toad與繫結變數peek.txt變數
- [20200326]繫結變數抓取與NULL值.txt變數Null
- 如何用FGA得到繫結變數的值變數
- 如何在對in操作使用變數繫結(轉)變數
- 關於js變數提升JS變數
- 關於JavaScript變數提升JavaScript變數
- Javascrip—關於this繫結機制的解析（12）Java
- [20231210]執行計劃與繫結變數.txt變數
- [20190506]檢視巢狀與繫結變數.txt巢狀變數
- Sqlserver 關於臨時表和表變數的總結SQLServer變數
- [20221103]繫結變數的分配長度11.txt變數
- [20221030]繫結變數的分配長度10.txt變數
- [20191213]共享池繫結變數的值在哪裡.txt變數
- 透過v$sql_bind_capture 檢視繫結變數。SQLAPT變數
- V$sql查詢未使用繫結變數的語句SQL變數
- [20211227]抽取跟蹤檔案中的繫結變數值.txt變數
- [20191216]共享池繫結變數的值在哪裡2.txt變數
- [20210112]完善查詢繫結變數指令碼bind_cap.txt變數指令碼
- 越來越發現自己不懂的還是不少--繫結變數變數
- Oracle中如何查詢未使用繫結變數的SQL語句？Oracle變數SQL
- Oracle資料傾斜導致的問題-無繫結變數Oracle變數
- Oracle資料傾斜導致的問題-有繫結變數Oracle變數
- zepto繫結事件改變冒泡事件流事件
- Grails中如何繫結引數AI
- 【CURSOR】Oracle繫結變數、執行計劃對遊標的影響Oracle變數
- oracle 11g 新特性之動態繫結變數窺視(一)Oracle變數
- oracle 11g 新特性之動態繫結變數窺視(二)Oracle變數
- 關於單例及靜態變數測試單例變數