/* bbwsid.sql
	see the sids waiting on buffer busy waits by datafile
*/
col spid head DBProc
col fname format a25 trunc head "FileName (#)"
col filesys format a5 head MTPT 
col username format a10 trunc
col module format a15 trunc
col p1p2p3txt format a15 trunc
col sql_hash_value head SQLHASH
col LastCallET format 99,999
col status format a1 trunc head S
set verify off
select s.sid, 
	s.status,
  	floor(last_call_et/60) "LastCallET",
	p.spid,
       	s.sql_hash_value,
	s.username,
	s.module,
        w.p1 || ':' || w.p2 || ':' || w.p3 p1p2p3txt,
        substr(f.name,8,(instr(f.name,'/',9,1) -8)) FILESYS,
        substr(f.name,(instr(f.name,'/',-1,1)+1)) || ' (' || f.file# || ')' fname
from v$session_wait w, v$session s, v$process p, v$datafile f
where w.event = 'buffer busy waits'
and w.p1 = f.file# (+)
and w.wait_time = 0
and w.sid = s.sid
and p.addr = s.paddr
order by 8,5;