mysql 固定执行计划,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