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 SQL statements should be shown
accept HowMany
set pagesize 999
set linesize 132
column "Name/Hash Value" format a20
select * from (
select HashOrName "Name/Hash Value", execs "Executions", timesecs "Seconds of Wait", ROUND(decode(execs,0,0,timesecs/execs),4) "Avg Execution"
from (
select nvl(n.name, ss.sqlhash) HashOrName,
sum(execs) execs,
sum(timesecs) timesecs
from con_stats_sum_&DBID ss, con_sql_name n
where ss.sqlhash = n.hash 
and datehour between to_date('&BeginDate','mm/dd/yy hh24:mi') and to_date('&EndDate','mm/dd/yy hh24:mi')
group by nvl(n.name, ss.sqlhash))
order by 4 desc)
where rownum <= &HowMany
/
