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'));
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69993859/viewspace-2753737/ ，如需转载，请注明出处，否则将追究法律责任。
