溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
隨著具體輸入值的不同，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及索引并收集統計信息
zx@MYDB>create table t1 as select * from dba_objects; Table created. zx@MYDB>create index idx_t1 on t1(object_id); Index created. zx@MYDB>select count(*) from t1; COUNT(*) ---------- 72005 zx@MYDB>select count(distinct(object_id)) from t1; COUNT(DISTINCT(OBJECT_ID)) -------------------------- 72005 zx@MYDB>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的解析情況
zx@MYDB>select count(*) from t1 where object_id between 999 and 1000; COUNT(*) ---------- 2 zx@MYDB>select count(*) from t1 where object_id between 999 and 60000; COUNT(*) ---------- 58180 zx@MYDB>col sql_text for a80 zx@MYDB>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 1 1 select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1
從查詢結果可以看出，Oracle在執行上述SQL時都使用了硬解析。Oracle分別為上述兩個SQL各自生成了一個Parent Cursor和一個Child Cursor。
再查看執行計劃：
從執行計劃可以看出between 999 and 1000條件的SQL走的是索引范圍掃描，而between 999 and 60000走的執行計劃是索引快速全掃描。
現在我們將全面的兩個SQL改造成使用綁定變量的等價形式。定義兩個綁定變量x和y，并分別給它們賦值999和1000。
zx@MYDB>var x number; zx@MYDB>var y number; zx@MYDB>exec :x := 999; PL/SQL procedure successfully completed. zx@MYDB>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
zx@MYDB>select count(*) from t1 where object_id between :x and :y; COUNT(*) ---------- 2 zx@MYDB>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 1 1 select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1 select count(*) from t1 where object_id between :x and :y 9dhu3xk2zu531 1 1
從上述查詢結果可以看到，Oracle在第一次執行上述等價SQL時也是用的硬解析
從執行計劃看，此時是對索引IDX_T1走的索引范圍掃描，而且Oracle評估出來執行這個索引范圍掃描所返回結果集的Cardinality的值為3。并注意到“Peeked Binds”部分的內容為“1 - :X (NUMBER): 999 2 - :Y (NUMBER): 1000”，這說明Oracle在硬解析上述SQL的過程中確實使用了綁定變量窺探，且做“窺探”這個動作時看到的綁定變量x和y的具體輸入值分別為999和1000。
現在保持x不變，將y修改為60000：
zx@MYDB>exec :y := 60000; PL/SQL procedure successfully completed. zx@MYDB>select count(*) from t1 where object_id between :x and :y; COUNT(*) ---------- 58180
從上述查詢結果可以看出上述SQL對應的VERSION_COUNT的值為1，列EXECUTIONS的值為2，這說明Oracle在第二次執行該SQL時用的是軟解析。
從執行計劃上可以看出，此時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
zx@MYDB>comment on table t1 is 'Test table for Bind Peeking'; Comment created. zx@MYDB>select count(*) from t1 where object_id between :x and :y; COUNT(*) ---------- 58180 zx@MYDB>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 1 1 select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1 select count(*) from t1 where object_id between :x and :y 9dhu3xk2zu531 1 1
從上面的查詢結果可以看到等價SQL對應的列VERSION_COUNT的值為1，列EXECUTIONS的值由之前的2變為了現在的1，說明Oracle在第三次執行該SQL時用的是硬解析(EXECUTIONS的值為1，是因為Oracle在這里重新生成了一對Parent Cursor和Child Cursor，原先EXECUTIONS的值為2所對應的Shared Cursor已經被Oracle標記為invalid，相當于被廢棄了)。
從執行計劃可以看出，現在執行計劃走的是索引快速全掃描，而Oracle評估出來執行這個索引快速全掃描所返回結果集的Cardinality的值為57646。并且“Peeked Binds”部分的內容依然為“1 - :X (NUMBER): 999 2 - :Y (NUMBER): 60000”。說明Oracle在執行上述SQL的過程中確實又一次使用了綁定變量窺探，且做“窺探”這個動作時看到的綁定變量x和y的具體輸入值分別為999和60000。
現在把隱含參數_OPTIM_PEEK_USER_BINDS的值設為FALSE以關閉綁定變量窺探：
zx@MYDB>alter session set "_optim_peek_user_binds"=false; Session altered.
然后保持x的值不變，將y值修改為1000
zx@MYDB>select count(*) from t1 where object_id between :x and :y; COUNT(*) ---------- 2 zx@MYDB>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 1 1 select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1 select count(*) from t1 where object_id between :x and :y 9dhu3xk2zu531 2 2
從上面的查詢結果可以看到等價SQL對應的列VERSION_COUNT和列EXECUTIONS的值均由1變為了現在的2，說明Oracle在第四次執行該SQL時使用硬解析。VERSION_COUNT的值為2，意味著該SQL所在的Parent Cursor下掛了兩個Child Cursor。從如下查詢結果可以看出該SQL確實有兩個Child Cursor：
zx@MYDB>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中。查看執行計劃
從執行計劃可以看出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值：
zx@MYDB>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 1 1 00000000B4D1B130 1143368397 select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1 00000000B4D1AA90 924127028 select count(*) from t1 where object_id between :x and :y 9dhu3xk2zu531 2 2 00000000B4CC4840 2247955553
使用dbms_shared_pool.purge刪除目標SQL的Shared Cursor：
zx@MYDB>exec sys.dbms_shared_pool.purge('00000000B4CC4840,2247955553','c');
PL/SQL procedure successfully completed.
zx@MYDB>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 1 1 00000000B4D1B130 1143368397
select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1 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：
zx@MYDB>exec :y := 60000; PL/SQL procedure successfully completed. zx@MYDB>select count(*) from t1 where object_id between :x and :y; COUNT(*) ---------- 58180 zx@MYDB>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 1 1 select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1 select count(*) from t1 where object_id between :x and :y 9dhu3xk2zu531 2 1 zx@MYDB>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。
這意味著當我們把綁定變量窺探關閉后，無論對綁定變量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。
zx@MYDB>select table_name,num_rows from dba_tables where owner=user and table_name='T1'; TABLE_NAME NUM_ROWS ------------------------------------------------------------------------------------------ ---------- T1 72005 zx@MYDB>select round(72005*0.05*0.05) from dual; ROUND(72005*0.05*0.05) ---------------------- 180
參考：《基于Oracle的SQL優化》
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
