oracle-如何手动刷新执行计划plan

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

select s.SQL_TEXT, s.ADDRESS || ',' || s.HASH_VALUE from v$sqlarea s where sql_id = '5xbtt680gjvht';

2,将该执行计划从shared pool 中清除

exec sys.dbms_shared_pool.purge('0000003E5F821DE8,16313881','c');

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

select * from v$sql_plan where sql_id = 'anpn3169dqr0y';

select * from v$sql where sql_id = 'anpn3169dqr0y';

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,查询执行计划内容

set line 200 pagesize 300
select * from table(dbms_xplan.display_cursor('anpn3169dqr0y',0,'ADVANCED'));       
select * from table(dbms_xplan.display_cursor(‘anpn3169dqr0y’,0,'ALLSTATS LAST ADVANCED PEEKED_BINDS'));

参考：
http://blog.itpub.net/69993859/viewspace-2753737/