基於成本的最佳化法則基於統計資訊找到最優執行計劃,但是一些環境的變化可能影響執行計劃的改變如：

· New optimizer version

· Changes to optimizer statistics and optimizer parameters

· Changes to schema and metadata definitions

· Changes to system settings

· SQL profile creating

執行計劃的變化有兩個方向，更好的效能和更差的效能, 11g以前為了保證執行計劃的穩定性透過Stored Outlines和鎖定統計資訊,但是這樣遮蔽了最佳化的一些新的特性和更優的執行計劃,11g SPM很好的解決了始終保證執行計劃朝著更好的效能發展。SPM自動管理執行計劃，當一個新的執行計劃被發現,不是立即被使用,而是被確保被驗證該計劃比歷史執行計劃有著更好的效能才被使用。

1. SQL Plan Baselines 捕獲

自動捕獲

設定引數OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 為true

被開啟後系統自動建立並維護執行計劃歷史記錄，執行計劃歷史包含sql語句,繫結變數,編譯環境等,第一次生成的執行計劃被標記為accepted狀態,代表執行計劃歷史和sql plan baseline。後來生成的執行計劃被加入執行計劃歷史被標記為non-accepted狀態,直到被驗證不會帶來效能下降。

手動捕獲

從SQL Tuning Sets 和 AWR Snapshots載入

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 => '99twu5t2dn5xd');

END;

/

2. SQL Plan Baselines選擇

1 OPTIMIZER_USE_SQL_PLAN_BASELINES 設定為 TRUE

2 在sql編譯過程中,最佳化器基於成本的最佳化法則首先構建一個最優執行計劃，然後去sql plan baseline找到匹配的計劃,如果能找到就使用該執行計劃,如果找不到最佳化器將要評估sql plan baseline中狀態為accepted狀態的計劃，找到一個成本最低的來執行,並新生成的執行計劃加入到sql plan baseline中標記為non-accepted狀態，不能被使用,直到被驗證不會引起效能下降後被標記為accepted後才可以使用。

3. SQL Plan Baselines演化

Sql plan從non-accepted到accepted的過程,手動演化的語句如下:

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;

/

12c引入了SYS_AUTO_SPM_EVOLVE_TASK自動作業verification non-accepted狀態的sql plan

4. SQL Plan Baselines 與 the SQL Tuning Advisor

當與SQL Tuning Advisor最佳化SQL語句，如果找到一個調整計劃，並驗證其效能要比從相應的SQL plan baseline選擇了一個更好的計劃，它使一個建議，接受SQL profile。當SQL profile被接受，調整計劃被新增到相應的SQL plan baseline。

5. 檢視SQL Plan Baselines

select sql_handle, sql_text, plan_name, origin,

enabled, accepted, fixed, autopurge

from dba_sql_plan_baselines;

select * from table(

dbms_xplan.display_sql_plan_baseline(

sql_handle=>'SYS_SQL_209d10fabbedc741',

format=>'basic'));

6. Database upgrade與SQL Plan Management

方式一

使用SQL Tuning Sets

可以選擇(cursor cache, workload repository或者其他SQL Tuning Sets)建立一個新的SQL Tuning Sets

BEGIN

SYS.DBMS_SQLTUNE.CREATE_SQLSET (

sqlset_name => 'SPM_STS',

description => '10g plans');

END;

\

從cursor cache填充SQL Tuning Sets

DECLARE

stscur dbms_sqltune.sqlset_cursor;

BEGIN

OPEN stscur FOR

SELECT VALUE(P)

FROM TABLE(dbms_sqltune.select_cursor_cache(

‘parsing_schema_name <> ‘‘SYS’’’,

null, null, null, null, 1, null, 'ALL')) P;

-- populate the sqlset

dbms_sqltune.load_sqlset(sqlset_name => 'SPM_STS',

populate_cursor => stscur);

END;

/

載入到SPM

SQL> Variable cnt number

SQL> execute :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -

sqlset_name => 'SPM_STS');

方式二

從 Stored Outlines匯入SPM

1 alter system set CREATE_STORED_OUTLINES=OLDPLAN;

2 執行應用sql或者開啟應用

3 alter system set CREATE_STORED_OUTLINES=false;

4 匯出exp outln/outln file=soutline.dmp owner=outln rows=y

5 匯入SPM

variable report clob;

exec :report:=DBMS_SPM.MIGRATE_STORED_OUTLINE( -

attribute_name=>'OUTLINE_NAME', attribute_value =>

'OLDPLAN');

方式三

如果升級前沒有捕獲執行計劃,可以選擇此方式

首先設定引數OPTIMIZER_FEATURES_ENABLE到10g,然後捕獲10g sql plan,然後再載入到SPM,最後再設定OPTIMIZER_FEATURES_ENABLE為11g

注意:捕獲的執行計劃需要使用10g的統計資訊詳細流程如下圖

7. SQL 管理庫策略

Sql plan base 相關資訊儲存在SQL Management Base，是資料庫字典的一部分,儲存在sysaux表空間，預設最大限制是sysaux表空間的10%可以透過以下方式更改限制值

BEGIN

DBMS_SPM.CONFIGURE(

'space_budget_percent',30);

END;

/

也可以設定沒有使用的sql plan保留時間,預設是53周，plan_retention_weeks的範圍值是5-523

可以透過以下方式進行調整

BEGIN

DBMS_SPM.CONFIGURE(

'plan_retention_weeks',105);

END;

/

配置引數查詢

select parameter_name, parameter_value from dba_sql_management_config;

PARAMETER_NAME PARAMETER_VALUE

------------------------------ ---------------

SPACE_BUDGET_PERCENT 30

PLAN_RETENTION_WEEKS 105

SQL Plan Management介紹

1. SQL Plan Baselines 捕獲

2. SQL Plan Baselines選擇

3. SQL Plan Baselines演化

4. SQL Plan Baselines 與 the SQL Tuning Advisor

5. 檢視SQL Plan Baselines

6. Database upgrade與SQL Plan Management

7. SQL 管理庫策略

相關文章