DB01 Locks History : ASH

[SriKrishna]

I am investigating row lock contention issue in one of our PROD systems.

i have been checking DBA_HIST_ACTIVE_SESS_HISTORY and GV$ACTIVE_SESSION_HISTORY, and observed a column BLOCKING_SESSION.

does BLOCKiNG_SESSION displays the session holding the lock or it displays the sessions waiting due to lock?

because, the description of BLOCKING_SESSION column on Oracle Docs is as follows.

Session identifier of the blocking session. Populated only when the session was waiting for enqueues or a "buffer busy" wait.

The second statement "Populated only when the session was waiting for enqueues" is confusing me.

any expert opinions on it ?

how to find out, which session is holding the locks ?

I have written a sample query to find out, but i want to know the list of sessions holding the locks.

any tweaks for my query ?

any recommendations will help me a lot

The query i used is below.

SELECT DISTINCT A.SQL_ID,TO_CHAR(A.SAMPLE_TIME,'YYYY-MM-DD HH24:MI:SS') SAMPLE_TIME,A.BLOCKING_SESSION,D.OBJECT_NAME,S.SQL_TEXT

FROM DBA_HIST_ACTIVE_SESS_HISTORY A, GV$SQL S, DBA_OBJECTS D

WHERE A.SQL_ID=S.SQL_ID

AND BLOCKING_SESSION IS NOT NULL

AND A.USER_ID <> 0

AND A.CURRENT_OBJ# = D.OBJECT_ID

AND A.SAMPLE_TIME BETWEEN

TO_TIMESTAMP('12.05.2015 00:00:00', 'dd.mm.yyyy hh24:mi:ss') AND

TO_TIMESTAMP('12.05.2015 23:59:59', 'dd.mm.yyyy hh24:mi:ss')

AND A.EVENT = 'enq: TX - row lock contention'

ORDER BY

SAMPLE_TIME DESC

[fidel_vales · Accepted solution]

Hi,

That columns indicate the session that currently has the lock.

Example, session 100 updates a row without a commit. Then session 500 tries to update the same row. The session 500 will be waiting on "enq: TX row lock contention" and the field will be update with the number 100 (session who holds the bock)

On the SAP Note 1438410 you have some scripts that can help you (start with LOCKS_)

[SriKrishna · Accepted solution]

Hi Fidel,

many thanks for the reply.

how to know the session which is holding the lock?

does V$session maintain any historical storage, like DBA* view for active sessions?

[Brindavan_M · Accepted solution]

Hi Krishna,

The below quires might be helpful to identified the lock session.

1) To find blocking sessions with v$session

select

   blocking_session,

   sid,

   serial#,

   wait_class,

   seconds_in_wait

from

   v$session

where

   blocking_session is not NULL

order by

   blocking_session;

2) To find blocking sessions using v$lock

SELECT

   l1.sid || ' is blocking ' || l2.sid blocking_sessions

FROM

   v$lock l1, v$lock l2

WHERE

   l1.block = 1 AND

   l2.request > 0 AND

   l1.id1 = l2.id1 AND

   l1.id2 = l2.id2

3) To identifying blocked objects

find the TM Locks

SELECT sid, id1 FROM v$lock WHERE TYPE='TM'

The ID you get from this query refers to the actual database object

SELECT object_name FROM dba_objects WHERE object_id=20127

Thanks,

BM

[fidel_vales · Accepted solution]

krishna chaitanya k s wrote:

Hi Fidel,

many thanks for the reply.
how to know the session which is holding the lock?
does V$session maintain any historical storage, like DBA* view for active sessions?

Hi,

the session holding the lock is in the field "blocking_session" in the view V$SESSION.

And the history is stored in the "ASH" (title of your question)

     V$ACTIVE_SESSION_HISTORY

     DBA_HIST_ACTIVE_SESS_HISTORY

If the blocking session does not appear on this views during the time the session is blocked, it means that the session is not active @ DB level (it is working on ABAP)