oracle 为sql寻找更好的执行计划并绑定

这种方法只适合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%';