SQL Plan Management(SPM)

在11g，oracle提供dbms_spm包來管理SQL Plan，SPM是一個預防機制，它記錄並評估sql的執行計劃，將已知的高效的sql執行計劃建立為SQL Plan Baselines，SQL Plan Baseline的功能是保持SQL的效能而不必關注系統的改變。

1 Capturing SQL Plan Baselines
在SQL Plan BaseLines捕獲階段，Oracle記錄SQL的執行計劃並檢測該執行計劃是否已經改變，如果SQL改變後的執行計劃是安全的，則SQL就使用新的執行計劃，因此，Oracle維護單個SQL執行計劃的歷史資訊，Oracle維護的SQL執行計劃的歷史僅僅針對重複執行的SQL，SQL Plan Baseline可以手工load，也可以設定為自動捕獲。
A Automatic Plan Capture
如果要啟用自動的SQL Plan Capture，則需要設定OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES，該引數預設為False，如果設定為True，則表示海底撈月活自動捕獲SQL Plan，則系統會自動建立並維護SQL Plan History，SQL Plan History包括優化器關注的：比如an execution plan, SQL text, outline, bind variables, and compilation environment。
B Manual Plan Loading
也可以手動裝載一個存在的SQL Plan作為SQL Plan Baseline，手動裝裝的SQL Plan並不校驗它的效能：
--從SQL Tuning Set中裝載:
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'tset1');
END;
/
--從Cursor Cache中裝載
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '7qqnad1j615m7');
END;
/
2 Selecting SQL Plan Baselines
在SQL Plan選擇階段，SQL每一次編繹，優化器使用基於成本的方式，建立一下best-cost的執行計劃，然後去匹配SQL Plan Baselines中的SQL Plan，如果找到了匹配的SQL Plan，則會使用這個執行計劃，如果沒有找到匹配的SQL Plan,優化器就會去SQL Plan History中去搜尋成本最低的SQL Plan，如果優化器在SQL Plan History中找不到任務匹配的SQL Plan，則該SQL Plan被作為一個Non-Accept Plan被存入SQL Plan History，新的SQL Plan直到它被驗證不會引起一下效能問題才會被使用。
--如何啟用使用SQL Plan Baselins
SQL> alter system set OPTIMIZER_USE_SQL_PLAN_BASELINES = true;
3 Evolving SQL Plan Baselines
在SQL Plan Baselines的演變階段，Oracle評估新的Plan的效能並將效能較好的Plan存放SQL Plan Baselines中，可以使用dbms_spm package的過程EVOLVE_SQL_PLAN_BASELINE將新的SQL Plan存入已經存在的SQL Plan Baselines中，新的Plan將會作為已經Accept Plan加入到SQL Plan Baselines中。
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => 'SYS_SQL_593bc74fca8e6738');
DBMS_OUTPUT.PUT_LINE(report);
END;
/
4 相關的資料字典
dba_sqlset_plans
dba_advisor_sqlplans
dba_sql_plan_baselines
5 用dbms_xplan顯示SQL Plan
在11g中，Oracle增強了dbms_xplan包的顯示功能，不僅可以顯示單個sql的執行計劃，還可以用來顯示sql tuning set，以及sql plan baselines等，在此就僅僅對顯示sql plan baselines做測試：
select *
from table(dbms_xplan.display_sql_plan_baseline(sql_handle => '&SYS_SQL_',
format => 'basic'));

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24570973/viewspace-751591/，如需轉載，請註明出處，否則將追究法律責任。