Oracle如何手动刷新执行计划

1.根据sql_id查询sql 内存地址及哈希值

s elect s.SQL_TEXT, s.ADDRESS || ',' || s.HASH_VALUE from v$sqlarea s where sql_id = '5z2gcdv62mwuv' ；

2.将该执行计划从shared pool 中消除

exec sys.dbms_shared_pool.purge('00000024792EE028,93546278','c');

3.查询现在的该sql的执行计划

select * from v$sql_plan where sql_id = '2qphk9c83y57m';

  4.查询sql的历史的执行计划

select s.begin_interval_time,

       s.end_interval_time,

       q.snap_id,

       q.dbid,

       q.sql_id,

       q.plan_hash_value,

       q.optimizer_cost,

       q.optimizer_mode

  from dba_hist_sqlstat q, dba_hist_snapshot s

 where q.sql_id = '&SQL'

   and q.snap_id = s.snap_id

 order by s.snap_id desc;

5.查询执行计划内容

select * from table(dbms_xplan.display_cursor('',null,'ADVANCED'));       --allstats lastselect * from table(dbms_xplan.display_cursor(' 9twgdt8qut16j '));

select * from table(dbms_xplan.display_ awr ( ' bfu4f625xjvra ' ,null,'ADVANCED'));