This was first published on https://blog.dbi-services.com/oracle-locks-identifiying-blocking-sessions (2014-11-17)
Republishing here for new followers. The content is related to the the versions available at the publication date
When you have sessions blocked on locks, you probably have all information about the waiters (they call you and anyway their waiting session is visible in v$session our ASH). But you usually need to get enough information that help to identify the blocker.
Here is a query I use to get that quickly, based on V$WAIT_CHAINS
Here is the result I want to get:
session wait event minutes USER PRO ----------------------- ----------------------------------------------- ------- ---- --- ABCLBP1 '831,54109@1' SQL*Net message from client 13.5 SYS sql ABCLBP4 '395,21891@4' enq: TX - row lock contention on TABLE 13.2 SYS SQL "SYS"."TEST_FRANCK" on rowid AAC0aCAAnAAABSCAAA
I have information about blocking session, waiting session, the type of lock (here TX – row lock) and because it is a row lock I want to know the ROWID of the locked row.
Here is the query I used to get it:
column "wait event" format a50 word_wrap column "session" format a25 column "minutes" format 9999D9 column CHAIN_ID noprint column N noprint column l noprint with w as ( select chain_id,rownum n,level l ,lpad(' ',level,' ')||(select instance_name from gv$instance where inst_id=w.instance)||' '''||w.sid||','||w.sess_serial#||'@'||w.instance||'''' "session" ,lpad(' ',level,' ')||w.wait_event_text || case when w.wait_event_text like 'enq: TM%' then ' mode '||decode(w.p1 ,1414332418,'Row-S' ,1414332419,'Row-X' ,1414332420,'Share' ,1414332421,'Share RX' ,1414332422,'eXclusive') ||( select ' on '||object_type||' "'||owner||'"."'||object_name||'" ' from all_objects where object_id=w.p2 ) when w.wait_event_text like 'enq: TX%' then ( select ' on '||object_type||' "'||owner||'"."'||object_name||'" on rowid ' ||dbms_rowid.rowid_create(1,data_object_id,relative_fno,w.row_wait_block#,w.row_wait_row#) from all_objects ,dba_data_files where object_id=w.row_wait_obj# and w.row_wait_file#=file_id ) end "wait event" , w.in_wait_secs/60 "minutes" , s.username , s.program from v$wait_chains w join gv$session s on (s.sid=w.sid and s.serial#=w.sess_serial# and s.inst_id=w.instance) connect by prior w.sid=w.blocker_sid and prior w.sess_serial#=w.blocker_sess_serial# and prior w.instance = w.blocker_instance start with w.blocker_sid is null ) select * from w where chain_id in (select chain_id from w group by chain_id having max("minutes") >= 1 and max(l)>1 ) order by n /
This query retrieves the wait chains where a session is waiting for more than one minute on a table lock (TM) or row lock (TX) .
When it is a table lock (TM), I get the locked object_id from the P2 parameter, in order to know the table name.
When it is a row lock, I get the table and rowid from V$SESSION. Note that I have to join with dba_data_files in order to convert the absolute file_id to a relative one, and to join to dba_objects in order to convert the object_id to the data_object_id one – in order to built the ROWID.
More information about ROWID, relative file number and data object id in my previous post: From 8.0 extended rowid to 12c pluggable db: Why Oracle Database is still a great software
AS you said >>>>When you have sessions blocked on locks, you probably have all information about the waiters (they call you and anyway their waiting session is visible in v$session our ASH).<<<<<<
Can we really depend on ASH to know about blocking sessions….
Pls check the below link.. https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9531968400346153211
Hi Satya, I was talking about blocked sessions, not blocking sessions. Blocked sessions are waiting on enqueue event, so you see them in ASH if they waited for more than 1 second (10 seconds if AWR history ASH). For blocking sessions, that’s different, because they may not be active. Regards, Franck.