Scope:
This blog shared method to fetch TOP sqls from history of Oracle database which can be used to analyse the performance of database while a defined time interval.
Script:
This blog shared method to fetch TOP sqls from history of Oracle database which can be used to analyse the performance of database while a defined time interval.
 |
| Database Logical Reads |
Script:
select * from
(
select
sq.sql_id,
sum(executions_delta) executions_delta,
sum(disk_reads_delta) disk_reads_delta,
sum(buffer_gets_delta) buffer_gets_delta,
max(module) module
from dba_hist_sqlstat sq, dba_hist_snapshot sn
where
sn.dbid = (select dbid from v$database)
and sn.begin_interval_time BETWEEN to_date('19-07-2013 17:00','DD-MM-YYYY HH24:MI') AND to_date('19-07-2013 19:00','DD-MM-YYYY HH24:MI')
and sn.instance_number = sq.instance_number
and sn.snap_id = sq.snap_id
and sn.dbid = sq.dbid
and sq.disk_reads_delta is not null
group by sq.sql_id
order by 4 desc
)
where rownum <= 10
/
No comments:
Post a Comment