Lock Chains 01: V$SESSION

(Translated by ChatGPT on 2025/11/22)

I often get a call from a colleague saying that an application session is running slowly. In most cases, the session is not actually slow — it is simply being blocked by another session.

Starting with Oracle 10g, a new column was added to the V$SESSION performance view that reports the instance number and session ID of the blocking session. This makes troubleshooting much easier.
However, after querying V$SESSION, you still need to scroll through the result set to match each blocked session with its blocker.

Since the relationship between blocking and blocked sessions is hierarchical — for example, session 123 blocks sessions 234 and 345, and session 345 then blocks session 999 — we can use a hierarchical query to represent the lock chain more clearly.

Note: The following statements are not RAC-aware yet. They work on a single instance only.

col path_state for a30
col path_event for a160
col path_sid for a20
col b_session for 999999
set lines 230
set pages 25
with
  snap as (
   select
        sid
      , blocking_instance
      , blocking_session
      , sql_id
      , prev_sql_id
      , event
      , state
    from
      v$session)
select
    to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') now
  , CONNECT_BY_ISCYCLE cycle
  , CONNECT_BY_ROOT sid b_session
  , SID l_session
  , SUBSTR(SYS_CONNECT_BY_PATH(
        DECODE(state,
                     'WAITING', sid||'/'||nvl(sql_id, prev_sql_id||'(p)')||'/'||event,
                                sid||'/'||nvl(sql_id, prev_sql_id||'(p)')||'/ON CPU'), ' -> ')
          ,5) path_event
 from
  snap
where
  level >1
connect by
  nocycle prior sid=blocking_session
/

The result is like this.

Picture 01 - Click to enlarge

Picture 02 - Click to enlarge

The output looks like this (see Picture 01 and Picture 02 in the original article).

Several things may seem strange at first glance:

##### Picture 01

Some sessions are blocking each other. Their CYCLE column is set to 1. Both are waiting for a “read by other session” event. This looks like a deadlock, yet there is no trace file containing the famous:

ORA-00060: deadlock detected while waiting for resource

Another interesting detail is the wait event "enq: TX – row lock contention". The highlighted session 2330 appears to be waiting for itself, which is doing… nothing.
In blue, you can see a session waiting for “read by other session” depending on a session that is reading the same block.

##### Picture 02

Here you can see several sessions (in red) locked by a session that is doing… nothing (in orange).
In the next post, I will show a similar statement that can be used on V$ACTIVE_SESSION_HISTORY.

How to read the output

1. CYCLE indicates whether a deadlock exists. If the value is 1, you have a cycle in the lock chain (as in Picture 01).

2. B_SESSION is the blocking session (the “head”) and L_SESSION is the blocked session (the “tail”).

3. PATH_EVENT shows the chain of sessions involved. It includes the SID, the SQL_ID, and the wait event if the session is waiting. Otherwise, the wait event is replaced with ON CPU.

4. If SQL_ID is NULL, the query tries to use PREV_SQL_ID. In this case, a (p) suffix is added (e.g., 2sbqfhnjz6ybw(p)).
   If both SQL_ID and PREV_SQL_ID are NULL, only (p) is shown (as in B_SESSION 1401 in Picture 02).