Identify root cause session using V$WAIT_CHAINS from 11g.

From 11g, oracle provide V$WAIT_CHAINS dynamic performance view.
It’s very powerful and convenient to use. So, you can easily idenfify root cause session among the many sessions using that view. The contents of V$WAIT_CHAINS view are very similar with the section of the output of ‘hanganalyze 1’. So, you don’t need to anymore execute ‘hanganalyze’ oradebug command. Instead, you just run below query.

SELECT decode( a.blocker_sid , NULL , '<chain id#' ||a.chain_id||'>' ) chain_id,
       RPAD( '+' , LEVEL , '-' ) ||a.sid sid,
       RPAD( ' ' , LEVEL , ' ' ) ||a.wait_event_text wait_event
FROM   V$WAIT_CHAINS  a
CONNECT BY PRIOR a.sid=a.blocker_sid
AND    PRIOR a.sess_serial#=a.blocker_sess_serial#
AND    PRIOR a.instance = a.blocker_instance START WITH a.blocker_is_valid='FALSE'
ORDER  BY a.chain_id ,
       LEVEL
/
 
CHAIN_ID             SID        WAIT_EVENT
-------------------- ---------- --------------------------------------------------
<chain id#1>         +190        SQL*Net message from client
                     +-4          enq: TX - row lock contention
<chain id#2>         +221        SQL*Net message from client
                     +-108        enq: TX - row lock contention
                     +--8          enq: TX - row lock contention
<chain id#3>         +67         SQL*Net message from client
                     +-155        enq: TX - row lock contention
                     +--146        enq: TX - row lock contention
                     +-156        enq: TX - row lock contention