【DB笔试面试609】在Oracle中，SPM（SQL Plan Management，SQL计划管理）是什么？

♣

题目部分

在Oracle中，SPM（SQL Plan Management，SQL计划管理）是什么？

♣

答案部分

Outline的缺点是太过死板，当数据量大幅度变化时无法做出相应的改变。SQL Proifle的缺点是，当数据量变化时，STA会不可预知地去更改执行计划，而SPM则会提供几个完整的PLAN供选择。SPM是一种随Oracle 11g引入的新功能，通过维护所谓的“SQL计划基线（SQL Plan Baseline）”来使系统能够自动控制SQL计划演变。启用此功能后，只要证明新生成的SQL计划与SQL计划基线相集成不会导致性能回归，就可以进行此项集成。因此，在执行某个SQL语句时，只能使用对应的SQL计划基线中包括的计划。可以使用SQL优化集自动加载或植入SQL计划基线。SPM的主要优点是系统性能稳定，不会出现计划回归。此外，该功能还可以节省DBA的许多时间，这些时间通常花费在确定和分析SQL性能回归以及寻找可用的解决方案上。在Oracle 11g中，Oracle提供DBMS_SPM包来管理SQL Plan，SPM是一种预防机制，它记录并评估SQL的执行计划，将已知的高效的SQL执行计划建立为SQL计划基线。

在SQL计划基线捕获阶段，Oracle记录SQL的执行计划并检测该执行计划是否已经改变。如果SQL改变后的执行计划是安全的，那么SQL就使用新的执行计划，因此，Oracle维护单个SQL执行计划的历史信息，Oracle维护的SQL执行计划的历史仅仅针对重复执行的SQL，SQL计划基线可以手工LOAD，也可以设置为自动捕获。

加载SQL计划基线的方式有两种，下面分别介绍。

（1）即时捕获，自动捕获（Automatic Plan Capture）：

使用自动计划捕获，方法如下：设置初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES为TRUE（默认值为FALSE）。将该参数设置为TRUE将打开自动标识可重复SQL语句，以及自动为此类语句创建计划历史记录的功能。如果要激活自动的SQL PLAN CAPTURE，那么需要设置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES为TRUE（默认值为FALSE），然后自动捕获SQL PLAN就会生效，系统会自动创建并维护SQL PLAN HISTORY，SQL PLAN HISTORY包括优化器关注的一些参数，例如：AN EXECUTION PLAN、SQL TEXT、OUTLINE、BIND VARIABLES、和COMPILATION ENVIRONMENT。

（2）成批加载（Manual Plan Loading）：

使用DBMS_SPM程序包可以加载SQL计划基线。该程序包支持手动管理SQL计划基线。使用此程序包，可以将SQL计划从游标高速缓存或现有的SQL优化集（SQL Tuning SET，STS）直接加载到SQL计划基线中。对于要从STS加载到SQL计划基线的SQL语句，需要将其SQL计划存储在STS中。使用DBMS_SPM可以将基线计划的状态从已接受更改为未接受或者从未接受更改为已接受。

从SQL优化集中装载的示例如下所示：

DECLARE
  MY_PLANS PLS_INTEGER;
BEGIN
  MY_PLANS := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'tset1');
END;
/

从游标高速缓存中装载的示例如下所示：

DECLARE
   MY_PLANS PLS_INTEGER;
BEGIN
  MY_PLANS := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '7qqnad1j615m7');
END;
/

在SQL计划基线演化阶段，Oracle会按常规方式评估新计划的性能，并将性能较好的计划集成到SQL计划基线中。当优化程序为SQL语句找到新的计划时，会将该计划作为未接受的计划添加到计划历史记录中。然后，相对于SQL计划基线的性能，验证该计划的性能。如果经验证某个未接受的计划不会导致性能回归（手动或自动），那么该计划会被更改为已接受计划，并集成到SQL计划基线中。

演化SQL计划基线的方式有两种：

（1）使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE函数。该函数将返回一个报表，显示是否已将一些现有的历史记录计划移到了计划基线中。也可以在历史记录中指定要测试的特定计划。

（2）运行SQL优化指导：通过使用SQL优化指导手动或自动优化SQL语句，演化SQL计划基线。当SQL优化指导发现已优化的计划，并确认其性能优于从相应的SQL计划基线中选择的计划的性能时，就会生成一个建议案以接受SQL概要文件。

在SQL计划基线的演变阶段，Oracle评估新的计划的性能并将性能较好的计划存放在SQL计划基线中，可以使用DBMS_SPM包的EVOLVE_SQL_PLAN_BASELINE过程用户将新的SQL PLAN存入已经存在的SQL计划基线中，新的PLAN将会作为已经ACCEPT PLAN加入到SQL计划基线中。代码如下所示：

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

SPM主要有下面几个相关的数据字典视图：

SELECT * FROM DBA_SQL_PLAN_BASELINES;
SELECT * FROM DBA_SQLSET_PLANS;
SELECT * FROM DBA_ADVISOR_SQLPLANS;

如果将计划添加到计划历史记录中，那么该计划将与一些重要的属性关联：

（1）SIGNATURE、SQL_HANDLE、SQL_TEXT和PLAN_NAME是搜索操作的重要标识符。

（2）使用ORIGIN可以确定计划是自动捕获的（AUTO-CAPTURE）、手动演化的（MANUALLOAD）、通过SQL优化指导自动演化的（MANUAL-SQLTUNE）还是通过自动SQL优化自动演化的（AUTO-SQLTUNE）。

（3）ENABLED和ACCEPTED：ENABLED属性表示计划已启用，可供优化程序使用。如果未设置ENABLED，那么系统将不考虑此计划。ACCEPTED属性表示用户在将计划更改为ACCEPTED时计划已经过验证为有效计划（系统自动进行的或用户手动进行的）。如果将某个计划更改为ACCEPTED，那么仅当使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE()更改其状态时，该计划才是非ACCEPTED的。可以通过删除ENABLED设置暂时禁用ACCEPTED计划。计划必须为ENABLED和ACCEPTED，优化程序才会考虑使用它。

（4）FIXED表示优化程序仅考虑标记为FIXED的计划，而不考虑其它计划。例如，如果有10个基线计划，其中的三个计划被标记为FIXED，那么优化程序将仅使用这三个计划中的最佳计划，而忽略其它所有计划。如果某个SQL计划基线至少包含一个已启用的已修复计划，则该SQL计划基线就是FIXED的。如果在修复的SQL计划基线中添加了新计划，则在手动将这些新计划声明为FIXED之前，无法使用这些新计划。

可以使用DBA_SQL_PLAN_BASELINES视图查看每个计划的属性。然后，可以使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE函数更改其中的某些属性，可以使用DBMS_SPM.DROP_SQL_PLAN_BASELINE函数删除计划或整个计划历史记录。

注：DBA_SQL_PLAN_BASELINES视图包含了一些附加属性；使用这些属性可以确定各个计划的上次使用时间，以及是否应自动清除某个计划。

如果使用的是自动计划捕获，那么第一次将某个SQL语句标识为可重复时，其最佳成本计划将被添加到对应的SQL计划基线中。然后，该计划将用于执行相应的语句。

如果某个SQL语句存在计划基线，并且初始化参OPTIMIZER_USE_SQL_PLAN_BASELINES被设置为TRUE（默认为TRUE），那么优化程序将使用比较计划选择策略。每次编译SQL语句时，优化程序都会先使用传统的基于成本的搜索方法建立一个最佳成本计划，然后尝试在SQL计划基线中找到一个匹配的计划。如果找到了匹配的计划，则优化程序将照常继续运行。如果未找到匹配的计划，那么优化程序会先将新计划添加到计划历史记录中，然后计算SQL计划基线中各个已接受的计划的成本，并选择成本最低的那个计划。使用随各个已接受的计划存储的大纲复制这些已接受的计划。因此，对于SQL语句来说，拥有一个SQL计划基线的好处就是：优化程序始终选择该SQL计划基线中的一个已接受的计划。

通过SQL计划管理，优化程序可以生成最佳成本计划，也可以生成基线计划。此信息将被转储在有关解释计划的PLAN_TABLE的OTHER_XML列中。此外，还可以使用新的DBMS_XPLAIN.DISPLAY_SQL_PLAN_BASELINE函数，显示某个计划基线中给定SQL_HANDLE的一个或多个执行计划。如果还指定了PLAN_NAME，那么将显示相应的执行计划。

注：为了保留向后兼容性，如果用户会话的某个SQL语句的存储大纲对是活动的，那么将使用此存储大纲编译该语句。此外，即使为会话启用了自动计划捕获，也不将优化程序使用存储大纲生成的计划存储在SMB中。

虽然存储大纲没有任何显式迁移过程，但可使用DBMS_SPM程序包中的LOAD_PLAN_FROM_CURSOR_CACHE过程或LOAD_PLAN_FROM_SQLSET过程将其迁移到SQL计划基线。迁移完成时，应禁用或删除原始的存储大纲。

在SQL Plan选择阶段，SQL每一次编绎，优化器使用基于成本的方式，建立一下best-cost的执行计划，然后去匹配SQL Plan Baselines中的SQL Plan，如果找到了匹配的SQL Plan，则会使用这个执行计划，如果没有找到匹配的SQL Plan,优化器就会去SQL Plan History中去搜索成本最低的SQL Plan，如果优化器在SQL Plan History中找不到任务匹配的SQL Plan，那么该SQL Plan被作为一个Non-Accept Plan被存入SQL Plan History，新的SQL Plan直到它被验证不会引起一下性能问题才会被使用。

DROP_SQL_PLAN_BASELINE函数可以从BASELINES中DROP某个执行的执行计划，如果不执行PLAN NAME，那么会DROP所有的PLAN，即DROP了BASELINE。

删除某个SQL的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;
/ 

删除所有baseline：

declare
  v_plan_num PLS_INTEGER;
begin

  for cur in (SELECT * FROM dba_sql_plan_baselines) loop

    begin
      v_plan_num := dbms_spm.drop_sql_plan_baseline(sql_handle => cur.sql_handle);
    exception
      when others then
        null;
    end;

  end loop;

end;
/

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。