Description
This custom report will list the Top N SQL statements in your instance by number of disk reads. To use this report, run it from SQL*Plus while connected to the Ignite repository database.
SQL*Plus Script
select id, name from cond order by name;
prompt Which Database ID
accept DBID
prompt Enter Start Date (mm/dd/yy hh24:mi)
accept BeginDate
prompt Enter End Date (mm/dd/yy hh24:mi)
accept EndDate
prompt How many SQLs should be displayed
accept HowMany
set pagesize 999
set linesize 132
column hash_or_name format a22
select * from (
select nvl(n.name, ss.sqlhash) hash_or_name, round(sum(dreads)/sum(ss.timesecs),0) disk_io_sec, sum(ss.timesecs) timesecs,
sum(dreads) disk_reads, sum(execs) executions, sum(row_count) row_count
FROM con_stats_sum_&DBID ss, con_sql_name n
where ss.datehour between to_date('&BeginDate','mm/dd/yy hh24:mi') and to_date('&EndDate','mm/dd/yy hh24:mi')
and ss.sqlhash = n.hash 
group by nvl(n.name, ss.sqlhash)
having sum(ss.timesecs) > 0
order by 2 desc)
where rownum <= &HowMany
order by timesecs desc
/
