Oracle11g新特性之动态变量窥视
??? 我們都知道，為了可以讓SQL語句共享運行計劃，oracle始終都是強調在進行應用系統的設計時，必須使用綁定變量，也就是用一個變量來取代原來出如今SQL語句里的字面值。比方，對于以下三條SQL語句來說：
select col1 from t where col2 = 1;
select col1 from t where col2 = 2;
select col1 from t where col2 = 3;
??? 我們能夠看到，這三條SQL語句差點兒一樣，僅僅有最后where條件里的字面值（各自是1、2、3）不同而已?？墒羌僭O寫成這個樣子，則oracle是不知道這三條SQL語句是一樣的，仍然把它們當作三條全然不同的SQL語句，從而在shared pool里進行硬解析，并生成終于的運行計劃?？墒俏覀儠l現，這三個運行計劃可能都是一樣的，因此后面兩次生成運行計劃的工作可能是全然不必要的，這在典型的OLTP環境中更是如此。因為解析本身屬于CPU密集型操作，因此為了減少對CPU的消耗，oracle建議將這種SQL寫成：
select col1 from t where col2 = :v1;
??? 然后，分別將1、2、3傳遞給v1，這種話，僅僅須要第一次傳入1時進行解析就可以。而后面運行2、3時，因為SQL文本本身沒有變化，因此直接把運行計劃拿來使用就可以，不須要再次生成運行計劃。
??? 可是，生成運行計劃本身是基于概率的理論，在不訪問詳細表里的數據的前提下，依據你的where條件，來推測返回的記錄數大概是多少，從而推斷應該採用如何的訪問路徑。非常明顯，這是一定要參照詳細的where條件里的值才干進行推測的。這樣就與節省CPU的初衷產生了矛盾，由于節省CPU的關鍵是使用綁定變量，你一旦使用了綁定變量，則oracle豈不是不知道你詳細的字面值了嗎？
??? 為了解決這一問題，oracle引入了綁定變量窺視。所謂綁定變量窺視，就是指oracle在第一次解析SQL語句的時候（也就是說該SQL第一次傳入shared pool），會將你輸入的綁定變量的值帶入SQL語句里，從而參考你的字面值來推測該SQL大概會返回多少條記錄，從而得到優化的運行計劃。然后，以后再次運行同樣的SQL語句時，不再考慮你所輸入的綁定變量的值，直接取出第一次生成的綁定變量。
??? 可是，非?？上У氖?#xff0c;使用綁定變量從而共享游標與SQL優化是兩個矛盾的目標。Oracle使用綁定變量的前提，是oracle覺得大部分的列的數據都是分布比較均勻的。從而，使用第一次的綁定變量的值所得到的運行計劃，大多數情況下都能適用于該綁定變量的其它的值。非常明顯，假設第一次傳入的綁定變量的值恰好占整個數據量的百分比較高，從而導致全表掃描的運行計劃。而后來傳入的綁定變量的值都占整個數據量的百分比都非常低，則應該走索引掃描會更好的，可是因為使用了綁定變量，從而oracle并不會再去看你的綁定變量的值，而是直接拿全表掃描的運行計劃來用。這時，因為使用了綁定變量，盡管我們達到了游標共享，從而節省CPU的目的，可是SQL的運行計劃卻不夠優化了。
??? 那么我們怎樣在綁定變量和SQL優化之間進行取舍呢？在OLTP應用中，因為并發性較高，CPU上的爭用會比較嚴重，同一時候SQL本身運行時間較短，涉及到的數據量較少，解析所占的時間在整個SQL運行時間中占的比例較高，而花在I/O上的時間占的比例較低。因此雖然綁定變量會有SQL不夠優化的問題，還是建議使用綁定變量?？墒窃贒SS應用和數據倉庫應用中，因為并發性較低，CPU上的爭用較輕，同一時候SQL語句的運行時間都非常長，并且主要時間花在等待I/O上，而解析占的比重較低，這時優化SQL運行計劃的重要性就體現出來了。因此，建議不要使用綁定變量，而直接使用字面值?？墒谴蠖鄶档那闆r都是混合應用，既有OLTP又有數據倉庫，這時就非常難完美的解決該問題了。
??? 我們先來看一下11g之前的綁定變量窺視是怎樣工作的，以10g為例。
我們先創建一個表，使得其含有的數據分布不均勻，并在該表上創建一個索引。?
hr@ora10g > create table t1 as select object_id as id,object_name from dba_objects; hr@ora10g > update t1 set id=1 where rownum<=10000; hr@ora10g > commit; hr@ora10g > create index idx_t1 on t1(id);
??? 這樣，該表里id為的1記錄有一萬條，而id為其它值的記錄都僅僅有一條。從而，我們構建出一個分布不均勻的測試用表。然后，我們收集一下統計信息。注意，這里要收集直方圖，為的是要讓CBO知道id列上的數據分布不均勻。
hr@ora10g> begin 2 dbms_stats.gather_table_stats( 3 user, 4 't1', 5 cascade => true, 6 method_opt => 'for columns id size 254' 7 ); 8 end; 9 /??? 我們找到表t1里最大的id，然后以該id作為第一個綁定變量傳入，能夠想象，該綁定變量將導致走索引。注意，我們這里設定的優化器目標為all_rows。
hr@ora11g > select max(id) from t1; MAX(ID) ---------- 13871 hr@ora10g> alter system flush shared_pool; hr@ora10g> var v_id number; hr@ora10g> var v_sql_id varchar2(20); hr@ora10g> exec :v_id := 13871; hr@ora10g> select * from t1 where id=:v_id; 此處省略查詢結果 hr@ora10g > begin 2 select sql_id into :v_sql_id from v$sql 3 where sql_text like 'select * from t1 where id=:v_id%'; 4 end; 5 / hr@ora10g > select * from table(dbms_xplan.display_cursor(:v_sql_id)); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID djwq30cpbcz7k, child number 0 ------------------------------------- select * from t1 where id=:v_id Plan hash value: 50753647 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 11 (100) | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1365 | 28665| 11 (0) | 00:00:01 |* 2 | INDEX RANGE SCAN | IDX_T1 | 1365 | | 3 (0) | 00:00:01 -------------------------------------------------------------------------------- ...... hr@ora10g> exec :v_id := 1; hr@ora10g> select * from t1 where id=:v_id; 此處省略查詢結果 hr@ora10g > begin 2 select sql_id into :v_sql_id from v$sql 3 where sql_text like 'select * from t1 where id=:v_id%'; 4 end; 5 / hr@ora10g > select * from table(dbms_xplan.display_cursor(:v_sql_id)); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID djwq30cpbcz7k, child number 0 ------------------------------------- select * from t1 where id=:v_id Plan hash value: 50753647 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 11 (100) | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1365 | 28665| 11 (0) | 00:00:01 |* 2 | INDEX RANGE SCAN | IDX_T1 | 1365 | | 3 (0) | 00:00:01 --------------------------------------------------------------------------------??? 從上面結果能夠看出，在為綁定變量傳入第一個值為13871時，因為返回的記錄條數較少，導致走索引掃描。當我們第二次傳入綁定變量值1時，oracle不再生成新的運行計劃，而直接拿索引掃描的運行路徑來用。
??? 可是，假設先傳入1的綁定變量值，然后再傳入13871的綁定變量值時，會如何？我們繼續測試。
??? 非常明顯，先傳入1的綁定變量時將導致生成的運行計劃走全表掃描。后面傳入的13871的綁定變量的最佳運行路徑應該是索引掃描，可是因為CBO并不知道這一點，而是直接拿第一次生成的運行計劃來用了，于是也走全表掃描了。
2. 11g之后的動態綁定變量窺視
??? 而從11g開始，這個尷尬的問題開始得到了改善。因此從11g開始，引入了所謂的自適應游標共享（Adaptive Cursor Sharing）。該特性是一個很復雜的技術，用來平衡游標共享和SQL優化這兩個矛盾的目標。11g里不會盲目的共享游標，而是會去查看每一個綁定變量，并為不同的綁定變量來產生不同的運行計劃。而oracle這么做的前提是，使用多個運行計劃的所帶來的收益，要比產生多個運行計劃所引起的CPU開銷要更大。
使用自適應游標共享時，會遵循以下的步驟：
??? 1) 一條新的SQL語句第一次傳入shared pool時，還是和曾經一樣，進行硬解析。并且進行綁定變量窺視，計算where條件各個列的selectivity，同一時候假設綁定變量所在的列上存在直方圖的話，也會去參考該直方圖來計算selectivity。該游標會被標記為是一個綁定敏感的游標（bind-sensitive cursor）。同一時候，oracle還會保留包括綁定變量的where條件的其它信息，比方selectivity等。Oracle會為該謂詞的selectivity維持一個范圍，oracle叫做立方體（cube）。僅僅要傳入的綁定變量所產生的selectivity落在該范圍里面，也就是落在該cube里面，就不產生新的運行計劃，而直接拿該cube所相應的運行計劃來用。
??? 2) 下次再次運行同樣的SQL時，傳入了新的綁定變量，如果使用新的綁定變量的謂詞的selectivity落在已經存在的cube范圍里，于是這次SQL的運行會使用該cube所相應的運行計劃。
??? 3) 同樣的查詢再次運行時，如果所使用的新的綁定變量導致這時候的selectivity不再落在已經存在的cube里了，于是也就找不到相應的運行計劃。于是系統會進行一個硬解析，這將產生第二個新的運行計劃。并且新的selectivity以及相應的cube也會保存下來。也就是說，這時，我們分別有兩個cube以及兩個運行計劃。
??? 4) 同樣的查詢再次運行時，如果所使用的新的綁定變量導致這時候的selectivity不落在現存的兩個cube中的不論什么一個，所以系統又會進行硬解析。如果這時硬解析所產生的運行計劃與第一次產生運行計劃一樣，也就是說，在第一次評估selectivity的cube時過于保守，導致cube過小，進而導致了這一次的不必要的硬解析。于是，oracle會將第一次產生的cube與這次產生的cube合并成一個新的更大的cube。那么，下次再次進行軟解析的時候，如果selectivity落在新的cube里，則會使用第一次所產生的運行計劃。
??? 我們從這里能夠看到，11g對這個問題的處理很精彩。這樣做的結果是，系統開始執行時，CPU消耗可能會比較嚴重，可是隨著系統不斷執行，cube的不斷合并從而不斷擴大，于是系統的CPU消耗會不斷下降，同一時候執行計劃也會更加的合理。
我們來做個試驗進行驗證。我們採用11g新引入的運行計劃管理特性來驗證該特性。
???? 與10g中的測試一樣，創建一個數據分布不均勻的表，在數據分布不均勻的列上創建索引，并收集統計信息，收集時注意要收集直方圖，從而讓CBO知道該列上的數據分布不均勻。
我們找到表t1里最大的id，然后以該id作為第一個綁定變量傳入，能夠想象，該綁定變量將導致走索引。
hr@ora11g > select max(id) from t1; MAX(ID) ---------- 12462 我們將optimizer_capture_plan_baselines設置為true，從而讓oracle自己主動獲取plan baseline。 hr@ora11g > alter system set OPTIMIZER_CAPTURE_PLAN_BASELINES=true; hr@ora11g > alter system flush shared_pool; hr@ora11g > var v_id number; hr@ora11g > exec :v_id := 12462; hr@ora11g > select * from t1 where id=:v_id; hr@ora11g > select * from t1 where id=:v_id;??? 我們運行兩遍select * from t1 where id=:v_id，從而讓oracle捕獲plan baseline。我們知道id為12462的記錄僅僅有一條，因此該SQL應該使用索引掃描。然后我們再為綁定變量傳入1，我們知道id為1的記錄有一萬條，所以較好的運行計劃不應該走已經生成的運行計劃，而應該走全表掃描。
hr@ora11g > exec :v_id := 1; hr@ora11g > set autotrace traceonly stat; --之所以設置stat是為了讓該sql實際運行，但不要返回全部記錄， hr@ora11g > select * from t1 where id=:v_id; hr@ora11g > select sql_handle,plan_name,origin,enabled,accepted 2 from dba_sql_plan_baselines where sql_text like 'select * from t1%'; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC ----------------------- ----------------------------- -------------- --- --- SYS_SQL_ea05bbed6f2f670c SYS_SQL_PLAN_6f2f670c844cb98a AUTO-CAPTURE YES YES SYS_SQL_ea05bbed6f2f670c SYS_SQL_PLAN_6f2f670cdbd90e8e AUTO-CAPTURE YES NO????? 我們能夠發現，如今該SQL語句存在兩個運行計劃了，當中第一個運行計劃，也就是accepted為YES的運行計劃為v_id等于12462得到的，而第二個運行計劃，也就是accepted為NO的是由v_id等于1得到的。第二個運行計劃還沒有被增加plan baseline，所以優化器不會使用該運行計劃。我們將第二個運行計劃的accepted改為YES，從而讓oracle考慮使用該計劃。
hr@ora11g > var cnt number; hr@ora11g > begin 2 :cnt := dbms_spm.alter_sql_plan_baseline( 3 sql_handle => 'SYS_SQL_ea05bbed6f2f670c', 4 plan_name => 'SYS_SQL_PLAN_6f2f670cdbd90e8e', 5 attribute_name => 'ACCEPTED', attribute_value => 'YES'); 6 end; 7 /我們來看一下這兩個運行計劃各自是如何的。
注意：在這里我們要驗證oracle會為不同綁定變量生成不同的運行計劃時，不能使用set autotrace traceonly exp stat等其它方式。由于set autotrace得出的運行計劃始終都是第一次生成的運行計劃。我們通過plan baseline從側面來驗證它。當然，我們也能夠通過設置sql_trace=true從而將運行計劃轉儲出來進行驗證。????
非常明顯，第一個是索引掃描，第二個是全表掃描。相同，我們來看一下v$sql里該sql語句有幾條記錄。
??? 能夠看到，該SQL語句眼下在內存里僅僅存在一個運行計劃，其plan hash value就等于我們在前面plan baseline里看到的第一個走索引的運行計劃的hash value。我們把該運行計劃顯示出來進行確認。??
hr@ora11g > select * from table(dbms_xplan.display_cursor('7y7tt6xyhas1g',0)); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 7y7tt6xyhas1g, child number 0 ------------------------------------- select * from t1 where id=:v_id Plan hash value: 50753647 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | | 2 (100) | | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 21 | 2 (0) | 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T1 | 1 | | 1 (0) | 00:00:01 | ......結果非常明顯，正是走索引的運行計劃。然后我們繼續為幫定變量傳入1，多運行幾次。
hr@ora11g > exec :v_id := 1;
hr@ora11g > set autotrace traceonly stat;
hr@ora11g > select * from t1 where id=:v_id;
hr@ora11g > select * from t1 where id=:v_id;
hr@ora11g > select * from t1 where id=:v_id;
?? 注意：這里，我們之所以要多運行幾次，主要是由于假設僅僅是運行一次或兩次，oracle可以認識到你傳入的綁定變量落在了第一次的綁定變量（12462）所在的cube之外，可是oracle覺得你可能僅僅是偶爾運行該綁定變量，所以并不一定會使用另外那個全表掃描的運行計劃。多運行幾次以后，你會發現consistent gets突然從1390直線下降到了715，這時就說明oracle開始使用新的全表掃描的運行計劃了。
然后，這時我們再去查看v$sql里該sql語句有幾條記錄。??
??? 我們發現，該SQL語句在內存里存在兩條記錄了，也就是存在兩個子游標了，分別相應了不同的運行計劃。相同，我們來看一下新產生的子游標，也就是child_number為1的運行計劃是如何的。
SQL> select * from table(dbms_xplan.display_cursor('7y7tt6xyhas1g',1));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7y7tt6xyhas1g, child number 1
-------------------------------------
select * from t1 where id=:v_id
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 16 (100) | |
|* 1 | TABLE ACCESS FULL| T1 | 9974 | 204K | 16 (0) | 00:00:01 |
......
我們還能夠從另外的角度來驗證11g里的動態綁定變量窺視，也就是設置sql_trace的方式。這個方式比較簡單，僅僅要先發出：alter session set sql_trace=true以后，傳入兩個不同的綁定變量，然后分別就不同的綁定變量多運行幾次。最后調用tkprof對跟蹤文件進行分析。這里注意兩個地方，第一是跟蹤文件位于ADR中，不再位于user_dump_dest參數所指定的文件夾里了。就這里的跟蹤文件而言，其所在位置缺省為：$ORACLE_HOME/diag/rdbms/<DB name>/<SID>/trace文件夾下；第二個要注意的是使用tkprof時，加入aggregate=no選項，缺省會將同樣SQL語句合并，這樣你就發現不到對于同樣SQL語句的不同的運行計劃了。
這里節選部分使用tkprof得到的文件內容，例如以下所看到的。?
? 從這里也可以非常清楚的看到，對于不同的綁定變量，oracle可以自行選擇是否應該生成更好的運行計劃并使用該運行計劃。?
來自網絡：http://tech.it168.com/db/2007-09-24/200709241709921_1.shtml
總結
以上是生活随笔為你收集整理的Oracle11g新特性之动态变量窥视的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Exchange2013学习笔记（4）-
- 下一篇： 色彩的定义