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'));