1.   简单介绍

Oracle Database11gR1引进了SQL PlanManagement（简称SPM），一套同意DBA捕获和保持随意SQL语句运行计划最优的新工具，这样，限制了刷新优化器统计数据。已有应用改变。甚至数据库版本号升级带来的影响。本文帮助对SPM原理基本了解，并对其性能优化能力进行简要的说明。

2.   SPM原理和机制

Oracle 11g通过一个简单而优雅的方法实施了解决SQL计划意外恶化的一套称为SQL Plan Management(SPM)的新特点。仅仅要用户会话开启了自己主动SQL Plan Baseline捕获，CBO就会在SQL Management Base(SMB)内记录该会话内运行的不论什么SQL，把SQL语句文本，梗概（Outline）,绑定变量，及其编译环境等存储为一个SQL Plan Baseline。

因为这是语句第一次运行，Oracle11g会把当时的运行计划当成最优的。正是在同样SQL语句第二次运行时。SPM的优雅才体现的更加明显。在语句的第二次运行期间，CBO会比較语句的运行计划和存储在SMB中的计划，新计划被评估看它是否比SMB中的计划更高效。

假设新计划会改善语句的性能，那么。SPM会把新计划标记为该语句最好的计划。

仅仅要DBA没改动OPTIMIZER_USE_SQL_PLAN_BASELINES參数的默认设置（true）,那么，CBO就会在当前的语句运行中採用新的计划。否则，假设新计划减少了语句的性能，那么，CBO会从SMB中全部可接受计划中选择一个成本最低的计划。而且，SPM会把那个新计划存储到SMB中，由于在不久的未来，该新计划或许成为不错的选择。

2.1.  捕获SQL Plan Basebline

Oracle11g中，捕获SQLPlan Baseline并存储到SMB中是很easy的事情。首先。OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES參数控制SQL Plan Baselines自己主动捕获是否开启，该參数的默认设置为FALSE，这意味着SQL Plan Baselines默认不会被自己主动捕获。然而，DBA在会话或系统级将它设置为TRUE，SPM就開始记录SQL语句的运行。当一条SQL语句被运行多于一次时。该SQL语句就被觉得SQL Plan Baselines捕获的候选。

其次。Oracle11g有个新的包DBMS_SPM，通过从下面几个来源手工“种植”计划,能够预先捕获和引进大量SQL语句：

? 数据库Library Cache中一个或多个SQL语句能被用来创建SQL PlanBaselines。

过程LOAD_PLANS_FROM_CURSOR_CACHE能被用来在Library Cache中捕获不论什么语句的子集作为潜在SMB的候选。 

? 存储于SQL Tuning Set或一个AWR快照中的SQL语句能被过程LOAD_PLANS_FROM_SQLSET捕获和被转换进SQL Plan Baselines。

? 最后。SQL Plan Baselines能来自一个导入存储表。这意味着能够从不同的数据库捕获语句。

3.  查看SQL Plan Baseline信息

被捕获和存储在SMB中的SQL Plan Baseline元数据包括SPM和CBO用来控制计划的属性。当新计划进入SMB时，它被标为ENABLED,但还不能标记ACCEPTED。直到：

1)   CBO已经评估了该计划并推断它为最好的计划。

2)   计划已被演化为ACCEPTED模式。在CBO考虑採用一个计划前，该计划必须被标记为ENABLED和ACCEPTED。

查看这些元数据最简单的方法就是查询DBA_SQL_PLAN_BASELINES字典视图。以下是一个控制运行计划的最有价值信息的总结： 

查看已存在SQL Plan Baselines中，对一条SQL语句运行有潜在影响的还有一个方法是通过DBMS_XPLAN的新过程DISPLAY_SQL_PLAN_BASELINE。比如：能用这个过程来查看SMB中和SQL语句柄匹配的全部SQLPlan Baselines；假设提供了SQL语句的计划名，也能够显示该语句的运行计划等。

4.   自己主动捕获的实现和过程

以下。我们分析自己主动捕获SQL Plan Baselines的过程。首先，我们设置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES參数为TRUE（默觉得FALSE）以开启SQL Plan Baselines自己主动捕获。我们还须要把OPTIMIZER_USE_SQL_PLAN_BASELINES參数设置为TRUE (默认值)。该參数控制CBO是否检查SQL语句反复运行产生的计划是否将被评估为一个好的计划。

接着。我们运行相同的一个SQL两次。第一次运行时。SQL语句被记录，第二次运行时，计划自己主动被捕获进SMB并被标记为该语句ACCEPTED的SQLPlan Baseline。

当今后该SQL语句再次被运行，并产生了一个不同的新的计划时，该计划也会被自己主动捕获进SMB。但并不被标为ACCEPTED。所以。SPM仅仅会把第一个计划标记为ENABLED和ACCEPTED。

5.  SQL Plan Baseline的演化

SPB中未被标示为ACCEPTED的SQL Plan Baselines，须要进一步被演化为标示ACCEPTED状态，才干够被今后再次运行的SQL语句採用，对SPB进行演化的方法，主要有例如以下两种：

5.1.  手工方法

? 调用dbms_spm包的evolve_sql_plan_baseline()函数

SQL> var report clob;

SQL> exec :report := dbms_spm.evolve_sql_plan_baseline();

SQL> print :report

SQL> select sql_text, plan_name, enabled, accepted fromdba_sql_plan_baselines;

? 调用SQL Tuning Advisor工具包

SQL> var tname varchar2(30);

SQL> exec :tname :=dbms_sqltune.create_tuning_task(sql_id => ‘bfbr3zrg9d5cc‘);

SQL> execdbms_sqltune.execute_tuning_task(task_name => :tname);

SQL> selectdbms_sqltune.report_tuning_task(:tname, ‘TEXT‘, ‘BASIC‘) FROM dual;

SQL> exec dbms_sqltune.accept_sql_profile(task_name=> :tname);

SQL> select sql_text, plan_name, enabled,accepted from dba_sql_plan_baselines;

5.2.  自己主动方法

?  定期调度dbms_spm包的evolve_sql_plan_baseline()

?  配置SQL TUNING ADVISOR，使其在自己主动任务窗体自己主动执行

6.   详细操作命令

?  开启自己主动捕获和採用SPM

ALTER SESSION SET optimizer_capture_sql_plan_baselines=TRUE;

ALTER SESSION SET optimizer_use_sql_plan_baselines=TRUE;

?  查看SPM元数据

COL creator         FORMAT A08      HEADING ‘Creator‘

COL hndle           FORMAT A08      HEADING ‘SQL|Handle‘

COL plnme           FORMAT A08      HEADING ‘Plan|Name‘

COL sql_hdr         FORMAT A25      HEADING ‘SQL Text‘ WRAP

COL origin          FORMAT A12      HEADING ‘Origin‘

COL optimizer_cost  FORMAT 9999999  HEADING ‘CBO|Cost‘

COL enabled         FORMAT A04      HEADING ‘Ena-|bled‘

COL accepted        FORMAT A04      HEADING ‘Acpt‘

COL fixed           FORMAT A04      HEADING ‘Fixd‘

COL autopurge       FORMAT A04      HEADING ‘Auto|Purg‘

COL create_dt       FORMAT A11      HEADING ‘Created|On‘ WRAP

COL lst_exc_dt      FORMAT A11      HEADING ‘Last|Executed‘ WRAP

SELECT 

     creator

    ,SUBSTR(sql_handle, -8, 8) hndle

    ,SUBSTR(plan_name, -8, 8)  plnme

    ,SUBSTR(sql_text, 1, 75) sql_hdr

    ,origin

    ,optimizer_cost

    ,enabled

    ,accepted

    ,fixed

    ,autopurge

    ,TO_CHAR(created, ‘yyyy-mm-dd hh24:mi:ss‘) create_dt

    ,TO_CHAR(last_executed, ‘yyyy-mm-dd hh24:mi:ss‘) lst_exc_dt

  FROM dba_sql_plan_baselines

  WHERE (sql_text LIKE ‘%SPM%‘)

 ORDER BY 1,2,3;

?  通过DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE显示已保留的包括特定文本的SQL Plan Baselines

SET LINESIZE 150

SET PAGESIZE 2000

SELECT PT.*

   FROM (SELECT 

             DISTINCT sql_handle 

           FROM dba_sql_plan_baselines

          WHERE sql_text like ‘%SPM%‘) SPB,

        TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(SPB.sql_handle, NULL,‘TYPICAL +NOTE‘)) PT;