基于成本的优化法则基于统计信息找到最优执行计划,但是一些环境的变化可能影响执行计划的改变如：
· New optimizer version
· Changes to optimizer statistics and optimizer parameters
· Changes to schema and metadata definitions
· Changes to system settings
· SQL profile creating
执行计划的变化有两个方向，更好的性能和更差的性能, 11g以前为了保证执行计划的稳定性通过Stored Outlines和锁定统计信息,但是这样屏蔽了优化的一些新的特性和更优的执行计划,11g SPM很好的解决了始终保证执行计划朝着更好的性能发展。SPM自动管理执行计划，当一个新的执行计划被发现,不是立即被使用,而是被确保被验证该计划比历史执行计划有着更好的性能才被使用。
1. SQL Plan Baselines 捕获
自动捕获
设置参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 为true
被开启后系统自动创建并维护执行计划历史记录，执行计划历史包含sql语句,绑定变量,编译环境等,第一次生成的执行计划被标记为accepted状态,代表执行计划历史和sql plan baseline。后来生成的执行计划被加入执行计划历史被标记为non-accepted状态,直到被验证不会带来性能下降。
手动捕获
从SQL Tuning Sets 和 AWR Snapshots加载
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 => '99twu5t2dn5xd');
END;
/
2. SQL Plan Baselines选择
1 OPTIMIZER_USE_SQL_PLAN_BASELINES 设置为 TRUE
2 在sql编译过程中,优化器基于成本的优化法则首先构建一个最优执行计划，然后去sql plan baseline找到匹配的计划,如果能找到就使用该执行计划,如果找不到优化器将要评估sql plan baseline中状态为accepted状态的计划，找到一个成本最低的来执行,并新生成的执行计划加入到sql plan baseline中标记为non-accepted状态，不能被使用,直到被验证不会引起性能下降后被标记为accepted后才可以使用。
3. SQL Plan Baselines演化
Sql plan从non-accepted到accepted的过程,手动演化的语句如下:
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;
/
12c引入了SYS_AUTO_SPM_EVOLVE_TASK自动作业verification non-accepted状态的sql plan
4. SQL Plan Baselines 与 the SQL Tuning Advisor
当与SQL Tuning Advisor优化SQL语句，如果找到一个调整计划，并验证其性能要比从相应的SQL plan baseline选择了一个更好的计划，它使一个建议，接受SQL profile。当SQL profile被接受，调整计划被添加到相应的SQL plan baseline。
5. 查看SQL Plan Baselines
select sql_handle, sql_text, plan_name, origin,
enabled, accepted, fixed, autopurge
from dba_sql_plan_baselines;
select * from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>'SYS_SQL_209d10fabbedc741',
format=>'basic'));
6. Database upgrade与SQL Plan Management
方式一
使用SQL Tuning Sets
可以选择(cursor cache, workload repository或者其他SQL Tuning Sets)创建一个新的SQL Tuning Sets
BEGIN
SYS.DBMS_SQLTUNE.CREATE_SQLSET (
sqlset_name => 'SPM_STS',
description => '10g plans');
END;
\
从cursor cache填充SQL Tuning Sets
DECLARE
stscur dbms_sqltune.sqlset_cursor;
BEGIN
OPEN stscur FOR
SELECT VALUE(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(
‘parsing_schema_name <> ‘‘SYS’’’,
null, null, null, null, 1, null, 'ALL')) P;
-- populate the sqlset
dbms_sqltune.load_sqlset(sqlset_name => 'SPM_STS',
populate_cursor => stscur);
END;
/
加载到SPM
SQL> Variable cnt number
SQL> execute :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
sqlset_name => 'SPM_STS');
方式二
从 Stored Outlines导入SPM
1 alter system set CREATE_STORED_OUTLINES=OLDPLAN;
2 执行应用sql或者开启应用
3 alter system set CREATE_STORED_OUTLINES=false;
4 导出exp outln/outln file=soutline.dmp owner=outln rows=y
5 导入SPM
variable report clob;
exec :report:=DBMS_SPM.MIGRATE_STORED_OUTLINE( -
attribute_name=>'OUTLINE_NAME', attribute_value =>
'OLDPLAN');
方式三
如果升级前没有捕获执行计划,可以选择此方式
首先设置参数OPTIMIZER_FEATURES_ENABLE到10g,然后捕获10g sql plan,然后再加载到SPM,最后再设置OPTIMIZER_FEATURES_ENABLE为11g
注意:捕获的执行计划需要使用10g的统计信息 详细流程如下图
7. SQL 管理库策略
Sql plan base 相关信息存储在SQL Management Base，是数据库字典的一部分,存储在sysaux表空间，默认最大限制是sysaux表空间的10%可以通过以下方式更改限制值
BEGIN
DBMS_SPM.CONFIGURE(
'space_budget_percent',30);
END;
/
也可以设置没有使用的sql plan保留时间,默认是53周，plan_retention_weeks的范围值是5-523
可以通过以下方式进行调整
BEGIN
DBMS_SPM.CONFIGURE(
'plan_retention_weeks',105);
END;
/
配置参数查询
select parameter_name, parameter_value from dba_sql_management_config;
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 30
PLAN_RETENTION_WEEKS 105
