Oracle - SPM固定執行計劃

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