脚本小心使用,有性能问题,若有疑问,请留言。
select stat.sql_id,
PARSING_SCHEMA_NAME SCHEMA,
max(stat.PLAN_HASH_VALUE) PLAN1,
min(stat.PLAN_HASH_VALUE) PLAN2
from dba_hist_snapshot snap,
dba_hist_sqlstat stat,
v$instance inst,
v$database dbs
where snap.dbid = stat.dbid
and snap.instance_number = stat.instance_number
and snap.snap_id = stat.snap_id
and snap.dbid = dbs.dbid
and snap.instance_number = inst.instance_number
and snap.BEGIN_INTERVAL_TIME >= sysdate - 1
group by stat.sql_id, PARSING_SCHEMA_NAME
having max(stat.PLAN_HASH_VALUE) <> min(stat.PLAN_HASH_VALUE);