View history of query run time

by Granville Bonyata on December 5, 2017

SELECT TO_CHAR(s.end_interval_time, 'MM/DD/YY HH24:MI') end_interval_time,
       t.executions_delta executions,
       ROUND(T.ELAPSED_TIME_DELTA/1000000,2) elapsed_time_sec,
       ROUND((t.elapsed_time_delta/t.executions_delta)/1000000,3) elapsed_time_per_exec_sec,
       t.plan_hash_value,
       t.*
  FROM dba_hist_sqlstat t,
       dba_hist_snapshot s
 WHERE t.sql_id = 'hjd56z95jdhw'
   AND t.snap_id = s.snap_id
   AND t.executions_delta > 0
   AND t.instance_number = s.instance_number
   AND s.end_interval_time > TRUNC(SYSDATE) - 7
 ORDER BY s.end_interval_time,
          t.executions_delta
/


More here: https://docs.oracle.com/database/121/REFRN/GUID-F5A246E0-C04A-406C-9E10-AC26E7742F06.htm#REFRN23447

Previous post:

Next post: