oracle使用SPM控制执行计划

一 SPM介绍

Oracle在11G中推出了SPM（SQL Plan management）,SPM是一种主动的稳定执行计划的手段，能够保证只有被验证过的执行计划才会被启用，当由于种种原因（比如统计信息的变更）而导致目标SQL产生了新的执行计划后，这个新的执行计划并不会被马上启用，直到它已经被我们验证过其执行效率会比原先执行计划高才会被启用。

SQL plan baseline中的列ENABLED和ACCEPTED用来描述一个SQL plan baseline所对应的执行计划是否被oracle启用，只有enanled和accepted的值均为yes的SQL plan baseline所对应的执行计划才会被oracle启用，如果一个SQL有超过1个以上的SQL plan baseline的enabled和accepted的值均为yes，则oracle会从中选择成本值最小的一个所对应的执行计划来作为该SQL的执行计划。

当某个sql选择了较慢的执行计划，可以创建一个计划基线，绑定较优执行计划。

注意：SPM需要维护计划基线，这可能会占用一定的数据库资源。因此，在配置SPM时，需要权衡性能稳定性和资源消耗之间的关系。

二 SPM实践

有个慢sql有时执行快，有时执行慢，这里计划为其创建一个计划基线，指定较优执行计划。

2.1检查参数

show parameter OPTIMIZER_USE_SQL_PLAN_BASELINES

#确保结果为查询true

2.2 手动生成SPM

2.2.1确定较优执行计划的plan hash value

SELECT INST_ID,SQL_ID,SQL_TEXT,SQL_FULLTEXT,PLAN_HASH_VALUE,EXECUTIONS,CPU_TIME,ROUND(ELAPSED_TIME/1000,2) as TOTAL_ELAPSED_TIME_S,

CASE  WHEN EXECUTIONS = 0 THEN ROUND(ELAPSED_TIME/1000/1,2) ELSE  ROUND(ELAPSED_TIME/1000/EXECUTIONS,2) END AS AVG_ELAPSED_TIME_S,

FIRST_LOAD_TIME,LAST_LOAD_TIME,LAST_ACTIVE_TIME,DISK_READS,DIRECT_WRITES,DIRECT_READS,BUFFER_GETS,PARSING_USER_ID,PARSING_SCHEMA_NAME,ADDRESS,HASH_VALUE,MODULE,ACTION

FROM GV$SQL

WHERE  SQL_TEXT  NOT  LIKE '%SELECT COMMAND_TYPE,SQL_ID,SQL_TEXT,SQL_FULLTEXT,EXECUTIONS%'

 AND PARSING_SCHEMA_NAME NOT LIKE '%SYS%' 

 AND PARSING_SCHEMA_NAME NOT IN ('DBSNMP')

 AND SQL_ID='sqlid值'

ORDER BY TOTAL_ELAPSED_TIME_S ASC;

可以看到，第一行执行最快，决定采用这个的执行计划。

2.2.2 建立基线

命令：

var temp number;

exec :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id =>'sqlid值',plan_hash_value =>plan hash value值);

这里是：

var temp number;

exec :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id =>'cbtfh4rvsc45d',plan_hash_value =>2595035735);

2.2.3 查看基线

select sql_handle,plan_name,origin,enabled,accepted,sql_text,created from dba_sql_plan_baselines where sql_text like '%sql文本%';

/*

如果后面需要删除该基线，这这样删除：

删除基线命令示例：

exec :temp:=dbms_spm.drop_sql_plan_baseline(sql_handle =>'SQL_62dd675eafd1bf34',plan_name =>'SQL_PLAN_65rb7burx3gtn30e445f2');

*/