Oracle綁定變數窺探

標籤：oracle 綁定變數 窺探

隨著具體輸入值的不同，SQL的where條件的可選擇率(Selectivity)和結果集的行數(Cardinality)可能會隨之發生變化，而Selectivity和Cardinality的值會直接影響CBO對於相關執行步驟成本值的估算，進而影響CBO對SQL執行計畫的選擇。這就意味著隨著具體輸入值的不同，目標SQL執行計畫可能會發生變化。

對於不使用綁定變數的SQL而言，具體輸入值一量發生了變化，目標SQL的SQL文本就會隨之發生變化，這樣Oracle就能很容易地計算出對應Selectivity和Cardinality的值，進而據此來選擇執行計畫。但對於使用綁定變數的SQL而言，情況就完全不一樣了，因為現在無論對應綁定變數的具體輸入值是什麼，目標SQL的SQL文本都是一模一樣的，這種情況下Oracle應該如何來決定目標SQL的執行計畫呢？

對於使用了綁定變數的SQL而言，Oracle可以選擇如下兩種方法來決定其執行計畫：

• 使用綁定變數窺探

• 如果不使用綁定變數窺探，則對於那些可選擇率可能會隨著具體輸入值的不同而不同的謂詞條件使用預設的可選擇率(例如5%)。

綁定變數窺探(Bind Peeking)是在Oracle 9i中引入的，是否啟用綁定變數窺探受隱含參數_OPTIM_PEEK_USER_BINDS的控制，_OPTIM_PEEK_USER_BINDS的預設值是TRUE，表示在Oracle 9i及其後續的版本中，綁定變數窺探在預設情況下就已經被啟用了。

當綁定變數窺探被啟用後，每當Oracle以硬解析的方式解析使用了綁定變數的目標SQL時，Oracle都會實際窺探(Peeking)一下對應綁定變數的具體輸入值，並以這些具體輸入值為標準，來決定這些使用了綁定變數的目標SQL的where條件的Selectivity和Cardinality的值，並據此來選擇該SQL的執行計畫。這個“窺探(Peeking)”的動作只有在硬解析的時候才會執行，當使用了綁定變數的目標SQL再次執行時(此時對應的是軟解析/軟軟解析)，即便此時對應綁定變數的具體輸入值和之前硬解析時對應的值不同，Oracle也會沿用之前硬解析時所產生的解析樹和執行計畫，而不再重複執行上述“窺探”的動作。

綁定變數窺探的好處是顯而易見的，因為有了綁定變數窺探，Oracle在計算目標SQL的where條件的Selectivity和Cardinality的值時，就可以避免使用預設的可選擇率，這樣就有更大的可能性得到該SQL準確的執行計畫。同樣，綁定變數窺探的壞處也是顯而易見的，對於那些執行計畫可能會隨著對應綁定變數具體輸入值的不同而變化的目標SQL而言，一旦啟用了綁定變數窺探，其執行計畫就會被固定下來，到於這個固定下來的執行計畫到底是什麼，則完全倚賴於該SQL在硬解析時傳入的對應綁定變數的具體值。這意味著一量啟用了綁定變數窺探，目標SQL在後續執行時就會沿用之前硬解析所產生的解析樹和執行計畫，即使這種沿用並不適合於當前的情形。

綁定變數窺探這種不管後續傳入的綁定變數的具體輸入值是什麼而一直沿用之前硬解析時所產生的解析權和執行計畫的特性一直飽受詬病(這種狀況一直到Oracle 11g中引入自適應遊標共用後才有所緩解)，因為它可能使CBO在某些情況下(對應綁定變數的某些具體輸入值)所選擇的執行計畫並不是目標SQL在當前情形下是最優執行計畫，而且它可能會帶來目標SQL執行計畫的突然改變，進而直接影響應用系統的效能。

比如某個SQL的執行計畫隨著綁定變數具體輸入值的不同會對應兩個執行計畫，一個是走對索引的索引範圍掃描，另一個是走對索引的索引快速全掃描。正常情況下，對絕大多數綁定變數輸入值，執行計畫都應該走索引範圍掃描，極少數情況下會走索引快速全掃描。但假如有一開該SQL對應的Shared Cursor被age out出Shared Pool了，那麼當該SQL再次執行時Oracle就得硬解析。不幸的是如果這次硬解析時傳入的綁定變數輸入值恰好是走索引快速全掃描所對應的極少數的情形，那麼後續的SQL走會走這個執行計畫，這種情況下該SQL的執行效率就很可能比之前慢一個甚至多個數量級。表現在在應用系統上就是突然有一天發現某個應用跑不動了，而之前一直是好好的。

下面看一個綁定變數窺探的執行個體：

建立測試表T1及索引並收集統計資訊

[email protected]>create table t1 as select * from dba_objects;Table created.[email protected]>create index idx_t1 on t1(object_id);Index created.[email protected]>select count(*) from t1;  COUNT(*)----------     72005[email protected]>select count(distinct(object_id)) from t1;COUNT(DISTINCT(OBJECT_ID))--------------------------     72005[email protected]>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>‘T1‘,estimate_percent=>100,cascade=>true,method_opt=>‘for all columns size 1‘,no_invalidate=>false);PL/SQL procedure successfully completed.

執行如下兩個sql並查看Oracle對SQL的解析情況

[email protected]>select count(*) from t1 where object_id between 999 and 1000;  COUNT(*)---------- 2[email protected]>select count(*) from t1 where object_id between 999 and 60000;  COUNT(*)----------     58180[email protected]>col sql_text for a80[email protected]>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘select count(*) from t1 %‘;SQL_TEXT SQL_ID  VERSION_COUNT EXECUTIONS-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd     11select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn     11

從查詢結果可以看出，Oracle在執行上述SQL時都使用了硬解析。Oracle分別為上述兩個SQL各自產生了一個Parent Cursor和一個Child Cursor。

再查看執行計畫：

650) this.width=650;" src="https://s5.51cto.com/wyfs02/M01/8E/58/wKioL1i-V5HTyQs-AABEBprX2lI104.png" style="float:none;" title="1.png" alt="wKioL1i-V5HTyQs-AABEBprX2lI104.png" />

650) this.width=650;" src="https://s5.51cto.com/wyfs02/M02/8E/5A/wKiom1i-V5KCcftKAABHNo2Q7oY075.png" style="float:none;" title="2.png" alt="wKiom1i-V5KCcftKAABHNo2Q7oY075.png" />

從執行計畫可以看出between 999 and 1000條件的SQL走的是索引範圍掃描，而between 999 and 60000走的執行計畫是索引快速全掃描。

現在我們將全面的兩個SQL改造成使用綁定變數的等價形式。定義兩個綁定變數x和y，並分別給它們賦值999和1000。

[email protected]>var x number;[email protected]>var y number;[email protected]>exec :x := 999;PL/SQL procedure successfully completed.[email protected]>exec :y := 1000;PL/SQL procedure successfully completed.

顯然，此時用綁定變數x和y的改寫形式“between :x and :y”與原來的“between 999 and 1000”是等價的。而且只要將y重新賦值為60000，則又和“between 999 and 60000”等價了。

現在x和y的值分別為999和100，執行改寫後的sql

[email protected]>select count(*) from t1 where object_id between :x and :y;  COUNT(*)---------- 2[email protected]>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘select count(*) from t1 %‘;SQL_TEXT SQL_ID  VERSION_COUNT EXECUTIONS-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd     11select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn     11select count(*) from t1 where object_id between :x and :y 9dhu3xk2zu531     11

從上述查詢結果可以看到，Oracle在第一次執行上述等價SQL時也是用的硬解析

650) this.width=650;" src="https://s2.51cto.com/wyfs02/M01/8E/5A/wKiom1i-WbDhyFLFAACXGqnFHA4818.png" title="1.png" alt="wKiom1i-WbDhyFLFAACXGqnFHA4818.png" />

從執行計畫看，此時是對索引IDX_T1走的索引範圍掃描，而且Oracle評估出來執行這個索引範圍掃描所返回結果集的Cardinality的值為3。並注意到“Peeked Binds”部分的內容為“1 - :X (NUMBER): 999 2 - :Y (NUMBER): 1000”，這說明Oracle在硬解析上述SQL的過程中確實使用了綁定變數窺探，且做“窺探”這個動作時看到的綁定變數x和y的具體輸入值分別為999和1000。

現在保持x不變，將y修改為60000：

[email protected]>exec :y := 60000;PL/SQL procedure successfully completed.[email protected]>select count(*) from t1 where object_id between :x and :y;  COUNT(*)----------     58180

650) this.width=650;" src="https://s3.51cto.com/wyfs02/M01/8E/5B/wKiom1i-W1GjIt6yAAA2k1Z_Uos382.png" style="float:none;" title="1.png" alt="wKiom1i-W1GjIt6yAAA2k1Z_Uos382.png" />

從上述查詢結果可以看出上述SQL對應的VERSION_COUNT的值為1，列EXECUTIONS的值為2，這說明Oracle在第二次執行該SQL時用的是軟解析。

650) this.width=650;" src="https://s3.51cto.com/wyfs02/M01/8E/59/wKioL1i-W1LwF0D7AACXQcddp3o111.png" style="float:none;" title="2.png" alt="wKioL1i-W1LwF0D7AACXQcddp3o111.png" />

從執行計畫上可以看出，此時SQL的執行計畫依然走的是對索引IDX_T1走的索引範圍掃描，並且“Peeked Binds”部分的內容依然為“1 - :X (NUMBER): 999 2 - :Y (NUMBER): 1000”。

之前在不使用綁定變數時，我們已經知道Oracle在執行“between 999 and 60000”條件時走的是索引快速全掃描。但第二次執行使用綁定變數等價改寫的SQL時，即使綁定變數x和y的具體的輸入值是999和60000，但Oracle這裡依然沿用該SQL之前硬解析時(對應綁定量x和y的具體的輸入值是999和1000)所產生的解析樹和執行計畫，而不再重複執行“窺探”的動作。

如果想讓上述等價SQL再次走索引快速全掃描，只需要讓Oracle再次執行SQL時使用硬解析就行。因為一旦使用硬解析，Oracle就會再執行一次“窺探”的動作。讓Oracle再次執行目標SQL時使用硬解析的方法有很多，其中很常見的一種方法是對目標SQL中所涉及的表執行DDL操作。因為一旦對某個表執行了DDL操作，庫緩衝 中所有在SQL文本中包含了這個表的Shared Cursor都會被Oracle標記為失效(invalid)，這意味著這些Shared Cursor中儲存的解析樹和執行計畫將不再能被重用，所以當Oracle再次執行與這個表相關的SQL時就會使用硬解析。這裡選擇對錶添加註釋(COMMENT)，它也是DDL操作。

對錶T1執行COMMENT語句並執行等價SQL

[email protected]>comment on table t1 is ‘Test table for Bind Peeking‘;Comment created.[email protected]>select count(*) from t1 where object_id between :x and :y;  COUNT(*)----------     58180[email protected]>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘select count(*) from t1 %‘;SQL_TEXT SQL_ID  VERSION_COUNT EXECUTIONS-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd     11select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn     11select count(*) from t1 where object_id between :x and :y 9dhu3xk2zu531     11

從上面的查詢結果可以看到等價SQL對應的列VERSION_COUNT的值為1，列EXECUTIONS的值由之前的2變為了現在的1，說明Oracle在第三次執行該SQL時用的是硬解析(EXECUTIONS的值為1，是因為Oracle在這裡重建了一對Parent Cursor和Child Cursor，原先EXECUTIONS的值為2所對應的Shared Cursor已經被Oracle標記為invalid，相當於被廢棄了)。

650) this.width=650;" src="https://s5.51cto.com/wyfs02/M00/8E/5B/wKiom1i-X_CSPyGlAACbQqs2_bE113.png" title="1.png" alt="wKiom1i-X_CSPyGlAACbQqs2_bE113.png" />

從執行計畫可以看出，現在執行計畫走的是索引快速全掃描，而Oracle評估出來執行這個索引快速全掃描所返回結果集的Cardinality的值為57646。並且“Peeked Binds”部分的內容依然為“1 - :X (NUMBER): 999 2 - :Y (NUMBER): 60000”。說明Oracle在執行上述SQL的過程中確實又一次使用了綁定變數窺探，且做“窺探”這個動作時看到的綁定變數x和y的具體輸入值分別為999和60000。

現在把隱含參數_OPTIM_PEEK_USER_BINDS的值設為FALSE以關閉綁定變數窺探：

[email protected]>alter session set "_optim_peek_user_binds"=false;Session altered.

然後保持x的值不變，將y值修改為1000

[email protected]>select count(*) from t1 where object_id between :x and :y;  COUNT(*)---------- 2[email protected]>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘select count(*) from t1 %‘;SQL_TEXT SQL_ID  VERSION_COUNT EXECUTIONS-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd     11select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn     11select count(*) from t1 where object_id between :x and :y 9dhu3xk2zu531     22

從上面的查詢結果可以看到等價SQL對應的列VERSION_COUNT和列EXECUTIONS的值均由1變為了現在的2，說明Oracle在第四次執行該SQL時使用硬解析。VERSION_COUNT的值為2，意味著該SQL所在的Parent Cursor下掛了兩個Child Cursor。從如下查詢結果可以看出該SQL確實有兩個Child Cursor：

[email protected]>select plan_hash_value,child_number from v$sql where sql_id=‘9dhu3xk2zu531‘;PLAN_HASH_VALUE CHILD_NUMBER--------------- ------------     1410530761    0     2351893609    1

顯然，我們把綁定變數窺探關閉後再次執行SQL時所對應的解析權和執行計畫應該儲存在CHILD_NUMBER為1的Child Cursor中。查看執行計畫

650) this.width=650;" src="https://s2.51cto.com/wyfs02/M00/8E/5A/wKioL1i-Zo7wJFiJAACPkGWks_M680.png" title="1.png" alt="wKioL1i-Zo7wJFiJAACPkGWks_M680.png" />

從執行計畫可以看出Oracle此時的執行計畫已經從之前的索引快速全掃描變為 現在的索引範圍掃描。而且Oracle評估出來執行這個索引範圍掃描所返回結果集的Cardinality的值為180。注意Outline Data部分有“OPT_PARAM(‘_optim_peek_user_binds‘ ‘false‘)”，而且執行計畫中沒有“Peeking Binds”部分內容，說明此時Oracle已經禁用了綁定變數窺探。

前面已經介紹了使用DDL操作可以讓Oracle再次執行SQL時使用硬解析，但這種方法的弊端在於其影響範圍還是太廣，因為一旦對某個表執行了DDL操作，再次執行與這個表相關的所有SQL時就會全部使用硬解析。這是很不好的，特別是對於OLTP類型的應用系統而言，因為這可能會導致短時間內的硬解析數量劇增，進而影響系統的效能。

下面再來介紹一種就去讓Oracle再次執行目標SQL時使用硬解析，但其影響範圍公限於目標SQL所對應的Shared Cursor，也就是說它可以做到讓Oracle在執行目標SQL時使用硬解析，在執行其他所有SQL時都和原來一樣保持不變。

這種方法就是使用DBMS_SHARED_POOL.PURGE。它是從Oracle 10.2.0.4開始引入的一種方法，它可以用來刪除指定的緩衝在庫緩衝中的Shared Cursor。DBMS_SHARED_POOL.PURGE可以讓Oracle在執行目標SQL時使用硬解析的原理是顯而易見的——如果某個SQL對應的Shared Cursor被刪除了，Oracle再次執行該SQL時自然就會使用硬解析了。

查看目標SQL對應的ADDRESS和HASH_VALUE值：

[email protected]>select sql_text,sql_id,version_count,executions,address,hash_value from v$sqlarea where sql_text like ‘select count(*) from t1 %‘;SQL_TEXT SQL_ID  VERSION_COUNT EXECUTIONS ADDRESS   HASH_VALUE-------------------------------------------------------------------------------- --------------------------------------- ------------- ---------- ---------------- ----------select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd     11 00000000B4D1B130 1143368397select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn     11 00000000B4D1AA90  924127028select count(*) from t1 where object_id between :x and :y 9dhu3xk2zu531     22 00000000B4CC4840 2247955553

使用dbms_shared_pool.purge刪除目標SQL的Shared Cursor：

[email protected]>exec sys.dbms_shared_pool.purge(‘00000000B4CC4840,2247955553‘,‘c‘);PL/SQL procedure successfully completed.[email protected]>select sql_text,sql_id,version_count,executions,address,hash_value from v$sqlarea where sql_text like ‘select count(*) from t1 %‘;SQL_TEXT SQL_ID  VERSION_COUNT EXECUTIONS ADDRESS   HASH_VALUE-------------------------------------------------------------------------------- --------------------------------------- ------------- ---------- ---------------- ----------select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd     11 00000000B4D1B130 1143368397select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn     11 00000000B4D1AA90  924127028

從上述查詢結果可以看出，dbms_shared_pool.purge確實已經刪除了目標sql對應的Shared Cursor。

需要注意的是，如果在10.2.0.4中使用dbms_shared_pool.purge，則在使用之前必須特工設定event 5614566(alter session set events ‘5614566 trace name context forever‘)，否則dbms_shared_pool.purge將不起作用，這個限制在10.2.0.4以上的版本中已經不存在了。

現在保持x值不變，將y修改為60000，並執行目標SQL：

[email protected]>exec :y := 60000;PL/SQL procedure successfully completed.[email protected]>select count(*) from t1 where object_id between :x and :y;  COUNT(*)----------     58180     [email protected]>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘select count(*) from t1 %‘;SQL_TEXT SQL_ID  VERSION_COUNT EXECUTIONS-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd     11select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn     11select count(*) from t1 where object_id between :x and :y 9dhu3xk2zu531     21[email protected]>select plan_hash_value,child_number from v$sql where sql_id=‘9dhu3xk2zu531‘;PLAN_HASH_VALUE CHILD_NUMBER--------------- ------------     2351893609    0

從上面的查詢結果可以看到該SQL對應的VERSION_COUNT的值為2，EXECUTIONS的值為1。EXECUTIONS的值為1說明Oracle在執行些SQL時確實是在用硬解析，但VERSION_COUNT的值應該為1才對，從查詢中也看到目標SQL的Parent Cursor下確實只掛了一個Child Cursor，所以VERSION_COUNT的值應該是1而不是2(也許是Oracle的BUG，不再深究)。

從執行計畫中可以看出，Oracle此時仍然選擇索引範圍掃描，而且Oracle評估出來執行這個索引範圍掃描返回的結果集的Cardinality的值依然是180。

650) this.width=650;" src="https://s2.51cto.com/wyfs02/M00/8E/5C/wKiom1i-bSni1GokAACUucZJtPw938.png" title="1.png" alt="wKiom1i-bSni1GokAACUucZJtPw938.png" />

這意味著當我們把綁定變數窺探關閉後，無論對綁定變數x和y傳入的值是多少，都不會影響Oracle對於目標SQL執行計畫的選擇。這也契合了之前提到的：如果不使用綁定變數窺探，則對那些可選擇率可能會隨著具體輸入值的變化的謂詞條件而言，Oracle會使用預設的可選擇率(例如5%)。

那180是如何計算出來的呢？

對於上述SQL其where條件的Selectivity和Cardinality的計算公式如下所示：

Cardinality = NUM_ROWS * Selectivity

Selectivity = 0.05*0.05

上述公式適用于禁用了綁定變數窺探且where條件為“目標列between x and y”的Selectivity和Cardinality的計算

NUM_ROWS表示目標列所在列的記錄數

where條件為“目標列between x and y”，相當於“目標列>=x and 目標列<=y”,對於“目標列>=x and 目標列<=y”而言，Oracle均會使用5%的可選擇率，所以可選擇率就是0.05*0.05。

代入公式進行計算，計算結果為180。

[email protected]>select table_name,num_rows from dba_tables where owner=user and table_name=‘T1‘;TABLE_NAME     NUM_ROWS------------------------------------------------------------------------------------------ ----------T172005[email protected]>select round(72005*0.05*0.05) from dual;ROUND(72005*0.05*0.05)----------------------   180

參考：《基於Oracle的SQL最佳化》

本文出自 “DBA Fighting!” 部落格，請務必保留此出處http://hbxztc.blog.51cto.com/1587495/1904004

Oracle綁定變數窺探