--------------------------------------------------------------------------------
--
-- File name:   sw.sql
-- Purpose:     Display current Session Wait info
--
-- Author:      Tanel Poder
-- Copyright:   (c) http://www.tanelpoder.com
--              
-- Usage:       @sw <sid>
--              @sw 52,110,225
-- 	        	@sw "select sid from v$session where username = 'XYZ'"
--              @sw &mysid
--
--------------------------------------------------------------------------------

col sw_event 	head EVENT for a40 truncate
col sw_p1transl head P1TRANSL for a42
col sw_sid		head SID for 999999

select 
	sid sw_sid, 
	CASE WHEN state != 'WAITING' THEN 'WORKING'
	     ELSE 'WAITING'
	END AS state, 
	CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
	     ELSE event
	END AS sw_event, 
	seq#, 
	seconds_in_wait sec_in_wait, 
	p1,
	p2,
	p3,
	CASE 
		WHEN event like 'enq%' AND state = 'WAITING' THEN 
			'0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))||': '||
			chr(bitand(p1, -16777216)/16777215)||
			chr(bitand(p1,16711680)/65535)||
			' mode '||bitand(p1, power(2,14)-1)
		WHEN event like 'latch%' AND state = 'WAITING' THEN 
			  '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))||': '||(
			  		select name||'[par' 
			  			from v$latch_parent 
			  			where addr = hextoraw(trim(to_char(p1,rpad('0',length(rawtohex(addr)),'X'))))
			   		union all
			   		select name||'[c'||child#||']' 
				   		from v$latch_children 
			  			where addr = hextoraw(trim(to_char(p1,rpad('0',length(rawtohex(addr)),'X'))))
			  )
	ELSE NULL END AS sw_p1transl
FROM 
	v$session_wait 
WHERE 
	sid IN (&1)
ORDER BY
	state,
	sw_event,
	p1,
	p2,
	p3
/