DESCRIPTION
This custom report will list the Top N Objects (tables/indexes) for a specified wait event. This will only work for wait events that have objects associated with them in the P1/P2 values, e.g. db file sequential read, etc. 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 objects should be displayed
accept HowMany
prompt Enter the wait event name
accept EventName
set pagesize 999
set linesize 132
column object_name format a80
select object_name, timesecs from (
select nvl(obj.name, 'N/A') object_name, sum(sw.qp/100) timesecs
FROM consw_&DBID sw, conobj_&DBID obj, conev_&DBID ev
where sw.d between to_date('&BeginDate','mm/dd/yy hh24:mi') and to_date('&EndDate','mm/dd/yy hh24:mi')
and sw.hgob = obj.id 
and sw.keeq = ev.id
and upper(ev.name) = upper('&EventName')
group by nvl(obj.name, 'N/A')
order by sum(sw.qp/100) desc)
where rownum <= &HowMany
/