Blocking wait chains

The v$wait_chains view is populated by the dia0 background processes which collects hanganalyze information and stores this in memory in the hang analysis cache. It collects this information on the local instance every 3 seconds and in a RAC environment it is collected every 10 seconds. This means that the v$wait_chains does not populate immediately on a blocking session, but may take a few seconds. v$wait_chains also does NOT have a gv$wait_chains equivalent but the v$ view holds RAC session information.

One use of v$wait_chains is to find out blocking wait chains. I took a query from MOS and modified it slightly - Troubleshooting Database Contention With V$Wait_Chains (Doc ID 1428210.1)

set line 200 col blkr_sid for a8 col blkr_ser for a8 col wait_event for a35 col FBLOCKER_PROC for a20 SELECT wc.chain_id, wc.sid, wc.sess_serial#, i.inst_id, decode(blocker_sid,null,'',blocker_sid) blkr_sid, decode(BLOCKER_SESS_SERIAL#,null,'',BLOCKER_SESS_SERIAL#) blkr_ser, blocker_instance blkr_inst, 'SID-'||decode(s.final_blocking_session,null,'<>', s.final_blocking_session) ||'-Inst# '||decode(s.final_blocking_instance,null,'<>', s.final_blocking_instance) FBLOCKER_PROC, wait_event_text wait_event, in_wait_secs Seconds FROM v$wait_chains wc, gv$session s, gv$session bs, gv$instance i, gv$process p WHERE wc.instance = i.instance_number (+) AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+) AND wc.sess_serial# = s.serial# (+)) AND (s.final_blocking_instance = bs.inst_id (+) and s.final_blocking_session = bs.sid (+)) AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+)) AND ( num_waiters > 0 OR ( blocker_osid IS NOT NULL AND in_wait_secs > 10 ) ) CONNECT BY PRIOR wc.sid = blocker_sid AND PRIOR wc.sess_serial# = blocker_sess_serial# AND PRIOR i.inst_id = blocker_instance START WITH blocker_is_valid = 'FALSE';

In a session in the database i create a lock on a row in a table, and in two other sessions i try to update the same row

SQL> delete from wait_chain_test where col1 = 2; 1 row deleted. {another session} SQL> update wait_chain_test set col1 = 3 where col1 = 2; {and another session} SQL> update wait_chain_test set col1 = 3 where col1 = 2;

Now I can run the query above on v$wait_chains to see the blocking tree

CHAIN_ID SID SESS_SERIAL# INST_ID BLKR_SID BLKR_SER BLKR_INST FBLOCKER_PROC WAIT_EVENT SECONDS ---------- ---------- ------------ ---------- -------- -------- ---------- -------------------- ----------------------------------- ---------- 1 11 64484 1 SID-<>-Inst# <> SQL*Net message from client 6115 1 10 4090 1 11 64484 1 SID-11-Inst# 1 enq: TX - row lock contention 168 2 24 45346 1 11 64484 1 SID-11-Inst# 1 enq: TX - row lock contention 7649

If we add a different set of blocking on the same table.

SQL> update wait_chain_test set col1 = 99 where col1 = 1; 1 row updated. {another session} SQL> update wait_chain_test set col1 = 88 where col1 = 1;

Running the same query on v$wait_chains shows the new blocking chain

CHAIN_ID SID SESS_SERIAL# INST_ID BLKR_SID BLKR_SER BLKR_INST FBLOCKER_PROC WAIT_EVENT SECONDS ---------- ---------- ------------ ---------- -------- -------- ---------- -------------------- ----------------------------------- ---------- 1 11 64484 1 SID-<>-Inst# <> SQL*Net message from client 6362 1 10 4090 1 11 64484 1 SID-11-Inst# 1 enq: TX - row lock contention 415 3 24 45346 1 11 64484 1 SID-11-Inst# 1 enq: TX - row lock contention 7896 2 378 52791 1 SID-<>-Inst# <> SQL*Net message from client 3907 2 12 43253 1 378 52791 1 SID-378-Inst# 1 enq: TX - row lock contention 112

******************************************
keywords: wait chains blocking tree
******************************************
rdbms version: 11gR2
******************************************