SQL History Plan change

COL instance_number FOR 9999 HEA 'Inst';
COL end_time HEA 'End Time';
COL plan_hash_value HEA 'Plan|Hash Value';
COL executions_total FOR 999,999 HEA 'Execs|Total';
COL rows_per_exec HEA 'Rows Per Exec';
COL et_secs_per_exec HEA 'Elap Secs|Per Exec';
COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';
COL io_secs_per_exec HEA 'IO Secs|Per Exec';
COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';
COL ap_secs_per_exec HEA 'App Secs|Per Exec';
COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';
COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';
COL ja_secs_per_exec HEA 'Java Secs|Per Exec';

SELECT h.instance_number,
       TO_CHAR(CAST(s.end_interval_time AS DATE), 'YYYY-MM-DD HH24:MI') end_time,
       h.plan_hash_value,
       h.executions_total,
       TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
       TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
       TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
       TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
       TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
       TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
       TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
       TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
       TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec
  FROM dba_hist_sqlstat h,
       dba_hist_snapshot s
 WHERE h.sql_id = '1ptjkbswfrthv'
   AND h.executions_total > 0
   AND s.snap_id = h.snap_id
   AND s.dbid = h.dbid
   AND s.instance_number = h.instance_number
 ORDER BY
       h.sql_id,
       h.instance_number,
       s.end_interval_time,
       h.plan_hash_value
/

EL1PROD1










select sql_id, snap.dbid, snap.snap_id, begin_interval_time, end_interval_time, plan_hash_value, executions_delta,
trunc(decode(executions_delta, 0, elapsed_time_delta, elapsed_time_delta/executions_delta)*0.000001) as "ELAPSED_TIME_AVG IN SECONDS",
trunc(decode(executions_delta, 0, rows_processed_delta, rows_processed_delta/executions_delta)) rows_avg,
trunc(decode(executions_delta, 0, fetches_delta, fetches_delta/executions_delta)) fetches_avg,
trunc(decode(executions_delta, 0, disk_reads_delta, disk_reads_delta/executions_delta)) disk_reads_avg,
trunc(decode(executions_delta, 0, buffer_gets_delta, buffer_gets_delta/executions_delta)) buffer_gets_avg,
trunc(decode(executions_delta, 0, cpu_time_delta, cpu_time_delta/executions_delta)) cpu_time_avg,
trunc(decode(executions_delta, 0, iowait_delta, iowait_delta/executions_delta)) iowait_time_avg
from dba_hist_sqlstat stat, dba_hist_snapshot snap
where stat.snap_id = snap.snap_id
and stat.dbid = snap.dbid
and stat.instance_number = snap.instance_number
and sql_id ='f8hmtwdb4p3vy'
order by sql_id, snap.snap_id desc;








set serveroutput on
begin
 for c in (select address,hash_value,users_executing,sql_text from gv$sqlarea where sql_id='ffvgfm36z5dkc')
 loop
  dbms_output.put_line(c.users_executing||' users executing '||c.sql_text);
  sys.dbms_shared_pool.purge(c.address||','||c.hash_value,'...');
  dbms_output.put_line('flushed.');
 end loop;
end;
/