System waits (user, non-idle)

set linesize 250
set verify off
set pagesize 9999

COLUMN Username     	FORMAT a14			  HEADING 'User'
COLUMN Sid   			FORMAT 99999          HEADING 'Sess ID'
COLUMN blocking_session	FORMAT 99999          HEADING 'Block SID'
COLUMN sql_id   		FORMAT a14            HEADING 'SQL ID'
COLUMN sql_hash_value	FORMAT 99999999999    HEADING 'SQL Hash'
COLUMN machine   		FORMAT a14            HEADING 'Machine'
COLUMN program   		FORMAT a20            HEADING 'Program'
COLUMN last_call_et   	FORMAT 999,999,999    HEADING 'Last call ET'
COLUMN event   			FORMAT a25            HEADING 'Event name'
COLUMN sec_wait  		FORMAT 999,999,999    HEADING 'Time waited'
COLUMN state   			FORMAT a10            HEADING 'State'
COLUMN name   			FORMAT a25            HEADING 'Event details'
COLUMN wait_object_name FORMAT a25            HEADING 'Object Name'

select s.username, s.sid, s.sql_id, s.blocking_session, s.sql_hash_value, s.machine, s.program, s.last_call_et,
decode(w.event,'latch free',w.event||': '||l.name||' ',w.event) event, w.seconds_in_wait sec_wait, s.state,
decode(w.event,
'db file sequential read',substr(substr(d.name,instr(d.name,'\',-1)+1,length(d.name)),instr(substr(d.name,instr(d.name,'\',-1)+1,length(d.name)),'/',-1)+1,
length(substr(d.name,instr(d.name,'\',-1)+1,length(d.name))))||' '||w.p1text||'='||w.p1||' '||w.p2text||'='||w.p2||' '||w.p3text||'='||w.p3,
'db file scattered read' ,substr(substr(d.name,instr(d.name,'\',-1)+1,length(d.name)),instr(substr(d.name,instr(d.name,'\',-1)+1,length(d.name)),'/',-1)+1,
length(substr(d.name,instr(d.name,'\',-1)+1,length(d.name))))||' '||w.p1text||'='||w.p1||' '||w.p2text||'='||w.p2||' '||w.p3text||'='||w.p3,
w.p1text||'='||w.p1||' '||decode(w.p2text,null,'',w.p2text||'='||w.p2)||' '||decode(w.p3text,null,'',w.p3text||'='||w.p3)
) name, 
o.owner || '.' ||o.object_name wait_object_name
  from
v$session_wait w,
v$session s,
v$datafile d, v$latch l, v$process p,
dba_objects o
where w.sid = s.sid and w.p2 = l.latch#(+) and p.addr = s.paddr
and w.event not in ('Idle', 'SQL*Net message from client')
and s.type = 'USER'
and w.p1=d.file#(+)
and s.row_wait_obj# = o.object_id(+)
order by s.sql_hash_value,1
/