This post describe the SQL to fetch TOP SQL by Physical Reads from Historical per AWR Snapshots from history of Oracle database which can be used to analyse the performance of database while a defined time interval.
Script:
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(disk_reads_delta) AS disk_reads_delta,
row_number() over ( partition BY sn.snap_id,sn.instance_number order by SUM(disk_reads_delta) 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 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
ORDER BY sn.snap_id,
sn.instance_number,
disk_reads_delta DESC,
sq.sql_id;
No comments:
Post a Comment