这种方法只适合sql本身有更好的执行计划，不能绑定自己构造的执行计划（比如加hint），并且每次只能针对一个sql_id，如果慢sql未使用绑定变量导致有很多类似sql最好从索引、sql改写等方面优化。
首先找到慢sql的sql_id，查看其各执行计划平均执行时间
-- 可用v$active_session_history，dba_hist_active_sess_history时间范围较长
select SQL_PLAN_HASH_VALUE,round(avg(RUN_MINS),2) as avg_run_mins,count(*) from
(
SELECT T.SQL_ID, T.SQL_EXEC_ID,
CAST(MAX(T.SAMPLE_TIME) AS DATE) EXEC_END_TIME,
T.SQL_EXEC_START EXEC_START_TIME,
ROUND((CAST(MAX(T.SAMPLE_TIME) AS DATE) - T.SQL_EXEC_START) * 1440, 2) RUN_MINS,
T.SQL_PLAN_HASH_VALUE, T.MODULE
FROM dba_hist_active_sess_history T
WHERE T.SQL_ID = '5w91hk4nmcmrx'
AND T.SAMPLE_TIME > SYSDATE - 36
GROUP BY T.SQL_ID, T.SQL_EXEC_ID, T.SQL_EXEC_START, T.SQL_PLAN_HASH_VALUE, T.MODULE
ORDER BY EXEC_END_TIME DESC
)
group by SQL_PLAN_HASH_VALUE
order by 2 desc;
一、 从缓存中载入
查询所需执行计划是否在缓存中，如果在，直接从缓存载入更为简单
select sql_text,
sql_id,
hash_value,
child_number,
plan_hash_value,
to_char(LAST_ACTIVE_TIME, 'hh24:mi:ss') time
from v$sql a
where sql_id='26kqp5puukbh8' and plan_hash_value='3059001790';
从库缓存中载入（时间太久可能已不在缓存中）
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '5w91hk4nmcmrx',plan_hash_value=> '3570344087');
END;
/查看载入后信息
SELECT * FROM dba_sql_plan_baselines where origin='MANUAL-LOAD' order by created desc;
固定执行计划，将该基线转为fixed
DECLARE
i NATURAL;
BEGIN
i := dbms_spm.alter_sql_plan_baseline(
'SQL_3ebb770da822a759',
'SQL_PLAN_3xfvr1qn259ut58e43372',
attribute_name => 'FIXED',
attribute_value => 'YES');
dbms_output.put_line(i);
END;
/
再次查看
SELECT * FROM dba_sql_plan_baselines where origin='MANUAL-LOAD' order by created desc;
查看基线中的执行计划
select * from table(dbms_xplan.display_sql_plan_baseline('SQL_3ebb770da822a759', plan_name => 'SQL_PLAN_3xfvr1qn259ut58e43372',format => 'ADVANCED'));删除方法如下
SELECT * FROM dba_sql_plan_baselines where origin='MANUAL-LOAD' order by created desc;
DECLARE
v_text PLS_INTEGER;
BEGIN
v_text := DBMS_SPM.drop_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9',plan_name => NULL);
DBMS_OUTPUT.put_line(v_text);
END;
/二、 利用sqlset从AWR中载入
如果好的执行计划已不在缓存中，只能尝试从AWR中载入。这个方法保留的执行计划时间会比较长，当然也不是永久的，也有可能会查不到。
查看期望的执行计划产生的时间
select * from dba_hist_sql_plan where sql_id='26kqp5puukbh8' and plan_hash_value='3059001790' order by timestamp desc;
-- 2018/2/28 17:32:03
根据产生时间找到对应的快照ID
select * from dba_hist_ash_snapshot d where d.BEGIN_INTERVAL_TIME like '28-FEB-18%' order by d.BEGIN_INTERVAL_TIME;
--24257和24258（看END_INTERVAL_TIME字段）
查看数据库中已有dba_sqlset
select owner, name, id, created, statement_count from dba_sqlset order by created;
创建sqlset
begin
DBMS_SQLTUNE.CREATE_SQLSET('mysts180104','SQL Tuning Set for loading plan into SQL Plan Baseline');
end;
/指定快照号从awr中将执行计划load进sqlset
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository
(begin_snap=>24257, --老执行计划起始的snap id
end_snap=>24258, --老执行计划结束的snap id
basic_filter=>'sql_id = ''26kqp5puukbh8''', --老执行计划起始的sql id
attribute_list=>'ALL')
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'mysts180306', populate_cursor=>cur);
CLOSE cur;
END;
/load 完之后检查sqlset情况，发现已经存在（如果为空可以把snap范围加大一点）
SELECT first_load_time,executions as execs,parsing_schema_name,elapsed_time / 1000000 as elapsed_time_secs,cpu_time / 1000000 as cpu_time_secs,buffer_gets,disk_reads,direct_writes,rows_processed,fetches,optimizer_cost,sql_plan,plan_hash_value,sql_id,sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'mysts180306'));
查看sqlset中的执行计划
set long 999999999
set line 1000
set pages 1000
SELECT * FROM table (DBMS_XPLAN.DISPLAY_SQLSET('mysts180306','26kqp5puukbh8'));
-- 可以看到其中的Plan hash value值
Plan hash value: 1734317001
...
Plan hash value: 2335232284
...
Plan hash value: 3059001790 <-- 绑定的执行计划从sqlset中载入基线
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'mysts180306',
basic_filter=>'plan_hash_value = ''3059001790''');
END;
/检查创建后信息
SELECT * FROM dba_sql_plan_baselines where origin='MANUAL-LOAD' order by created desc;
将该基线转为fixed
DECLARE
i NATURAL;
BEGIN
i:=dbms_spm.alter_sql_plan_baseline(
'SQL_3ebb770da822a759',
'SQL_PLAN_3xfvr1qn259ut58e43372',
attribute_name => 'FIXED',
attribute_value => 'YES');
dbms_output.put_line(i);
END;
/查看固定后信息
SELECT * FROM dba_sql_plan_baselines where origin='MANUAL-LOAD' order by created desc;
查看基线中的执行计划
select * from table(dbms_xplan.display_sql_plan_baseline('SQL_3ebb770da822a759', plan_name => 'SQL_PLAN_3xfvr1qn259ut58e43372',format => 'ADVANCED'));
-- 可以看到
-- Plan name: SQL_PLAN_3xfvr1qn259ut58e43372 Plan id: 1491350386
-- Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD三、 将指定sql执行计划清出缓存
如果awr中都没有好的执行计划信息了，可以赌一把把当前缓存sql_id的执行计划清出去，让它重新解析看看能不能生成回正确的执行计划。如果是参数嗅探导致的执行计划改变问题，这样有可能是可以的。
col SQL_TEXT format a35
col ADDRESS format a18
col HASH_VALUE format a10
select s.SQL_TEXT, s.ADDRESS, s.HASH_VALUE||'' from v$sqlarea s where sql_text like 'select /*gg*/count(*) from test%';
SQL_TEXT ADDRESS S.HASH_VALUE||''
----------------------------------- ------------------ --------------------------------
select /*gg*/count(*) from test 0000000300B06D70 728448230
--清除该sql执行计划
exec sys.dbms_shared_pool.purge('0000000300B06D70,728448230','c');
-- 再次查询，一般无输出结果，除非sql执行频率特别高
select s.SQL_TEXT, s.ADDRESS, s.HASH_VALUE||'' from v$sqlarea s where sql_text like 'select /*gg*/count(*) from test%';