查看执行计划变化的SQL

 

select a.sql_id,a.exes,a.rowds,a.plan_hash_value,b.plan_hash_value,a.etimes,b.etimes from (
SELECT DISTINCT 
         a.sql_id,
         a.plan_hash_value,
         a.executions_delta exes,
         ROUND (a.elapsed_time_delta / a.executions_delta / 1000000, 2) etimes,
         a.ROWS_PROCESSED_DELTA rowds
    FROM dba_hist_sqlstat a,
            DBA_HIST_SNAPSHOT b
   WHERE a.snap_id = b.snap_id
         and  b.END_INTERVAL_TIME between sysdate-1/24 and sysdate
         AND a.executions_delta > 0) a,
(select sql_id,plan_hash_value,round((ELAPSED_TIME/EXECUTIONS)/1000000,2) etimes from v$sql) b 
where a.sql_id=b.sql_id
and a.plan_hash_value<>b.plan_hash_value;