使用SPM（sql plan management，sql计划管理）

SPM有助于保持sql语句的性能，只允许执行能提高语句性能的执行计划。SPM的目的是稳定sql语句的执行计划的，允许选择新的执行计划，只要能提高sql语句的性能。

什么时候用到SPM

1.新版本的oracle。

2.优化器统计信息的更改或数据的变化

3.方案，应用程序或元数据的变化

4.系统设置发生改变

5.sql profile的创建

SMB（sql management base，sql管理基线）层次关系：

SMB：包含sql计划历史和sql计划基线。存储在sysaux表空间中，并包含sql profile

sql计划历史：smb的子集，包含为sql语句生产的接受和未接受的执行计划

sql计划基线：sql计划历史的子集，只包含sql语句接受的执行计划（sql计划基线必须同时处于启用和接受状态，才会被优化器使用）

其他的一些参数值：

autopurge，如果一个计划连续53周没有使用，就会自动被从计划历史中清除，可以使用的dbms_spm.configure包修稿这个值。

optimizer_use_sql_plan_baselines，默认值是true，如果sql计划基线存在则数据库确定是否使用它。

optimizer_capture_plan_baselines，默认值是false，如果为true，则任何执行的sql语句都会被添加到sql计划基线中，但不一定是接受的计划

dba_sql_plan_baselines视图收集已经创建好的计划基线信息

SPM有助于：

1.数据库升级

2.系统和数据的变化

3.部署行的应用

SPM示例

1.启用捕获并多次执行sql语句以便spm判断语句是否可重复，然后关闭捕获：

alter session set optimizer_capture_sql_plan_baselines=true;

select * /* wlp_spm */ from SCOTT.emp where job='CLERK';

select * /* wlp_spm */ from SCOTT.emp where job='CLERK';

select * /* wlp_spm */ from SCOTT.emp where job='CLERK';

alter session set optimizer_capture_sql_plan_baselines=false;

2.查看dba_sql_plan_baselines，确定sql语句再spm中的状态：

select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text
from dba_sql_plan_baselines;

其中，enable是yes，accepted是yes，fixed是no，另外pl/sql developer是添加这个计划的模块（module是pl/sql developer）。

3.现在增加索引，改变环境，并在此运行sql

create index ind_emp_job on scott.emp(job);

alter session set optimizer_capture_sql_plan_baselines=true;

select * /* wlp_spm */ from SCOTT.emp where job='CLERK';

select * /* wlp_spm */ from SCOTT.emp where job='CLERK';

select * /* wlp_spm */ from SCOTT.emp where job='CLERK';

alter session set optimizer_capture_sql_plan_baselines=false;

4.查询dba_sql_plan_baselines，确定sql语句再spm中的状态

这里又添加了一个具有相同sql_handle的计划名称，并且accepted是no，表示优化器不会考虑这个计划。

5.现在设置optimizer_use_sql_plan_baselines为false，并允许优化器选择计划，此计划不一定在sql计划基线中。然后，将optimizer_use_sql_plan_baselines为true，并强制优化器再计划基线中仅选择一个已经接受的计划。

alter system set optimizer_use_sql_plan_baselines=false;

explain plan for
 select * /* wlp_spm */ from SCOTT.emp where job='CLERK';

select * from table(dbms_xplan.display(null,null,'basic'));

alter system set optimizer_use_sql_plan_baselines=true;

explain plan for
 select * /* wlp_spm */ from SCOTT.emp where job='CLERK';

select * from table(dbms_xplan.display(null,null,'basic'));

第一种情况下使用了索引，第二种情况下并没有使用。这是因为索引的计划是未被接受的计划。

6.接下来改善sql计划，如果这个计划比accepted状态已经是yes的计划性能更好，允许accepted状态为no的计划变成yes。

declare
   report clob;
begin
  report:=dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_a9143294c7145a6e');
  dbms_output.put_line(report);
end;

/

查看sql语句再spm中的状态， 这个计划，之前是为接受，现在变成了接受。

select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text
from dba_sql_plan_baselines;

现在将optimizer_use_sql_plan_baselines设为true再运行sql，会使用这个新的已经接受的计划。因为它的性能比其他的以及接受的计划更好：

alter system set optimizer_use_sql_plan_baselines=true;

explain plan for
 select * /* wlp_spm */ from SCOTT.emp where job='CLERK';

select * from table(dbms_xplan.display(null,null,'basic'));

使用固定的sql计划基线

如果有的话，最好使用固定的sql计划，而不是其他sql计划基线中的计划，如果存在不止一个sql语句的固定计划，那么使用性能更好的固定计划。

如何设置固定计划的例子：

declare
  plans_altered  PLS_INTEGER;
begin
  plans_altered:=dbms_spm.alter_sql_plan_baseline(
  sql_handle => 'SYS_SQL_a9143294c7145a6e',
  plan_name=>'SQL_PLAN_ak51kkm3j8qmfe1ff382b',
  attribute_name=>'fixed',
  attribute_value=>'YES');
end;

select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text
from dba_sql_plan_baselines;

删除计划

查询dba_sql_plan_baselines表，基线被删除；