oracle SPM 执行计划管理

************************************************************
第一部分：概念

************************************************************



SQL 计划管理是一种随Oracle Database 11g 引入的新功能，通过维护所谓的“SQL
计划基线(SQL plan baseline(11g))”来使系统能够自动控制SQL 计划演变。启用此功能后，
只要证明新生成的SQL 计划与SQL 计划基线相集成不会导致性能回归，就可以进行此项集成。
因此，在执行某个SQL 语句时，只能使用对应的SQL 计划基线中包括的计划。可以使用SQL
优化集自动加载或植入SQL 计划基线。

SQL 计划管理功能的主要优点是系统性能稳定，不会出现计划回归。此外，
该功能还可以节省DBA 的许多时间，这些时间通常花费在确定和分析SQL 性能回归以及
寻找可用的解决方案上.

（1）  即时捕获：

使用自动计划捕获，方法是：将初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELIN
ES 设置为TRUE。默认情况下，该参数设置为FALSE。将该参数设置为TRUE
将打开自动标识可重复SQL 语句，以及自动为此类语句创建计划历史记录的功能。


（2）  成批加载：
使用DBMS_SPM 程序包；该程序包支持手动管理SQL
计划基线。使用此程序包，可以将SQL 计划从游标高速缓存或现有的SQL
优化集(STS) 直接加载到SQL计划基线中。对于要从STS 加载到SQL 计划基线的SQL
语句，需要将其SQL计划存储在STS中。使用DBMS_SPM
可以将基线计划的状态从已接受更改为未接受（以及从未接受更改为已接受），还
可以从登台表导出基线计划，然后使用导出的基线计划将SQL
计划基线加载到其它数据库中。


NOTE:
SQL计划管理使用一种叫做SQL计划基准机制。计划基线是针对sql优化器
允许使用并接受的执行计划的一个集合。
在典型使用情况下，数据库只接受那些通过验证并执行良好的执行计划到计划基线中。



-----设置参数：启用sql_plan_baseline

show parameter optimizer_capture_sql_plan_baselines

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean	 FALSE


alter system set optimizer_capture_sql_plan_baselines=true;


show parameter optimizer_capture_sql_plan_baselines






---1.准备测试环境

create table t2
(
sid number not null ,
sname varchar2(10)
)
tablespace test;







--循环导入数据
declare
        maxrecords constant int:=20000;
        i int :=1;
    begin
        for i in 1..maxrecords loop
          insert into t2 values(i,'ocpyang');
        end loop;
    dbms_output.put_line(' 成功录入数据！ ');
    commit;
    end; 
/







exec dbms_stats.gather_table_stats('SCOTT','T2',cascade => true);





************************************************************
第二部分：为sql 调优集中的sql语句创建计划基线
************************************************************
创建基线的几种方式
1.自动捕获基线
2.从SQL调优集合中加载，通过使用包dbms_spm.load_plans_from_sqlset
3.从库缓存中加载，通过包dbms_spm.load_plans_from_cursor_cache函数为一条已经在游标缓存中的语句创建基线


----------------------------------*
方式1.自动捕获基线
----------------------------------*

--------案例演示

步骤1：简单查询

set autotrace on;
var v varchar2(5);  
exec :v :=1000;  
select   * from t2 where sid<=:v;
set autotrace off;


执行计划
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |  1000 | 12000 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2	 |  1000 | 12000 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SID"<=TO_NUMBER(:V))





步骤2：简单查询

set autotrace on;
var v varchar2(5);  
exec :v :=1000;  
select   * from t2 where sid<=:v;
set autotrace off;



步骤3：查看SQL PLAN BASELINE

SELECT sql_handle, plan_name,enabled, accepted 
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%select   * from t2 where sid<=:v%';

SQL_HANDLE		       PLAN_NAME		      ENA ACC
------------------------------ ------------------------------ --- ---
SQL_60fea6835db2e913	       SQL_PLAN_61zp6hdfv5u8mb860bcf2 YES YES



步骤4：新建索引

create index index_01 on t2(sid);

exec dbms_stats.gather_table_stats('SCOTT'