原 Oracle固定执行计划的方法--outline、SQL Profile和SPM（绑定执行计划）
Tags： Oracle原创执行计划绑定执行计划outlineSPMSQL Profile
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优化集中装载的示例如下所示：
1 2 3 4 5 6 7 | DECLARE MY_PLANS PLS_INTEGER; BEGIN MY_PLANS := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'tset1'); END; / |
从游标高速缓存中装载的示例如下所示：
1 2 3 4 5 6 7 | 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计划基线中。代码如下所示：
1 2 3 4 5 6 7 8 9 10 | 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主要有下面几个相关的数据字典视图：
1 2 3 4 | SELECT * FROM DBA_SQL_PLAN_BASELINES; SELECT * FROM DBA_SQLSET_PLANS; SELECT * FROM DBA_ADVISOR_SQLPLANS; |
