DESCRIPTION
This custom report will list the top P1 and P2 values for a SQL statement. This is often used with SQL statements waiting on disk reads such as db file sequential read, db file scattered read, etc. It can often be useful for SQLs that wait on latching issues as well. To use this report, run it from SQL*Plus while connected to the Ignite repository database.
SQL*PLUS SCRIPT
set linesize 132
set pages 999
column name format a50
select id, name from cond order by name
/
prompt Enter DBID from the above list:
accept DBID
prompt Enter Start Date (mm/dd/yy hh24:mi):
accept StartDate
prompt Enter End Date (mm/dd/yy hh24:mi):
accept EndDate
prompt Enter Hash Value:
accept SQLHash
prompt Number of Top P1 and P2 to display:
accept HowMany
column event format a40
select event, p1, p2, wait_time
from (
select ev.name event, sw.kxpi p1, sw.idsq p2, sum(sw.qp)/100 wait_time
from consw_&DBID sw, conev_&DBID ev
where sw.keeq = ev.id
and sw.izho = &SQLHash
and sw.d between to_date('&StartDate', 'mm/dd/yy hh24:mi') and to_date('&EndDate', 'mm/dd/yy hh24:mi')
and ev.name <> 'CPU'
group by ev.name, sw.kxpi, sw.idsq
order by 4 desc) inner
where rownum <= &HowMany
order by wait_time desc, p1, p2
/