繫結變數之繫結變數窺探(Bind Peeking)

繫結變數是Oracle解決硬解析的首要利器，能解決OLTP系統中共享池的過度耗用以提高系統效能。對於使用了繫結變數的SQL而言，具體有哪些因素決定其執行計劃？
     1.繫結變數窺探
     2.若未啟用繫結變數窺探，則對於哪些可選擇率會隨著具體輸入值的不同而不同的謂詞條件會使用預設的選擇率。
     具體資料庫是否啟用繫結變數窺探(Bind Peeking)由隱含引數_OPTIM_PEEK_USER_BINDS決定，在9i版本之後該引數值預設為TRUE，代表繫結窺探已經啟用了。
     
     啟用繫結變數窺探，Oracle會在第一次硬解析時根據WHERE條件計算出SELECTIVITY以及CARDINALITY，最大限度的得到SQL的準確執行計劃。但其最大問題在於，不管以後繫結變數傳入何值，它會一直沿用之前硬解析時產生的解析樹和執行計劃(針對於11g之前的版本，11g引入了ACS該問題得到了改善)。該問題可能會導致CBO在某些情況下所選擇的執行計劃並不是最優執行計劃，從而極大的影響了系統的效能。
    操作環境：

準備測試表：

    可以看到在OBJECT_TYPE列存在著嚴重的傾斜，由於該列收集了直方圖資訊，因此在不使用繫結變數的情況下OBJECT_TYPE='INDEX PARTITION'和INDEX PARTITION='VIEW'可能會走不同的執行計劃。具體實驗如下：

可以看到在不使用繫結變數的情況下，object_type='INDEX PARTITION'走的INDEX RANGE SCAN索引範圍掃描，object_type='VIEW'走的INDEX FAST FULL SCAN索引快速全掃描，執行計劃與實際資料的分佈情況相符。
   現在我們將前面的兩條語句改造成使用繫結變數的等價形式。
     

   從上述顯示內容可以看到，select count(*) from t where object_type=‘VIEW’本應該走索引快速全掃描，但由於使用了繫結變數，這條SQL的解析為軟解析，此執行計劃會沿用第一次硬解析的結果：走的是索引範圍掃描，並且Peeked Binds部分的內容依然為'INDEX PARTITION'。
  下面在做同樣的實驗，只不過把順序調整一下，先執行exec :v:='VIEW',然後再執行exec :v:='INDEX PARTITION'， 看看具體效果。
  為避免重用之前的解析樹以及執行計劃，首先重新整理一遍共享池(此步驟儘量不要在生產環境嘗試)
  alter system flush shared_pool;
    從上述顯示內容可以看到，select count(*) from t where object_type=‘INDEX PARTITION’走索引範圍掃描才是最好的執行計劃，但由於使用了綁定變量，這條SQL的解析為軟解析，此執行計劃會沿用第一次硬解析的結果：走的是索引快速全掃描。
   透過以上的兩個實驗驗證了一個問題：當SQL語句中有繫結變數，不管以後繫結變數傳入何值，它會一直沿用之前硬解析時產生的解析樹和執行計劃.
   有沒有辦法讓上述使用繫結變數改寫後的SQL語句再次執行時，選擇走索引快速全掃描呢?有，讓Oracle再次執行硬解析就可以，通常做法為對SQL涉及的表執行DDL操作，因為一旦某表執行了DDL操作，庫快取中所有SQL文字中包含了這個表的Cursor都會被Oracle標記為失效，Oracle再次執行與該表相關的SQL就會使用硬解析。最簡單的DDL操作就是新增註釋的COMMENT語句：
  comment on table t is 'Bind Peeking Test';
  再次執行

點選(此處)摺疊或開啟

1. exec :v:='INDEX PARTITION';
2. select count(*) from t where object_type=:v;
   
3. select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t%';
   
4. SQL_TEXT                                                      SQL_ID                                  VERSION_COUNT EXECUTIONS
   
5. ------------------------------------------------------------ --------------------------------------- ------------- ----------
   
6. select count(*) from t where object_type=:v                  2mbcpzxk94xyg                            1              1
7. 
   
8. select * from table(dbms_xplan.display_cursor('2mbcpzxk94xyg',0,'advanced'));
9. PLAN_TABLE_OUTPUT
   ------------------------------------------------------------------------------------------------------------------------------------------------------
   SQL_ID  2mbcpzxk94xyg, child number 0
   -------------------------------------
   select count(*) from t where object_type=:v
   Plan hash value: 3381337503
   
   ----------------------------------------------------------------------------------
   | Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
   ----------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT  |              |       |       |     1 (100)|          |
   |   1 |  SORT AGGREGATE   |              |     1 |     6 |            |          |
   |*  2 |   INDEX RANGE SCAN| IND_OBJ_TYPE |     2 |    12 |     1   (0)| 00:00:01 |
   ----------------------------------------------------------------------------------
   
   Peeked Binds (identified by position):
   --------------------------------------
      1 - :V (VARCHAR2(30), CSID=31): 'INDEX PARTITION'

看到了什麼？當對SQL涉及的表進行DDL操作後，會重新進行硬解析，重新生成解析樹以及執行計劃。