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

在之前的Blog 里了解了Oracle 11g SQL Plan Management的理论，这篇Blog来演示一些具体的操作示例。

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, --> 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;
/

1.8 相关MOS 文档

Whitepaper: SQL Plan Management in Oracle Database 11g

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

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

| Id | Operation         | Name         | Rows | Bytes | Cost (%CPU)| Time     |

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

|   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

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

| Id | Operation         | Name         | Rows | Bytes | Cost (%CPU)| Time     |

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

|   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

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

| Id | Operation                   |Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

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

| Id | Operation         | Name         | Rows | Bytes | Cost (%CPU)| Time     |

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

|   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

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

| Id | Operation                   |Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

[转]Oracle 11g 新特性 -- SQL Plan Management 示例,古老的榕树,5-wow.com