Oracle 11g 新特性 -- SQL Plan Management 示例

Oracle 11g 新特性 --SQL Plan Management 說明

http://blog.csdn.net/tianlesoftware/article/details/8292410

官網說明：

Using SQL Plan Management

http://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm

一. SPM 說明

與Oracle 9i 的outline和10g 的profile比，Oracle 11g的SPM相對更加的靈活。如，一條帶有繫結變數的SQL語句，最好的執行計劃會根據繫結變數的值而不同，11g以前的方法都無法解決這個問題。在11g中，與adaptive cursor sharing配合，SPM允許你同時接受多個執行計劃。執行時，根據不同的變數值，SPM會花費很少的運算從中選擇一條最合適的。

Oracle 11g 新特性 -- 自適應遊標共享(Adaptive Cursor Sharing： ACS) 說明

http://blog.csdn.net/tianlesoftware/article/details/7573502

SPM 相關的語句日誌、計劃歷史記錄和計劃基線都儲存在SQL 管理庫(SMB) 中，該庫還包含SQL概要檔案。SMB 是資料庫字典的一部分，儲存在SYSAUX 表空間中。預設情況下，SMB 的空間預算限制被設定為SYSAUX 大小的10%。但是，可以使用DBMS_SPM.CONFIGURE 過程配置SMB，將空間預算更改為介於1% 和50%之間的一個值。

如果SMB 空間超過了定義的百分比限制，則會向預警日誌中寫入警告。通過清除一些SQL管理物件（如SQL 計劃基線或SQL概要檔案）來增加SMB 空間限制、增加SYSAUX 大小或者減小SMB 大小之前，將按周生成警報。

SPM相關引數:

optimizer_capture_sql_plan_baselines
optimizer_use_sql_plan_baselines
create_stored_outline
use_stored_outlines

注意事項：

（1）使用多種方式控制執行計劃時：

+ Stored Outline（9i）存在時，它具有最高的優先順序。
+ 已經實施的SQL profile（10g）會被自動加入到SQL plan baseline中
+ STA(SQL Tuning Advisor) 會自動接收新的profile，意味著它會生成新的baseline。
（2）如果可能話，儘量移植到SPM，混合多種方式會變得複雜。

1.1 相關名詞說明

SQL Plan Management（SPM）：oracle11g 中提供的新特性，用來更好地控制執行計劃。
Plan History：優化器生成的所有執行計劃的總稱。
SQL Plan Baseline： Plan History裡那些被標記為“ACCEPTED”的執行計劃的總稱。
Plan Evolution：把一條執行計劃從Plan History裡標記為“ACCEPTED”的過程。
SQL Management Base（SMB）：字典表裡儲存的執行計劃的總稱，包括Plan History，SQL Plan Baseline和SQL profile。

1.2 SPM的特點

1.2.1 與profile和outline相比，更加靈活的控制手段

（1）可以有很多的計劃被儲存下來，只有"ENABLED"並且"ACCEPTED"的執行計劃才可以被選擇。
（2）允許有多個"ACCEPTED"的執行計劃，根據實際情況進行選擇。
（3）可以用手工或者自動的方式，把執行計劃演化(evolve)為"ACCEPTED"。還可以控制只讓效能更好的計劃被接受。
（4）允許設定"FIXED"的計劃。這樣其他的計劃將不會被選擇。

1.1.2 SPM使計劃真正的穩定

outline的缺點是太過死板，當資料量大幅度變化時無法做出相應的改變。 SQL proifle的缺點是，當資料量變化時，STA(SQL TuningAdvisor)會不可預知地去更改執行計劃。而SPM則會提供幾個完整的plan供選擇。

1.3 SPM的控制方式

SPM通過幾個標記來實現對執行計劃的控制：

（1）Enabled (控制活動)：
+ YES (活動的，但不一定會被使用)
+ NO (可以理解為被標記刪除)
（2）Accepted(控制使用)：
+ YES (只有 “Enabled” 並且“Accepted” 的計劃才會被選擇使用)
+ NO (如果是“Enabled” 那麼只有被evolve成“Accepted”才有可能被執行)
（3）Fixed(控制優先順序)：
+ YES (如果是“Enabled”並且“Accepted”，會優先選擇這個計劃，這個計劃會被視為不需要改變的)
+ NO (普通的計劃，無需優先)
（4）Reproduced(有效性)：
+ YES (優化器可以使用這個計劃)
+ NO (計劃無效，比如索引被刪除)

1.4 SPM如何捕捉（載入）執行計劃

1.4.1 自動捕捉

1. 首先把OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES設定成TRUE。
2. 從這個時刻開始，所有執行兩次以上的SQL語句會被觀測，執行計劃會進入Plan History。有個別例外的，參見note 788853.1。
3. 生成的第一個執行計劃被標記為ENABLED並且是ACCEPTED，後續的執行計劃會被標記為ENABLED但不是ACCEPTED。

4. 這時把OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES設定會FALSE，新的語句將不會建立Baseline。
5. 需要注意的是，即使關閉了自動捕捉，針對存在baseline的SQL，由於ACS（自適應遊標共享）的作用，仍舊會有新的PLAN生成，新的Plan仍會進入Plan History，標記為ENABLED但不是ACCEPTED。

1.4.2 批量匯入

匯入的baseline都會被自動標記為ACCEPTED， Oralce提供六種方式把計劃匯入到sql plan baseline中：
（1）從 SQL Tuning Set STS 匯入：DBMS_SPM.LOAD_PLANS_FROM_SQLSET

（2）從Cursor Cache中裝載：DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE

（3）從Stored Outlines中匯入： DBMS_SPM.MIGRATE_STORED_OUTLINE
（4）從記憶體中存在的計劃中匯入：DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE;

（5）從staging table表中匯入：dbms_spm.create_stgtab_baseline

（6）通過staging table從另外一個系統中移植：
DBMS_SPM.CREATE_STGTAB_BASELINE
DBMS_SPM.PACK_STGTAB_BASELINE
DBMS_SPM.UNPACK_STGTAB_BASELINE

1.5 執行計劃的選擇過程

在OPTIMIZER_USE_SQL_PLAN_BASELINES被設定成預設值TRUE，SQl Plan Baseline就會起作用。

1. 首先，無論是否存在baseline，oracle都會正常進行硬解析或者軟解析，為SQL生成一個執行計劃。由於ACS和bindpeeking的作用，存在baseline的SQL有可能在這時生成一個不同於baseline的執行計劃。

2. 如果baseline不存在，就按生成的計劃執行。如果baseline存在，那麼要檢視history裡是否有這個計劃，如果沒有，就將這個計劃插入，並標記為ENABLED,NON-ACCEPTED.

3. 在baseline中檢視是否有FIXED的計劃存在，如果存在，執行FIXED的計劃，如果存在多個FIXED的計劃，根據統計資訊重新計算cost，選擇cost小的那個。

4. 如果FIXED的計劃不存在，就選擇ACCEPTED的計劃執行。如果存在多個ACCEPTED的計劃，根據統計資訊重新計算cost，選擇cost小的那個。

注意：

這裡每次重新計算cost的代價不大，因為執行計劃是已知的，優化器不必遍歷所有的可能，只需根據演算法計算出已知計劃的cost便可。

1.6 執行計劃的演化(evolution)

執行計劃的演化指PlanHistory裡的執行計劃從NON-ACCEPTED，變成ACCEPTED的過程。如果上所述，由於ACS和Bind Peeking的作用，存在baseline的SQL有可能生成新的執行計劃，被儲存到Plan History中。 Oracle提供了API，通過自動或手工的方式，將一個計劃標記為ACCEPTED，這個計劃就會被後續的執行所選擇。

使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE這個API來控制執行計劃的演化。語法：
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL, --&gt NULL表示針對所有SQL
plan_name IN VARCHAR2 := NULL,
time_limit IN INTEGER := DBMS_SPM.AUTO_LIMIT,
verify IN VARCHAR2 := 'YES',
commit IN VARCHAR2 := 'YES' )
RETURN CLOB;

這裡由兩個標記控制：

（1）Verify：
+ YES (只有效能更好的計劃才會被演化)
+ NO (演化所有的計劃)
（2）Commit：
+ YES (直接演化)
+ NO (只生成報告)

這裡可以通過不同的排列組合，達到不同的效果：

（1）自動接收所有效能更好的執行計劃(Verify->YES, Commit->YES)
（2）自動接收所有新的執行計劃 (Verify->NO,Commit->YES)
（3）比較效能，生成報告，人工確認是否演化(Verify->NO, Commit->NO)

注意：

對於效能的驗證的方式，oracle會去實際執行來比較buffer gets

1.7 修改已有的Baseline

通過DBMS_SPM.ALTER_SQL_PLAN_BASELINE來完成。

語法：

DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
attribute_name IN VARCHAR2,
attribute_value IN VARCHAR2 )
RETURN PLS_INTEGER;

如把某個baseline 標記為FIXED：

SET SERVEROUT ON;
DECLARE
x NUMBER;
BEGIN
x := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle => '&&sql_handle',
plan_name => '&&plan_name',
attribute_name => 'FIXED',
attribute_value => 'YES' );
END;
/

Loading Hinted Execution Plans into SQLPlan Baseline. [ID 787692.1]

How to Use SQL Plan Management (SPM) -Example Usage (Doc ID 456518.1)
Plan Stability Features (Including SPM) Start Point (Doc ID 1359841.1)
HOW TO LOAD SQL PLANS INTO SPM FROM AWR (Doc ID 789888.1)
Sql Plan Baseline Not always created (Doc ID 788853.1)
Transporting SQL PLAN Baselines from one database to another. (Doc ID 880485.1)

以上內容轉自：

https://blogs.oracle.com/Database4CN/entry/oracle_11g_%E9%92%88%E5%AF%B9sql%E6%80%A7%E8%83%BD%E7%9A%84%E6%96%B0%E7%89%B9%E6%80%A7_%E4%B8%89_sql

二． SPM 示例

2.1 自動捕捉

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES引數用來控制SPM的自動捕獲，該引數預設值為FALSE。當該引數設定為TRUE時，對於重複執行的SQL 都會被觀測，其對應的執行計劃也會被加入Plan History。生成的第一個執行計劃被標記為ENABLED並且是ACCEPTED，後續的執行計劃會被標記為ENABLED但不是ACCEPTED。僅當在演化的過程中，效能最優的Plan (即標記為ACCEPTED)才會被新增到 SQL Plan baseline。

SQL> show parameteroptimizer_capture_sql_plan_baselines

NAME TYPE VALUE

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

optimizer_capture_sql_plan_baselinesboolean FALSE

SQL> alter system set optimizer_capture_sql_plan_baselines=true;

System altered.

SQL> show parameter optimizer_capture_sql_plan_baselines

NAME TYPE VALUE

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

optimizer_capture_sql_plan_baselinesboolean TRUE

SQL>

2.2 手工捕獲執行計劃

SPM捕獲執行計劃有大致分自動捕獲和手工捕獲，手工捕獲又有6種方法，具體見1.4.2 小節。

注意：

手工裝載的執行計劃預設都會被標記為accepted。如果SQL Plan baseline已經存在，那麼裝載的執行計劃就會新增到對應的baseline裡，如果不存在，就建立一個baseline。

這裡演示最常用的從cursorcache中load plan。使用DBMS_SPM.load_plans_from_cursor_cache函式來完成，關於該函式的具體說明，請參考官方文件。

SQL> col plan_name for a35

SQL> col sql_handle for a30

SQL> col origin for a15

SQL> selectSQL_HANDLE,plan_name,origin,enabled,accepted,fixed from DBA_SQL_PLAN_BASELINES;

SQL_HANDLE PLAN_NAME ORIGIN ENABLE ACCEPT FIXED

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

SQL_a6f4c0adedb52ad0 SQL_PLAN_adx60prqvaaqhf8e55c8a AUTO-CAPTURE YES YES NO

SQL> DECLARE

2 l_plans_loaded PLS_INTEGER;

3 BEGIN

4 l_plans_loaded :=DBMS_SPM.load_plans_from_cursor_cache(sql_id => 'axpwqnq0454s9');

5 END;

6 /

PL/SQL procedure successfully completed.

SQL> selectSQL_HANDLE,plan_name,origin,enabled,accepted,fixed from DBA_SQL_PLAN_BASELINES;

SQL_HANDLE PLAN_NAME ORIGIN ENABLE ACCEPT FIXED

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

SQL_267afeb2e8216c2d SQL_PLAN_2cyryqbn22v1da82c8876 MANUAL-LOAD YES YES NO

SQL_a6f4c0adedb52ad0 SQL_PLAN_adx60prqvaaqhf8e55c8a AUTO-CAPTURE YES YES NO

SQL>

使用DBMS_SPM.load_plans_from_cursor_cache函式load 之後，在DBA_SQL_PLAN_BASELINES檢視中多了一條記錄，並且顯示該plan 是accepted狀態。

2.3 演化SQL Plan Baselines

演化的過程就是把non-accepted 的plan 改成accepted的過程。對於手工load的執行計劃，會自動執行evolving的過程，因此預設就是accepted，而對於自動裝載的執行計劃，就需要使用EVOLVE_SQL_PLAN_BASELINE函式來實現演化過程。

SQL> SET LONG 10000

SQL> SELECTDBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_267afeb2e8216c2d')FROM dual;

2.4 完整示例

--取消自動捕獲：

SQL> conn / as sysdba

Connected.

SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;

System altered.

--建立表並插入資料：

SQL> conn dave/dave

Connected.

SQL> CREATE TABLE spm_test_tab (

2 id NUMBER,

3 description VARCHAR2(50)

4 );

Table created.

SQL> DECLARE

2 TYPE t_tab IS TABLE OFspm_test_tab%ROWTYPE;

3 l_tab t_tab := t_TAB();

4 BEGIN

5 FOR i IN 1 .. 10000 LOOP

6 l_tab.extend;

7 l_tab(l_tab.last).id := i;

8 l_tab(l_tab.last).description := 'Description for ' || i;

9 END LOOP;

10

11 FORALL i IN l_tab.first .. l_tab.last

12 INSERT INTO spm_test_tab VALUES l_tab(i);

13

14 COMMIT;

15 END;

16 /

PL/SQL procedure successfully completed.

SQL> EXECDBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);

PL/SQL procedure successfully completed.

--使用非索引列進行查詢，這裡使用Table access full：

SQL> set autot trace

SQL> SELECT description

2 FROM spm_test_tab

3 WHERE id = 99;

Execution Plan

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

Plan hash value: 1107868462

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

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

| 0| SELECT STATEMENT | | 1 | 25 | 13 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| SPM_TEST_TAB | 1 | 25 | 13 (0)| 00:00:01 |

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

--獲取剛才查詢的SQL_ID:

SQL> SELECT sql_id

2 FROM v$sql

3 WHERE sql_text LIKE '%spm_test_tab%'

4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%'

5 AND sql_text NOT LIKE '%EXPLAIN%';

SQL_ID

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

gat6z1bc6nc2d

--使用SQL_ID 從cursorcache中手工捕獲執行計劃：

SET SERVEROUTPUT ON

DECLARE

l_plans_loaded PLS_INTEGER;

BEGIN

l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(

sql_id => 'gat6z1bc6nc2d');

DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);

END;

/

Plans Loaded: 1

PL/SQL procedure successfully completed.

--使用DBA_SQL_PLAN_BASELINES檢視檢視SPM 資訊：

SQL> col sql_handle for a35

SQL> col plan_name for a35

SQL> set lin 120

SQL> SELECT sql_handle, plan_name,enabled, accepted

2 FROM dba_sql_plan_baselines

3 WHERE sql_text LIKE '%spm_test_tab%'

4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%';

SQL_HANDLE PLAN_NAME ENABLE ACCEPT

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

SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES

--重新整理Share Pool，使下次SQL 執行時必須進行硬解析：

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

--建立索引，收集統計資訊，並查詢相同的SQL:

SQL> CREATE INDEX spm_test_tab_idx ONspm_test_tab(id);

Index created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER,'SPM_TEST_TAB', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT description

2 FROM spm_test_tab

3 WHERE id = 99;

Execution Plan

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

Plan hash value: 1107868462

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

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

| 0| SELECT STATEMENT | | 1 | 25 | 13 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| SPM_TEST_TAB | 1 | 25 | 13 (0)| 00:00:01 |

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

這裡我們建立了索引，但是這裡還是走的全表掃描，這裡使用索引明顯才是最優的方案。

--檢視SPM 檢視：

SQL> SELECT sql_handle, plan_name,enabled, accepted

2 FROM dba_sql_plan_baselines

3 WHERE sql_handle = 'SQL_7b76323ad90440b9';

SQL_HANDLE PLAN_NAME ENABLE ACCEPT

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

SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES

SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5ted3324c0 YES NO

通過baselines查詢的結果，可以看到我們的SQL 產生了2條執行計劃。但是我們認為最優的執行計劃並沒有被標記為ACCEPT，所以沒有使用。

--演化執行計劃：演化就是將cost低的執行計劃標記為accept

SQL> SET LONG 10000

SQL> SELECTDBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_7b76323ad90440b9') FROMdual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_7B76323AD90440B9')

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

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

Evolve SQL PlanBaseline Report

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

Inputs:

-------

SQL_HANDLE = SQL_7b76323ad90440b9

PLAN_NAME =

TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

VERIFY = YES

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_7B76323AD90440B9')

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

COMMIT = YES

Plan:SQL_PLAN_7qxjk7bch8h5ted3324c0

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

Plan was verified: Time used .13 seconds.

Plan passed performance criterion: 15.01 times better than baselineplan.

Plan was changed to an accepted plan.

Baseline Plan Test Plan Stats Ratio

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

Execution Status: COMPLETE COMPLETE

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_7B76323AD90440B9')

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

Rows Processed: 1 1

Elapsed Time(ms): .376 .027 13.93

CPUTime(ms): .333 0

Buffer Gets: 45 3 15

Physical Read Requests: 0 0

Physical Write Requests: 0 0

Physical Read Bytes: 0 0

Physical Write Bytes: 0 0

Executions: 1 1

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

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_7B76323AD90440B9')

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

Report Summary

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

Number of plans verified: 1

Number of plans accepted: 1

SQL>

--再次檢視DBA_SQL_PLAN_BASELINES檢視：

SQL> SELECT sql_handle, plan_name,enabled, accepted

2 FROM dba_sql_plan_baselines

3 WHERE sql_handle = 'SQL_7b76323ad90440b9';

SQL_HANDLE PLAN_NAME ENABLE ACCEPT

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

SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES

SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5ted3324c0 YES YES

--再次執行SQL:

SQL> SELECT description

2 FROM spm_test_tab

3 WHERE id = 99;

Execution Plan

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

Plan hash value: 3121206333

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

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

| 0| SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |

| 1| TABLE ACCESS BY INDEX ROWID|SPM_TEST_TAB | 1 | 25 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN |SPM_TEST_TAB_IDX | 1 | | 1 (0)| 00:00:01 |

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

這次正確的使用了索引。因為只有標記為ENABLE和 ACCEPT的plan 才可以被使用。

2.5 修改 Plan Baselines

通過ALTER_SQL_PLAN_BASELINE函式可以修改執行計劃的屬性，具體修改的選項如下：

(1) enabled (YES/NO) : If YES, the plan is available for theoptimizer if it is also marked as accepted.

(2) fixed (YES/NO) : If YES, the SQL plan baseline will not evolveover time. Fixed plans are used in preference to non-fixed plans.

(3) autopurge (YES/NO) : If YES, the SQL plan baseline is purgedautomatically if it is not used for a period of time.

(4) plan_name : Used to amend the SQL plan name, up to a maximum of30 character.

(5) description : Used to amend the SQL plan description, up to amaximum of 30 character.

下面示例將我們2.4 節中的第一個走全表掃描的執行計劃標記為fixed。標記為fixed的執行計劃會被優先使用。

SET SERVEROUTPUT ON

DECLARE

l_plans_altered PLS_INTEGER;

BEGIN

l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(

sql_handle => 'SQL_7b76323ad90440b9',

plan_name => 'SQL_PLAN_7qxjk7bch8h5tb65c37c8',

attribute_name => 'fixed',

attribute_value => 'YES');

DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);

END;

/

Plans Altered: 1

PL/SQL procedure successfully completed.

--驗證：

SQL> set lin 180

SQL> col sql_handle for a25

SQL> col plan_name for a35

SQL> col origin for a15

SQL> selectSQL_HANDLE,plan_name,origin,enabled,accepted,fixed from DBA_SQL_PLAN_BASELINES;

SQL_HANDLE PLAN_NAME ORIGIN ENABLE ACCEPT FIXED

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

SQL_267afeb2e8216c2d SQL_PLAN_2cyryqbn22v1da82c8876 MANUAL-LOAD YES YES NO

SQL_496b0b4abd8a2948 SQL_PLAN_4kusb9aysnaa865d30abd AUTO-CAPTURE YES YES NO

SQL_5f081cda0133e385 SQL_PLAN_5y20wv80m7sw5391601ca AUTO-CAPTURE YES YES NO

SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 MANUAL-LOAD YES YES YES

SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5ted3324c0 AUTO-CAPTURE YES YES NO

SQL_a6f4c0adedb52ad0 SQL_PLAN_adx60prqvaaqhf8e55c8a AUTO-CAPTURE YES YES NO

SQL_f88491799a8f900b SQL_PLAN_gj14jg6d8z40ba052f708 AUTO-CAPTURE YES YES NO

--再次檢視我們之前的SQL:

SQL> SELECT description

2 FROM spm_test_tab

3 WHERE id = 99;

Execution Plan

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

Plan hash value: 1107868462

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

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

| 0| SELECT STATEMENT | | 1 | 25 | 13 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| SPM_TEST_TAB | 1 | 25 | 13 (0)| 00:00:01 |

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

--這裡已經走了全表掃描，根據2.4的示例，這裡走索引會更優，但因為我們將走全表掃描的執行計劃設定為fixed，所以優先使用這個執行計劃。

2.6 顯示SQL Plan Baselines

我們可以查詢DBA_SQL_PLAN_BASELINES檢視來獲取baselines的資訊，也可以通過DBMS_XPLAN包來獲取。 DISPLAY_SQL_PLAN_BASELINE函式會將plan 按一定格式進行輸入，這裡格式可以選擇：BASIC, TYPICAL 和ALL，預設使用TYPICAL。

示例如下：

SET LONG 10000

SELECT * FROM

TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_7qxjk7bch8h5ted3324c0'));

PLAN_TABLE_OUTPUT

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

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

SQL handle: SQL_7b76323ad90440b9

SQL text: SELECT description FROM spm_test_tab WHERE id = 99

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

Plan name:SQL_PLAN_7qxjk7bch8h5ted3324c0 Plan id: 3979551936

Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 3121206333

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

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

| 0| SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |

| 1| TABLE ACCESS BY INDEX ROWID|SPM_TEST_TAB | 1 | 25 | 2 (0)| 00:00:01 |

|* 2| INDEX RANGE SCAN | SPM_TEST_TAB_IDX | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified byoperation id):

PLAN_TABLE_OUTPUT

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

2- access("ID"=99)

25 rows selected.

2.7 設定SQL Management Base

SQL Management Plan的資料存在在SYSAUX表空間下面，存放的資料包括SQL Plan baselines，statement logs，plan histories 和SQL Profiles。 SMB 能分配的磁碟空間由如下2個屬性控制。可以使用DBMS_SPM.CONFIGURE過程來進行修改：

(1) space_budget_percent (default10) : Maximum size as a percentage of SYSAUX space.Allowable values 1-50.

(2) plan_retention_weeks (default 53) : Number of weeks unusedplans are retained before being purged. Allowable values 5-523 weeks.

SQL> col parameter_name for a25

SQL> SELECT parameter_name, parameter_valueFROM dba_sql_management_config;

PARAMETER_NAME PARAMETER_VALUE

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

SPACE_BUDGET_PERCENT 10

PLAN_RETENTION_WEEKS 53

--修改這2個屬性：

SQL> BEGIN

2 DBMS_SPM.configure('space_budget_percent', 11);

3 DBMS_SPM.configure('plan_retention_weeks', 54);

4 END;

5 /

PL/SQL procedure successfully completed.

--驗證：

SQL> SELECT parameter_name,parameter_value FROM dba_sql_management_config;

PARAMETER_NAME PARAMETER_VALUE

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

SPACE_BUDGET_PERCENT 11

PLAN_RETENTION_WEEKS 54

2.8 遷移SQL Plan Baselines

可以使用DBMS_SPM 包將SQLPlan baselines在不同資料庫之間進行遷移。具體的操作步驟如下。

2.8.1 在source database 建立登臺表（staging table）

BEGIN

DBMS_SPM.CREATE_STGTAB_BASELINE(

table_name =>'spm_stageing_tab',

table_owner => 'DAVE',

tablespace_name => 'USERS');

END;

/

2.8.2 將SQL Planbaselines 匯入staging table

使用PACK_STGTAB_BASELINE函式可以實現這個功能，該函式有一些引數，具體參考官方文件。這裡示例將所有SQL Plan Baselines匯入staging table。

SET SERVEROUTPUT ON

DECLARE

l_plans_packed PLS_INTEGER;

BEGIN

l_plans_packed := DBMS_SPM.pack_stgtab_baseline(

table_name =>'spm_stageing_tab',

table_owner => 'DAVE');

DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);

END;

/

SQL>

2.8.3 將staging table 傳輸到目標庫

這裡可以使用expdp/impdp 實現。不多說。

2.8.4 將staging table匯入目標庫

使用UNPACK_STGTAB_BASELINE函式實現這個功能，同樣有一些引數設定，這裡演示匯入所有SQL PLAN BASELINES.

SET SERVEROUTPUT ON

DECLARE

l_plans_unpacked PLS_INTEGER;

BEGIN

l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(

table_name =>'spm_stageing_tab',

table_owner => 'DAVE',

creator => 'DAVE');

DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);

END;

/

2.9 刪除Plans 和 Baselines

DROP_SQL_PLAN_BASELINE函式可以從baselines中drop 某個執行的執行計劃，如果不執行plan name，那麼會drop 所有的plan。即drop了baseline。

SET SERVEROUTPUT ON

DECLARE

l_plans_dropped PLS_INTEGER;

BEGIN

l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (

sql_handle => 'SQL_7b76323ad90440b9',

plan_name => NULL);

DBMS_OUTPUT.put_line(l_plans_dropped);

END;

/

博文連結地址：http://blog.csdn.net/tianlesoftware/article/details/8296524

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

Oracle 11g 新特性 -- SQL Plan Management 示例

一. SPM 說明

1.1 相關名詞說明

1.2 SPM的特點

1.2.1 與profile和outline相比，更加靈活的控制手段

1.1.2 SPM使計劃真正的穩定

1.3 SPM的控制方式

1.4 SPM如何捕捉（載入）執行計劃

1.4.1 自動捕捉

1.4.2 批量匯入

1.5 執行計劃的選擇過程

1.6 執行計劃的演化(evolution)

1.7 修改已有的Baseline

1.8 相關MOS 文件

二． SPM 示例

2.1 自動捕捉

2.2 手工捕獲執行計劃

2.3 演化SQL Plan Baselines

2.4 完整示例

2.5 修改 Plan Baselines

2.6 顯示SQL Plan Baselines

2.7 設定SQL Management Base

2.8 遷移SQL Plan Baselines

2.8.1 在source database 建立登臺表（staging table）

2.8.2 將SQL Planbaselines 匯入staging table

2.8.3 將staging table 傳輸到目標庫

2.8.4 將staging table匯入目標庫

2.9 刪除Plans 和 Baselines

相關文章