索引是我們進行最佳化的一種重要方式。實際工作中，一個簡單的索引，可能就會大大提升提高關鍵業務作業效率，最終提升使用者滿意度。在CBO時代，DBA和開發人員經常為索引為什麼不出現在執行計劃中而困惑。

問題提出

使用繫結變數已經是現在成熟專案開發團隊開發規範的一種重要組成部分。對大部分系統場景，特別是OLTP系統，使用繫結變數帶來的好處是不可忽視的。使用繫結變數可以有效減少系統硬解析發生，緩解系統例項資源的消耗和爭用。但是，一些場合下，繫結變數也是會給我們帶來一些困擾。其中，繫結變數窺視peeking是經常會出現的一個問題，會影響到執行計劃的正確生成。

實驗環境準備

實驗選擇Oracle 11gR2 For Linux環境。

SQL> create table t as select * from dba_objects;

Table created

SQL> create index idx_t_owner on t(owner);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

標準SQL實驗

我們首先使用硬解析手段獲取標準的執行計劃。硬解析手段雖然要消耗較多的資源，但是生成的執行計劃通常是唯一且正確的。

//查詢結果省略

SQL> select * from t where wner='SYS';

已選擇58483行。

已用時間: 00: 00: 01.75

SQL> select * from t where wner='SCOTT';

已選擇6行。

已用時間: 00: 00: 00.00

我們從Shared_pool中直接取出執行計劃。

SQL> col sql_text for a40;

SQL> select sql_text, sql_id, version_count from v$sqlarea where sql_text like 'select * from t%';

SQL_TEXT SQL_ID VERSION_COUNT

---------------------------------------- ------------- -------------

select * from t where wner='SYS' 6gzd8z5vm5k0t 1

select * from t where wner='SCOTT' 5ytkakbws70wb 1

v$sqlarea中表示的是父遊標的記錄資訊，相同sql結構的語句都會對應到相同的記錄上，使用sql_id進行區分。獲取到父遊標sql_id之後，就可以獲取到當時的執行計劃。

SQL> select * from table(dbms_xplan.display_cursor('5ytkakbws70wb',null,null));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID 5ytkakbws70wb, child number 0

-------------------------------------

select * from t where wner='SCOTT'

Plan hash value: 1516787156

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)|

| 1 | TABLE ACCESS BY INDEX ROWID| T | 13 | 1261 | 2 (0)|

|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 13 | | 1 (0)|

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OWNER"='SCOTT')

19 rows selected

SQL> select * from table(dbms_xplan.display_cursor('6gzd8z5vm5k0t',null,null));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID 6gzd8z5vm5k0t, child number 0

-------------------------------------

select * from t where wner='SYS'

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 280 (100)| |

|* 1 | TABLE ACCESS FULL| T | 57833 | 5478K| 280 (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OWNER"='SYS')

18 rows selected

此時，我們看到了由於統計量資料分佈的差異，使得相同結構的SQL語句，由於選取值分佈的差異，執行計劃有差異。Owner=’SYS’由於資料量較大，所以選擇進行全表掃描的成本cost較低，CBO選擇生成全表掃描的執行計劃。而scott的資料量較少，選擇索引路徑相對高效些。

繫結變數路徑

下面實驗繫結變數路徑方式。

SQL> var x varchar2(10);

SQL> exec :x := 'SYS';

PL/SQL 過程已成功完成。

已用時間: 00: 00: 00.03

SQL> select * from t where wner=:x;

已選擇58483行。

已用時間: 00: 00: 01.87

SQL> exec :x := 'SCOTT';

PL/SQL 過程已成功完成。

已用時間: 00: 00: 00.00

SQL> select * from t where wner=:x;

已選擇6行。

已用時間: 00: 00: 00.00

我們從shared pool中獲取到父遊標資訊。

SQL> select sql_text, sql_id, version_count, EXECUTIONS from v$sqlarea where sql_text like 'select * from t%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

---------------------------------------- ------------- ------------- ----------

select * from t where wner=:x 25hhj4rsxz9w1 1 2

SQL>

SQL> select count(*) from v$sql where sql_id='25hhj4rsxz9w1';

COUNT(*)

----------

1

//繫結變數資訊

SQL> select bind_data from v$sql where sql_id='25hhj4rsxz9w1';

BIND_DATA

--------------------------------------------------------------------------------

BEDA0B2001004DB6ACF7000101F00120036903535953

注意，雖然執行了兩次，雖然執行兩次使用的繫結變數值不同，但是由於SQL語句結構相同，所以父遊標相同。觀察version_count=1，證明始終只有一個執行計劃。兩次執行共享的是一個執行計劃。下面提取出執行計劃全文。

SQL> select * from table(dbms_xplan.display_cursor('25hhj4rsxz9w1',0,'advanced'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID 25hhj4rsxz9w1, child number 0

-------------------------------------

select * from t where wner=:x

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 280 (100)| |

|* 1 | TABLE ACCESS FULL| T | 57833 | 5478K| 280 (1)| 00:00:04 |

--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1 / T@SEL$1

Outline Data

-------------

Peeked Binds (identified by position):

--------------------------------------

1 - :X (VARCHAR2(30), CSID=873): 'SYS'

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OWNER"=:X)

（篇幅原因，有刪節…）

54 rows selected

從執行計劃中，我們可以看到兩次共享的執行計劃路徑和採用的方案。其中標紅的peeked binds部分，可以看出Oracle在生成這個執行計劃時，是使用’SYS’作為參照值進行生成的。因為’SYS’對應的執行路徑是全表掃描，所以在第二次執行的時候，即使繫結變數X取值為’SCOTT’，由於公用執行計劃的原因，依然使用全表掃描的方式。這就是出現了所謂的binds peeking問題。

Binds peeking是Oracle在生成執行計劃時候所採用的一種技術。當Oracle最佳化器解析語句時，如果這個語句上是攜帶者繫結變數的，就會使用到這種技術。CBO是依據統計量生成執行計劃的，此時需要知道變數取值來確定計劃路徑。這時候Oracle就會“偷偷”看一下送過來SQL中使用的繫結變數取值（‘SYS’），依據這個值生成執行計劃。第二次相同的語句來的時候，就是用第一次peeking生成的執行計劃即可。

這樣，就出現了本篇要說明的問題，就是peeking有時候會影響到SQL執行的效率。如果出現了本例中的情況，Oracle在peeking的時候，恰好peeking到了一個全表掃描或者錯誤的路徑方案，之後所有的路徑都共享這個執行計劃，必然之後會帶來效能上的突然變化。

繫結變數使用的越來越廣泛，那麼我們如何避免由於使用繫結變數而帶來的binds peeking問題呢？筆者個人覺得從如下幾個方面考慮：

ü 列資料分佈的詳細研究。通常，我們都希望資料列取值儘可能分佈均勻，不出現過於偏移的資料分佈狀況。Peeking不是Oracle的罪過，資料分佈不均勻是引起peeking問題的根源。所以，要下力氣研究分析資料分佈規律，儘量避免分佈偏移情況，或者設計對應的解決方案；

ü 進入Oracle10g之後，Oracle引入了多次peeking的方案。也就是對一個繫結變數執行計劃，不是一次peeking就結束。而是根據計算成本情況，不定時的多次取binds值，調整執行計劃。這樣，綜合獲取到適當執行計劃的機率有所提升；

ü 最後是一個“以暴制暴”的方法，既然peeking引起了諸多問題。那麼就透過禁用隱含引數“_optim_peek_user_binds”的方法，禁止使用peeking。隨之帶來的就是執行計劃生成依據的缺失。這種情況下，Oracle更傾向於走索引路徑和巢狀迴圈路徑來生成執行計劃。此時要想控制執行計劃就只能透過outline或者hint來實現；

繫結變數在OLTP系統中，帶來好處多多。但是隨之而來的binds peeking問題，也是我們進行開發和日常運維不能忽視的問題現象。

索引失效系列——繫結變數引起的peeking

相關文章