Lock Chains 01: V$SESSION

I always receive a call where a colleague tell me that a session of an application is slow.  Really that session is not slow but locked by another one.

Starting with 10g, Oracle introduced a new column in the V$SESSION performance view, that report the instance number and the session id of the blockers. In this way, the troubleshooting is more simple.

Anyway, after you query the V$SESSION you have to scroll down the result set in order to match the blocking session with the locked session.

Because the relation between the blocker and locked sessions is of "hierarchical" type, I can use the hierarchical query (session 123 is blocking the session 234 and session 345. This last one also is blocking the session 999, for example) to try to picture this relation.

*****
***** Please pay attention. The following statements are not RAC aware yet. So they work just on a single instance *****
*****

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

Picture 02 - Click for enlarge

There are a lot of things that seem to be strange.


##### Picture 01

There are sessions that lock each other. They have the column "CYCLE" to 1. Both of them are waiting for "read by other sessions" event. This is strange because it seems to be a deadlock, but there isn't a trace file containing the "ORA-00060: deadlock detected while waiting for resource".

Other curious things are the "enq: TX - row lock contention". In green, I highlighted this. The session 2330 is waiting for the session 2330 that doing...nothing.

 In blue, instead, you can see how a session waiting for "read by other session" depend from a session is reading the same block. 

##### Picture 02

Here you can see how some sessions (in red) are locked by a session that did...nothing (in orange).

In the next post, I'll show a similar statement that you can use on V$ACTIVE_SESSION_HISTOR.

Note: How to read the output

1. The column CYCLE, tell you if you have a deadlock (like in picture 01). In this case, there will be a "1"
2. B_SESSION and L_SESSION columns are the blocking session (the head) and the locked session (the tail), respectively 
3. PATH_EVENT column, show you the lock path. It includes the Session ID, the SQL ID and the wait event if that session in waiting. Otherwise, the wait event is replaced by "ON CPU" 
4. If the SQL_ID is NULL, then I try to use the PREV_SQL_ID. In this case a "(p)" will appear (for example "2sbqfhnjz6ybw(p)" in orange in the picture 02). Anyway there are some cases where also the previous SQL_ID is NULL. In this case there will be only the "(p)" without the SQL_ID (B_SESSION = 1401, in the picture 02, for example).