Oracle11g的新特性:SQL Plan Management(SPM)

    来源：赛迪网    作者：yashi

Oracle11g中，Oracle提供dbms_spm包来管理SQL Plan，SPM是一个预防机制，它记录并评估sql的执行计划，将已知的高效的sql执行计划建立为SQL Plan Baselines，SQL Plan Baseline的功能是保持SQL的性能而不必关注系统的改变。

1.Capturing SQL Plan Baselines

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

A. Automatic Plan Capture

如果要激活自动的SQL Plan Capture，则需要设置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES，该参数默认为False，如果设置为True，则表示海底捞月活自动捕获SQL Plan，则系统会自动创建并维护SQL Plan History，SQL Plan History包括优化器关注的：比如an execution plan, SQL text, outline, bind variables, and compilation environment。

B. Manual Plan Loading

也可以手动装载一个存在的SQL Plan作为SQL Plan Baseline，手动装装的SQL Plan并不校验它的性能：

--从SQL Tuning Set中装载:
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET
(sqlset_name => 'tset1');
END;
/
--从Cursor Cache中装载
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
(sql_id => '7qqnad1j615m7');
END;
/

2.Selecting SQL Plan Baselines

在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直到它被验证不会引起一下性能问题才会被使用。

--如何激活使用SQL Plan Baselins

SQL> alter system set OPTIMIZER_USE_SQL_PLAN_BASELINES = true;

3.Evolving SQL Plan Baselines

在SQL Plan Baselines的演变阶段，Oracle评估新的Plan的性能并将性能较好的Plan存放SQL Plan Baselines中，可以使用dbms_spm package的过程EVOLVE_SQL_PLAN_BASELINE将新的SQL Plan存入已经存在的SQL Plan Baselines中，新的Plan将会作为已经Accept Plan加入到SQL Plan Baselines中。

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

4.相关的数据字典

dba_sqlset_plans
dba_advisor_sqlplans
dba_sql_plan_baselines

5.用dbms_xplan显示SQL Plan

在11g中，Oracle增强了dbms_xplan包的显示功能，不仅可以显示单个sql的执行计划，还可以用来显示sql tuning set，以及sql plan baselines等，在此就仅仅对显示sql plan baselines做测试：

select *
from table(dbms_xplan.display_sql_plan_baseline
(sql_handle => '&SYS_SQL_',
format => 'basic'));