繫結變數窺測：在物理最佳化階段，查詢最佳化器會窺測繫結變數的值，將它作為文字來使用。這種方法的問題是它生成的執行計劃會依賴第一次生成執行計劃時所提供的值。
--建立索引
SQL> create index i_obj_id on t_var_peek(object_id);

索引已建立。

已用時間: 00: 00: 00.25
---執行計劃選擇了 range scan
SQL> select count(object_id) from t_var_peek where object_id < 10;
已用時間: 00: 00: 00.01
執行計劃
----------------------------------------------------------
Plan hash value: 1952566611

------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    13 |     2   (0)| 00:00:01|
|   1 | SORT AGGREGATE   |          |     1 |    13 |            |         |
|* 2 |   INDEX RANGE SCAN| I_OBJ_ID |     8 |   104 |     2   (0)| 00:00:01|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"<10)
Note
   - dynamic sampling used for this statement
----使用繫結變數
SQL> exec :x1 :=999;
PL/SQL 過程已成功完成。
已用時間: 00: 00: 00.01
SQL> select count(object_id) from t_var_peek where object_id < :x1;
已用時間: 00: 00: 00.00
執行計劃
----------------------------------------------------------
Plan hash value: 1952566611
------------------------------------------------------------------------------

| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    13 |     3   (0)| 00:00:01 |
|   1 | SORT AGGREGATE   |          |     1 |    13 |            |          |
|* 2 |   INDEX RANGE SCAN| I_OBJ_ID | 3439 | 44707 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"Note
   -----
   - dynamic sampling used for this statement

----此時返回的行數和cost 大小是和 :X1=999 的值是一樣的。說明第一次最佳化是使用值999來執行的。結果，查詢最佳化器就選擇了INDEX RANGE SCAN。由於遊標是共享的，因此是這個選擇影響了第二次使用9作為條件的查詢語句。
SQL> exec :x1 :=9;
PL/SQL 過程已成功完成。
已用時間: 00: 00: 00.01
SQL> select count(object_id) from t_var_peek where object_id < :x1;
已用時間: 00: 00: 00.00
執行計劃
----------------------------------------------------------
Plan hash value: 1952566611
------------------------------------------------------------------------------

| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    13 |     3   (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

|* 2 | INDEX RANGE SCAN| I_OBJ_ID | 3439 | 44707 | 3 (0)| 00:00:01 |

------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"Note
-----
   - dynamic sampling used for this statement
---在使用 <9 時請注意執行計劃的rows 和 bytes cost的值！
SQL> select count(object_id) from t_var_peek where object_id < 9;
已用時間: 00: 00: 00.01

執行計劃
----------------------------------------------------------
Plan hash value: 1952566611
------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    13 |     2   (0)| 00:00:01 |
|   1 | SORT AGGREGATE   |          |     1 |    13 |            |          |
|* 2 |   INDEX RANGE SCAN| I_OBJ_ID |     7 |    91 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------
    Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"<9)
Note
-----
   - dynamic sampling used for this statement
--此時：x1 的值還是 9 但是執行計劃的和前面 :X1 =999 和 :X1=999 的執行計劃是一樣的，明顯的發生了變數窺測！
SQL> select count(object_id) from t_var_peek where object_id < :x1;
已用時間: 00: 00: 00.00
執行計劃
----------------------------------------------------------
Plan hash value: 1952566611
------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    13 |     3   (0)| 00:00:01 |
|   1 | SORT AGGREGATE   |          |     1 |    13 |            |          |
|* 2 |   INDEX RANGE SCAN| I_OBJ_ID | 3439 | 44707 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

     Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"Note
-----
   - dynamic sampling used for this statement
   只要遊標還儲存在庫快取中並且可以被共享，就可以被重用。不管與它相關的執行計劃的效率如何，這種事情都會發生。
解決辦法：
  為了解決這個問題，Oracle11g中引入了一個稱為擴充套件的遊標共享（extended cursor sharing，也稱為適應性遊標共享，adaptive cursor sharing）的新功能。它的目的是在重用一個已經存在的但是會導致執行效率低下的遊標時能夠自動進行識別。

http://space.itpub.net/22664653/viewspace-673026

【SQL 調優】繫結變數窺測

相關文章