1. 通过dbms_xplan.display_cursor查看指定sql都有哪些执行计划
sql> select * from table(dbms_xplan.display_cursor(‘&sql_id’,null,’typical peeked_binds’));
2. 查询该sql的历史执行情况
sql> col snap_id for 99999999
sql> col date_time for a30
sql> col plan_hash for 9999999999
sql> col executions for 99999999
sql> col avg_etime_s heading ‘etime/exec’ for 9999999.99
sql> col avg_lio heading ‘buffer/exec’ for 99999999999
sql> col avg_pio heading ‘diskread/exec’ for 99999999999
sql> col avg_cputime_s heading ‘cputim/exec’ for 9999999.99
sql> col avg_row heading ‘rows/exec’ for 9999999
sql> select * from(
select distinct
s.snap_id,
to_char(s.begin_interval_time,’mm/dd/yy_hh24mi’) || to_char(s.end_interval_time,’_hh24mi’) date_time,
sql.plan_hash_value plan_hash,
sql.executions_delta executions,
(sql.elapsed_time_delta/1000000)/decode(sql.executions_delta,null,1,0,1,sql.executions_delta) avg_etime_s,
sql.buffer_gets_delta/decode(sql.executions_delta,null,1,0,1,sql.executions_delta) avg_lio,
sql.disk_reads_delta/decode(sql.executions_delta,null,1,0,1,sql.executions_delta) avg_pio,
(sql.cpu_time_delta/1000000)/decode(sql.executions_delta,null,1,0,1,sql.executions_delta) avg_cputime_s,
sql.rows_processed_total/decode(sql.executions_delta,null,1,0,1,sql.executions_delta) avg_row
from dba_hist_sqlstat sql, dba_hist_snapshot s
where sql.instance_number =(select instance_number from v$instance)
and sql.dbid =(select dbid from v$database)
and s.snap_id = sql.snap_id
and sql_id = trim(‘&sql_id’) order by s.snap_id desc)
where rownum <= 100;
3. 绑定执行计划
从前两步中可以看到该sql有两条执行计划，假如plan_hash_value为’2214001748’才是对的，而此时数据库选择的是另一条执行计划，我们可以通过执行以下function去将执行计划固定为我们想要的。
sql> var temp number;
sql> begin
:temp := dbms_spm.load_plans_from_cursor_cache(sql_id=>’66a4184u0t6hn’, plan_hash_value=>2214001748);
end;
/
–转自
oracle – spm固定执行计划(一)
https://www.cnblogs.com/ddzj01/p/11365541.html
oracle – spm固定执行计划(二)
https://www.cnblogs.com/ddzj01/p/11377049.html