rem ********************************************************************
rem * Filename          : %M% - Version %I%
rem * Author            : Virag S.
rem *                     John Strother, Craig Shallahamer, Mike Jondrow
rem * Original          : 17-APR-95
rem * Last Update       : %H% - %T%
rem * Description       : Load session wait information
rem * Usage             : start swdr.sql <run id>
rem ********************************************************************

spool swdr.rslt

def runid=&&1

def aps_prog    = 'swdr.sql'
def aps_title   = 'Session Wait Detail Report'
start apstitle

col rid                 heading "Run ID"        format 99999
col ev                  heading "Event"         format A24
col sn                  heading "Segment"       format A25
col type                heading "Action"        format A8
col cnt                 heading "Hits"          format 99999

break on runid,event

select b.runid rid,
       b.event ev,
       a.segment_name sn,
       a.segment_type type,
       count(*) cnt
from   sys.dba_extents a,
       aps_session_wait_log b
where  to_number(b.p1) = a.file_id and  
       to_number(b.p2) between 
              a.block_id and a.block_id + a.blocks and  
       b.event like 'db_file%' and  
       b.runid = &runid
group  by b.runid,b.event,a.segment_name,a.segment_type;

spool off;