--------------------------------------------------------------------------------

--

-- 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

--

-- Version - Coskan Gundogar :

--  add extra columns by joining with v$session like  sql_id child number blocking session username

--  buffer busy and read by another session events related info

--  

--------------------------------------------------------------------------------

col sw_event         head EVENT for a40 truncate

col sw_p1transl head P1TRANSL for a42

col sw_sid                head SID for 999999

col sw_p1       head P1 for a25 justify right word_wrap

col sw_p2       head P2 for a25 justify right word_wrap

col sw_p3       head P3 for a25 justify right word_wrap

col chn format 999

col blckng_sid format 9999999

col blckng_inst format a12

col blckng_sid_status format a12

col username format a5

WITH W as(

 select rownum class#, class from v$waitstat)

select

        sw.sid sw_sid,substr(username,1,5) username,

        CASE WHEN sw.state != 'WAITING' THEN 'WORKING'

             ELSE 'WAITING'

        END AS state,

        CASE WHEN sw.state != 'WAITING' THEN 'On CPU / runqueue'

             ELSE sw.event

        END AS sw_event,

        sw.seq#,

        sw.seconds_in_wait sec_in_state, s.sql_id,s.sql_child_number chn,

        NVL2(sw.p1text,sw.p1text||'= ',null)||CASE WHEN sw.P1 < 536870912 THEN to_char(sw.P1) ELSE '0x'||rawtohex(sw.P1RAW) END SW_P1,

        NVL2(sw.p2text,sw.p2text||'= ',null)||CASE WHEN sw.P2 < 536870912 THEN to_char(sw.P2) ELSE '0x'||rawtohex(sw.P2RAW) END SW_P2,

        NVL2(sw.p3text,sw.p3text||'= ',null)||CASE WHEN sw.P3 < 536870912 THEN to_char(sw.P3) ELSE '0x'||rawtohex(sw.P3RAW) END SW_P3,

        CASE

                WHEN sw.event like 'cursor:%' THEN

                    '0x'||trim(to_char(sw.p1, 'XXXXXXXXXXXXXXXX'))

                WHEN sw.event like 'enq%' AND sw.state = 'WAITING' THEN

                    '0x'||trim(to_char(sw.p1, 'XXXXXXXXXXXXXXXX'))||': '||

                    chr(bitand(sw.p1, -16777216)/16777215)||

                    chr(bitand(sw.p1,16711680)/65535)||

                    ' mode '||bitand(sw.p1, power(2,14)-1)

                WHEN sw.event like 'enq%' AND sw.state = 'WAITING' THEN

                        '0x'||trim(to_char(sw.p1, 'XXXXXXXXXXXXXXXX'))||': '||

                        chr(bitand(sw.p1, -16777216)/16777215)||

                        chr(bitand(sw.p1,16711680)/65535)||

                        ' mode'||bitand(sw.p1, power(2,14)-1)

                WHEN (sw.event like 'buffer busy%' or sw.event like 'read by %') AND sw.state='WAITING' THEN

                (select w.class from  w where  w.class#(+)=sw.p3)|| (select '  obj='||object_name||' type='||object_type from dba_objects o where o.objecT_id(+)=s.ROW_WAIT_OBJ#)

                WHEN sw.event like 'latch%' AND sw.state = 'WAITING' THEN

                          '0x'||trim(to_char(sw.p1, 'XXXXXXXXXXXXXXXX'))||': '||(

                                          select name||'[par'

                                                  from v$latch_parent

                                                  where addr = hextoraw(trim(to_char(sw.p1,rpad('0',length(rawtohex(addr)),'X'))))

                                           union all

                                           select name||'[c'||child#||']'

                                                   from v$latch_children

                                                  where addr = hextoraw(trim(to_char(sw.p1,rpad('0',length(rawtohex(addr)),'X'))))

                          )

                WHEN sw.event like 'library cache pin' THEN

                         '0x'||RAWTOHEX(sw.p1raw)

        ELSE NULL END AS sw_p1transl,blocking_session blckng_sid,blocking_session_status blckng_sid_status,blocking_instance blckng_inst

FROM

        v$session_wait sw,v$session s

WHERE

        sw.sid IN (&1)

        and sw.sid=s.sid

ORDER BY

        state,

        sw_event,

        sql_id,

        SW_P1,

        sql_id,

        SW_P2,        

        SW_P3

/