g之前的綁定變量窺視
我們都知道為了能夠讓SQL語句共享執行計劃oracle始終都是強調在進行應用系統的設計時必須使用綁定變量也就是用一個變量來代替原來出現在SQL語句裡的字面值比如對於下面三條SQL語句來說
select col from t where col = ;
select col from t where col = ;
select col from t where col = ;
我們可以看到這三條SQL語句幾乎一樣只有最後where條件裡的字面值（分別是）不同而已但是如果寫成這個樣子則oracle是不知道這三條SQL語句是一樣的仍然把它們當作三條完全不同的SQL語句從而在shared pool裡進行硬解析並生成最終的執行計劃但是我們會發現這三個執行計劃可能都是一樣的因此後面兩次生成執行計劃的工作可能是完全不必要的這在典型的OLTP環境中更是如此由於解析本身屬於CPU密集型操作因此為了降低對CPU的消耗oracle建議將這樣的SQL寫成
select col from t where col = :v;
然後分別將傳遞給v這樣的話只需要第一次傳入時進行解析即可而後面執行時由於SQL文本本身沒有變化因此直接把執行計劃拿來使用即可不需要再次生成執行計劃
但是生成執行計劃本身是基於概率的理論在不訪問具體表裡的數據的前提下根據你的where條件來猜測返回的記錄數大概是多少從而判斷應該采用怎樣的訪問路徑很明顯這是一定要參照具體的where條件裡的值才能進行猜測的這樣就與節省CPU的初衷產生了矛盾因為節省CPU的關鍵是使用綁定變量你一旦使用了綁定變量則oracle豈不是不知道你具體的字面值了嗎？
為了解決這一問題oracle引入了綁定變量窺視所謂綁定變量窺視就是指oracle在第一次解析SQL語句的時候（也就是說該SQL第一次傳入shared pool）會將你輸入的綁定變量的值帶入SQL語句裡從而參考你的字面值來猜測該SQL大概會返回多少條記錄從而得到優化的執行計劃然後以後再次執行相同的SQL語句時不再考慮你所輸入的綁定變量的值直接取出第一次生成的綁定變量
但是很可惜的是使用綁定變量從而共享游標與SQL優化是兩個矛盾的目標Oracle使用綁定變量的前提是oracle認為大部分的列的數據都是分布比較均勻的從而使用第一次的綁定變量的值所得到的執行計劃大多數情況下都能適用於該綁定變量的其他的值很明顯如果第一次傳入的綁定變量的值恰好占整個數據量的百分比較高從而導致全表掃描的執行計劃而後來傳入的綁定變量的值都占整個數據量的百分比都很低則應該走索引掃描會更好的但是由於使用了綁定變量從而oracle並不會再去看你的綁定變量的值而是直接拿全表掃描的執行計劃來用這時由於使用了綁定變量雖然我們達到了游標共享從而節省CPU的目的但是SQL的執行計劃卻不夠優化了
那麼我們如何在綁定變量和SQL優化之間進行取捨呢？在OLTP應用中由於並發性較高CPU上的爭用會比較嚴重同時SQL本身執行時間較短涉及到的數據量較少解析所占的時間在整個SQL執行時間中占的比例較高而花在I/O上的時間占的比例較低因此盡管綁定變量會有SQL不夠優化的問題還是建議使用綁定變量但是在DSS應用和數據倉庫應用中由於並發性較低CPU上的爭用較輕同時SQL語句的執行時間都很長而且主要時間花在等待I/O上而解析占的比重較低這時優化SQL執行計劃的重要性就體現出來了因此建議不要使用綁定變量而直接使用字面值但是大多數的情況都是混合應用既有OLTP又有數據倉庫這時就很難完美的解決該問題了
我們先來看一下g之前的綁定變量窺視是如何工作的以g為例
我們先創建一個表使得其含有的數據分布不均勻並在該表上創建一個索引
hr@orag > create table t as select object_id as idobject_name from dba_objects;
hr@orag > update t set id= where rownum<=;
hr@orag > commit;
hr@orag > create index idx_t on t(id);
這樣該表裡id為的記錄有一萬條而id為其他值的記錄都只有一條從而我們構建出一個分布不均勻的測試用表然後我們收集一下統計信息注意這裡要收集直方圖為的是要讓CBO知道id列上的數據分布不均勻
hr@orag> begin
dbms_statsgather_table_stats(
user
t
cascade => true
method_opt => for columns id size
);
end;
/
我們找到表t裡最大的id然後以該id作為第一個綁定變量傳入可以想象該綁定變量將導致走索引注意我們這裡設定的優化器目標為all_rows
hr@orag > select max(id) from t;
MAX(ID)
hr@orag> alter system flush shared_pool;
hr@orag> var v_id number;
hr@orag> var v_sql_id varchar();
hr@orag> exec :v_id := ;
hr@orag> select * from t where id=:v_id;
此處省略查詢結果
hr@orag > begin
select sql_id into :v_sql_id from v$sql
where sql_text like select * from t where id=:v_id%;
end;
/
hr@orag > select * from table(dbms_xplandisplay_cursor(:v_sql_id));
PLAN_TABLE_OUTPUT
SQL_ID djwqcpbczk child number
select * from t where id=:v_id
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| | SELECT STATEMENT | | | | () |
| | TABLE ACCESS BY INDEX ROWID| T | | | () | ::
|* | INDEX RANGE SCAN | IDX_T | | | () | ::
hr@orag> exec :v_id := ;
hr@orag> select * from t where id=:v_id;
此處省略查詢結果
hr@orag > begin
select sql_id into :v_sql_id from v$sql
where sql_text like select * from t where id=:v_id%;
end;
/
hr@orag > select * from table(dbms_xplandisplay_cursor(:v_sql_id));
PLAN_TABLE_OUTPUT
SQL_ID djwqcpbczk child number
select * from t where id=:v_id
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| | SELECT STATEMENT | | | | () |
| | TABLE ACCESS BY INDEX ROWID| T | | | () | ::
|* | INDEX RANGE SCAN | IDX_T | | | () | ::
從上面結果可以看出在為綁定變量傳入第一個值為時由於返回的記錄條數較少導致走索引掃描當我們第二次傳入綁定變量值時oracle不再生成新的執行計劃而直接拿索引掃描的執行路徑來用
但是如果先傳入的綁定變量值然後再傳入的綁定變量值時會怎樣？我們繼續測試
hr@orag> alter system flush shared_pool;
hr@orag> set autotrace traceonly exp stat;
hr@orag> exec :v_id := ;
hr@orag> select * from t where id=:v_id;
hr@orag > begin
select sql_id into :v_sql_id from v$sql
where sql_text like select * from t where id=:v_id%;
end;
/
hr@orag > select * from table(dbms_xplandisplay_cursor(:v_sql_id));
PLAN_TABLE_OUTPUT
SQL_ID djwqcpbczk child number
select * from t where id=:v_id
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| | SELECT STATEMENT | | | | ()| |
|* | TABLE ACCESS FULL | T | | K | () | :: |
hr@orag > exec :v_id := ;
hr@orag > select * from t where id=:v_id;
hr@orag > begin
select sql_id into :v_sql_id from v$sql
where sql_text like select * from t where id=:v_id%;
end;
/
hr@orag > select * from table(dbms_xplandisplay_cursor(:v_sql_id));
PLAN_TABLE_OUTPUT
SQL_ID djwqcpbczk child number
select * from t where id=:v_id
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| | SELECT STATEMENT | | | | ()| |
|* | TABLE ACCESS FULL | T | | K | () | :: |
很明顯先傳入的綁定變量時將導致生成的執行計劃走全表掃描後面傳入的的綁定變量的最佳執行路徑應該是索引掃描但是由於CBO並不知道這一點而是直接拿第一次生成的執行計劃來用了於是也走全表掃描了
g之後的動態綁定變量窺視
而從g開始這個尴尬的問題開始得到了改善因此從g開始引入了所謂的自適應游標共享（Adaptive Cursor Sharing）該特性是一個非常復雜的技術用來平衡游標共享和SQL優化這兩個矛盾的目標g裡不會盲目的共享游標而是會去查看每個綁定變量並為不同的綁定變量來產生不同的執行計劃而oracle這麼做的前提是使用多個執行計劃的所帶來的收益要比產生多個執行計劃所引起的CPU開銷要更大
使用自適應游標共享時會遵循下面的步驟
) 一條新的SQL語句第一次傳入shared pool時還是和以前一樣進行硬解析而且進行綁定變量窺視計算where條件各個列的selectivity同時如果綁定變量所在的列上存在直方圖的話也會去參考該直方圖來計算selectivity該游標會被標記為是一個綁定敏感的游標（bindsensitive cursor）同時oracle還會保留包含綁定變量的where條件的其他信息比如selectivity等Oracle會為該謂詞的selectivity維持一個范圍oracle叫做立方體（cube）只要傳入的綁定變量所產生的selectivity落在該范圍裡面也就是落在該cube裡面就不產生新的執行計劃而直接拿該cube所對應的執行計劃來用
) 下次再次執行相同的SQL時傳入了新的綁定變量假設使用新的綁定變量的謂詞的selectivity落在已經存在的cube范圍裡於是這次SQL的執行會使用該cube所對應的執行計劃
) 相同的查詢再次執行時假設所使用的新的綁定變量導致這時候的selectivity不再落在已經存在的cube裡了於是也就找不到對應的執行計劃於是系統會進行一個硬解析這將產生第二個新的執行計劃而且新的selectivity以及對應的cube也會保存下來也就是說這時我們分別有兩個cube以及兩個執行計劃
) 相同的查詢再次執行時假設所使用的新的綁定變量導致這時候的selectivity不落在現存的兩個cube中的任何一個所以系統又會進行硬解析假設這時硬解析所產生的執行計劃與第一次產生執行計劃一樣也就是說在第一次評估selectivity的cube時過於保守導致cube過小進而導致了這一次的不必要的硬解析於是oracle會將第一次產生的cube與這次產生的cube合並成一個新的更大的cube那麼下次再次進行軟解析的時候如果selectivity落在新的cube裡則會使用第一次所產生的執行計劃
我們從這裡可以看到g對這個問題的處理非常精彩這樣做的結果是系統開始運行時CPU消耗可能會比較嚴重但是隨著系統不斷運行cube的不斷合並從而不斷擴大於是系統的CPU消耗會不斷下降同時執行計劃也會更加的合理
我們來做個試驗進行驗證我們采用g新引入的執行計劃管理特性來驗證該特性
與g中的測試一樣創建一個數據分布不均勻的表在數據分布不均勻的列上創建索引並收集統計信息收集時注意要收集直方圖從而讓CBO知道該列上的數據分布不均勻
hr@orag > create table t as select object_id as idobject_name from dba_objects;
hr@orag > select count(*) from t;
COUNT(*)
hr@orag > update t set id= where rownum<=;
hr@orag > commit;
hr@orag > create index idx_t on t(id);
hr@orag > begin
dbms_statsgather_table_stats(
user
t
cascade => true
method_opt => for columns id size
);
end;
/
我們找到表t裡最大的id然後以該id作為第一個綁定變量傳入可以想象該綁定變量將導致走索引
hr@orag > select max(id) from t;
MAX(ID)
我們將optimizer_capture_plan_baselines設置為true從而讓oracle自動獲取plan baseline
hr@orag > alter system set OPTIMIZER_CAPTURE_PLAN_BASELINES=true;
hr@orag > alter system flush shared_pool;
hr@orag > var v_id number;
hr@orag > exec :v_id := ;
hr@orag > select * from t where id=:v_id;
hr@orag > select * from t where id=:v_id;
我們運行兩遍select * from t where id=:v_id從而讓oracle捕獲plan baseline我們知道id為的記錄只有一條因此該SQL應該使用索引掃描然後我們再為綁定變量傳入我們知道id為的記錄有一萬條所以較好的執行計劃不應該走已經生成的執行計劃而應該走全表掃描
hr@orag > exec :v_id := ;
hr@orag > set autotrace traceonly stat;
之所以設置stat是為了讓該sql實際執行但不要返回所有記錄
hr@orag > select * from t where id=:v_id;
hr@orag > select sql_handleplan_nameoriginenabledaccepted
from dba_sql_plan_baselines where sql_text like select * from t%;
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC
SYS_SQL_eabbedffc SYS_SQL_PLAN_ffccba AUTOCAPTURE YES YES
SYS_SQL_eabbedffc SYS_SQL_PLAN_ffcdbdee AUTOCAPTURE YES NO
我們可以發現現在該SQL語句存在兩個執行計劃了其中第一個執行計劃也就是accepted為YES的執行計劃為v_id等於得到的而第二個執行計劃也就是accepted為NO的是由v_id等於得到的第二個執行計劃還沒有被加入plan baseline所以優化器不會使用該執行計劃我們將第二個執行計劃的accepted改為YES從而讓oracle考慮使用該計劃
hr@orag > var cnt number;
hr@orag > begin
:cnt := dbms_spmalter_sql_plan_baseline(
sql_handle => SYS_SQL_eabbedffc
plan_name => SYS_SQL_PLAN_ffcdbdee
attribute_name => ACCEPTED attribute_value => YES);
end;
/
我們來看一下這兩個執行計劃分別是怎樣的
注意在這裡我們要驗證oracle會為不同綁定變量生成不同的執行計劃時不能使用set autotrace traceonly exp stat等其他方式因為set autotrace得出的執行計劃始終都是第一次生成的執行計劃我們通過plan baseline從側面來驗證它當然我們也可以通過設置sql_trace=true從而將執行計劃轉儲出來進行驗證
SQL> select * from table(dbms_xplandisplay_sql_plan_baseline
(SYS_SQL_eabbedffcSYS_SQL_PLAN_ffccba));
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| | SELECT STATEMENT | | | | ()| :: |
| | TABLE ACCESS BY INDEX ROWID| T | | | ()| :: |
|* | INDEX RANGE SCAN | IDX_T | | | ()| :: |
SQL> select * from table(dbms_xplandisplay_sql_plan_baseline
(SYS_SQL_eabbedffcSYS_SQL_PLAN_ffcdbdee));
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| | SELECT STATEMENT | | | | () | :: |
|* | TABLE ACCESS FULL | T | | | () | :: |
很明顯第一個是索引掃描第二個是全表掃描同樣我們來看一下v$sql裡該sql語句有幾條記錄
hr@orag > select sql_textsql_idchild_numberplan_hash_value
from v$sql where sql_text like select * from t where%;
SQL_TEXT SQL_ID CHILD_NUMBER PLAN_HASH_VALUE
select * from t where id=:v_id yttxyhasg
可以看到該SQL語句目前在內存裡只存在一個執行計劃其plan hash value就等於我們在前面plan baseline裡看到的第一個走索引的執行計劃的hash value我們把該執行計劃顯示出來進行確認
hr@orag > select * from table(dbms_xplandisplay_cursor(yttxyhasg));
PLAN_TABLE_OUTPUT
SQL_ID yttxyhasg child number
select * from t where id=:v_id
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| | SELECT STATEMENT | | | | () | |
| | TABLE ACCESS BY INDEX ROWID| T | | | () | :: |
|* | INDEX RANGE SCAN | IDX_T | | | () | :: |
結果很明顯正是走索引的執行計劃然後我們繼續為幫定變量傳入多執行幾次
hr@orag > exec :v_id := ;
hr@orag > set autotrace traceonly stat;
hr@orag > select * from t where id=:v_id;
hr@orag > select * from t where id=:v_id;
hr@orag > select * from t where id=:v_id;
注意這裡我們之所以要多執行幾次主要是因為如果只是執行一次或兩次oracle能夠認識到你傳入的綁定變量落在了第一次的綁定變量（）所在的cube之外但是oracle認為你可能只是偶爾執行該綁定變量所以並不一定會使用另外那個全表掃描的執行計劃多執行幾次以後你會發現consistent gets突然從直線下降到了這時就說明oracle開始使用新的全表掃描的執行計劃了
然後這時我們再去查看v$sql裡該sql語句有幾條記錄
hr@orag > select sql_textsql_idchild_numberplan_hash_value
from v$sql where sql_text like select * from t where%;
SQL_TEXT SQL_ID CHILD_NUMBER PLAN_HASH_VALUE
select * from t where id=:v_id yttxyhasg
select * from t where id=:v_id yttxyhasg
我們發現該SQL語句在內存裡存在兩條記錄了也就是存在兩個子游標了分別對應了不同的執行計劃同樣我們來看一下新產生的子游標也就是child_number為的執行計劃是怎樣的
SQL> select * from table(dbms_xplandisplay_cursor(yttxyhasg));
PLAN_TABLE_OUTPUT
SQL_ID yttxyhasg child number
select * from t where id=:v_id
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| | SELECT STATEMENT | | | | () | |
|* | TABLE ACCESS FULL| T | | K | () | :: |
我們還可以從另外的角度來驗證g裡的動態綁定變量窺視也就是設置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得到的文件內容如下所示
SQL ID : yttxyhasg
select *
from
t where id=:v_id
Rows Row Source Operation
TABLE ACCESS BY INDEX ROWID T (cr= pr= pw= time= us cost= size= card=)
INDEX RANGE SCAN IDX_T (cr= pr= pw= time= us cost= size= card=)(object id )
SQL ID : yttxyhasg
select *
from
t where id=:v_id
Rows Row Source Operation
TABLE ACCESS FULL T (cr= pr= pw= time= us cost= size= card=)
從這裡也可以很清楚的看到對於不同的綁定變量oracle能夠自行選擇是否應該生成更好的執行計劃並使用該執行計劃
From:http://tw.wingwit.com/Article/program/Oracle/201311/16590.html
