Database Reference
In-Depth Information
Essentially, v$wait_chains stores wait-for-graph as a chain and then identifies if there is any cycle in the chain.
The usefulness of this view stems from the fact that locking contention is usually very complex in a high-end production
environment. You are looking for just one blocker to terminate so that the application can continue processing
normally. The following SQL statement queries v$wait_chains and prints the waits in a tree format to identify the
blockers quickly.
col ses format a15
set lines 180
WITH blocked AS
(SELECT * FROM
(SELECT instance, sid, sess_serial#, blocker_instance,
blocker_sid, blocker_sess_serial#, level lv,
num_waiters, blocker_chain_id
FROM v$wait_chains
CONNECT BY PRIOR sid = blocker_sid
AND PRIOR sess_serial# = blocker_sess_serial#
AND PRIOR INSTANCE = blocker_instance
START WITH blocker_is_valid = 'FALSE'
)
WHERE num_waiters >0 OR blocker_sid IS NOT NULL
)
SELECT instance,
LPAD(' ', 2*(lv-1)) ||b.sid ses, b.sess_serial#,
b.blocker_instance, b.blocker_sid, b.blocker_sess_serial#
FROM blocked b
/
Blocker Blocker Blocker
INSTANCE SES Serial# Instance sid serial#
---------- --------------- ------- ---------- ------- ----------
1 10127 43405
1 6374 27733 1 10127 43405
1 1421 24393 1 6374 27733
The preceding output shows a wait-for-graph of 1421 → 6374 → 10127. So, we may have to terminate
session 10127 to clear the locking contention.
Hanganalyze
Hanganalyze can also be used to identify chains. The hanganalyze command essentially goes through chain of waiters
to identify if there is a process blocking all other sessions. In RAC, you need to take concurrent hanganalyze dumps
using -g all option, as shown in the following.
SQL> oradebug setmypid
SQL> oradebug -g all hanganalyze 10
Hang Analysis in /u01/app/product/rdbms/diag/cdb12/cdb12_diag_11019.trc
The DIAG process will generate trace file wait cycle information. The following trace file shows a sample of a
DIAG trace file.
 
Search WWH ::




Custom Search