Top SQL by "Elapsed Time" reads from AWR history

This post describe the SQL to fetch "TOP SQL by Elapsed Time" from history of Oracle database which can be used to analyse the performance of database while a defined time interval.


Script:

select INST_NUMBER,to_char(begin_timestamp,'DD/MON/YYYY HH24:MI') TIME_FRAME,sql_id,Elapsed_TIME_MIN from

(

select  sn.snap_id begin_snap,  sn.instance_number INST_NUMBER,sq.sql_id SQL_ID,

sn.begin_interval_time begin_timestamp ,sn.end_interval_time end_timestamp,

sum(elapsed_time_delta/1000000/60) as Elapsed_TIME_MIN,

sum(EXECUTIONS_DELTA) EXECUTIONS,

row_number() over ( partition by sn.snap_id,sn.instance_number order by sum(elapsed_time_delta/1000000/60)  desc) AS ROWSNUMBERING

from dba_hist_sqlstat sq,dba_hist_snapshot sn

where  sq.instance_number=sn.instance_number

and sn.snap_id=sq.snap_id

and sn.begin_interval_time > sysdate-1

--and sn.begin_interval_time BETWEEN to_date('10-JUL-16 00:00','DD-MON-YY HH24:MI')   AND to_date('28-JUL-16 00:00','DD-MON-YY HH24:MI')

group by sn.instance_number ,sn.snap_id,sn.begin_interval_time , sn.end_interval_time ,sq.sql_id

)

where ROWSNUMBERING<21

and EXECUTIONS>0

order by to_char(begin_timestamp,'DD/MON/YYYY HH24:MI'),INST_NUMBER,Elapsed_TIME_MIN desc;