DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, December 20, 2016

History of enq: TX - row lock contention

As a DBA, we should be able to provide the history of locks to the application team to check.

This is the query to get the information from v$active_session_history

This view is part of diagnosis pack, make sure that you have a license for Diagnostic Pack before running this query.

Lock History information - v$active_session_history:
====================================================
col SAMPLE_TIME for a30
col BLOCKING_PATH format a40
set lines 170
SELECT 
   level,
   sample_time,
   session_id blocked_sid,
   CONNECT_BY_ROOT blocking_session ultimate_blocker_sid,
   sys_connect_by_path(blocking_session,'/')|| '/' || session_id blocking_path 
FROM
   (-- Blocked Sessions
    SELECT
       s.session_id,
       s.blocking_session,
       s.sample_time
    FROM v$active_session_history s 
    --s.dbid = 70854992
   -- AND s.instance_number = 1
    --AND s.snap_id BETWEEN 62234 and 62235
    WHERE  s.blocking_session IS NOT NULL
    AND s.event IN 
        ('enq: TX - row lock contention')
    UNION
    -- Blocking Sessions
    SELECT s.session_id,
           s.blocking_session,
           s.sample_time
    FROM v$active_session_history s 
    -- s.dbid = 70854992
    --AND s.instance_number = 1
    --AND s.snap_id BETWEEN 62234 and 62235
    WHERE s.blocking_session IS NULL
    AND s.event IN
        ('enq: TX - row lock contention'
        ,'enq: TM - contention'
        ,'enq: UL - contention'
        ,'enq: TX - allocate ITL entry')
    AND EXISTS
       (SELECT 'exists' 
        FROM v$active_session_history bs 
        --bs.dbid = 70854992
        --AND bs.instance_number = 1
        --AND bs.snap_id BETWEEN 62234 and 62235
        WHERE bs.blocking_session = s.session_id
        AND bs.sample_time = s.sample_time
        AND bs.blocking_session IS NOT NULL
        AND bs.event IN 
            ('enq: TX - row lock contention')
       )
   )
CONNECT BY NOCYCLE PRIOR session_id = blocking_session 
                   AND PRIOR sample_time = sample_time 
ORDER BY sample_time;


What we can do, if the required information not found in v$active_session_history?

Don't worry, we can get it from dba_hist_active_sess_history view.

Lock History information - dba_hist_active_sess_history:
========================================================

SELECT 
   level,
   sample_time,
   session_id blocked_sid,
   CONNECT_BY_ROOT blocking_session ultimate_blocker_sid,
   sys_connect_by_path(blocking_session,'/')|| '/' || session_id blocking_path 
FROM
   (-- Blocked Sessions
    SELECT
       s.session_id,
       s.blocking_session,
       s.sample_time
    FROM dba_active_sess_history s 
   -- WHERE s.dbid = 70854992
   -- AND s.instance_number = 1
    WHERE s.snap_id BETWEEN '&StSnap' and '&EdSnap'
    AND s.blocking_session IS NOT NULL
    AND s.event IN 
        ('enq: TX - row lock contention'
        ,'enq: TM - contention'
        ,'enq: UL - contention'
        ,'enq: TX - allocate ITL entry')
    UNION
    -- Blocking Sessions
    SELECT s.session_id,
           s.blocking_session,
           s.sample_time
    FROM dba_active_sess_history s 
    -- s.dbid = 70854992
    -- s.instance_number = 1
    WHERE s.snap_id BETWEEN '&StSnap' and '&EdSnap'
    AND s.blocking_session IS NULL
    AND s.event IN
        ('enq: TX - row lock contention'
        ,'enq: TM - contention'
        ,'enq: UL - contention'
        ,'enq: TX - allocate ITL entry')
    AND EXISTS
       (SELECT 'exists' 
        FROM DBA_HIST_ACTIVE_SESS_HISTORY bs 
        -- bs.dbid = 70854992
        -- bs.instance_number = 1
        WHERE bs.snap_id BETWEEN '&StSnap' and '&EdSnap'
        AND bs.blocking_session = s.session_id
        AND bs.sample_time = s.sample_time
        AND bs.blocking_session IS NOT NULL
        AND bs.event IN 
            ('enq: TX - row lock contention'
            ,'enq: TM - contention'
            ,'enq: UL - contention'
            ,'enq: TX - allocate ITL entry')
       )
   )
CONNECT BY NOCYCLE PRIOR session_id = blocking_session 
                   AND PRIOR sample_time = sample_time 

ORDER BY level DESC, blocked_sid, sample_time;


No comments: