oracle11g中SQL优化（SQL TUNING）新特性之SQL Plan Management(SPM)

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中是非常容易的事情。首先，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字典视图。下面是一个控制执行计划的最有价值信息的总结： 

Table 1.1. SQL Plan Baseline Plan Control Metadata
Attribute	Description
SQL_HANDLE	A unique SQL identifier in string form; it can be used as a search key
PLAN_NAME	A unique SQL plan identifier in string form; it can be used as a search key
SQL_TEXT	The SQL statement’s unnormalized, actual text
ORIGIN	Tells if the SQL Plan was either: AUTO-CAPTURE: Automatically captured MANUAL-LOAD: Manually evolved MANUAL-SQLTUNE: Automatically evolved by SQL Tuning Advisor AUTO-SQLTUNE: Automatically evolved by Automatic SQL Tuning
ENABLED	Indicates that the SQL Plan is enabled (YES) for CBO utilization or not (NO). Disabled plans are ignored by the CBO
ACCEPTED	Indicates that the SQL Plan is validated as a good plan, either because Oracle 11g has: Automatically accepted it, or The DBA has forced its manual acceptance by changing its status to ACCEPTED via procedure DBMS_SPM.ALTER_SQL_PLAN_BASELINE()
FIXED	SQL Plans whose FIXED attribute is set to YES will be considered by the CBO. If multiple plans are marked as FIXED, the CBO will only select the best execution plan from those so marked
OPTIMIZER_COST	The total cost estimated by the CBO to execute the SQL statement using this execution plan

查看已存在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;