Tracking Locks in Oracle Database

A web application I was tracing had serious DB performance issues. I reviewed the AWR, found out that the IO was incredibly slow, but a weird behavior made me suspect some locks were also the reason for the poor performance.
While running JMeter, I saw a situation where no responses are received from the server, until, suddenly, everything started running – only to be stuck again.
So, how did I track locks in the database? I’ve used the following queries (of course, all require SYSDBA to run):

SELECT l.session_id||','||v.serial# sid_serial,
       l.ORACLE_USERNAME ora_user,
       o.object_name, 
       o.object_type, 
       DECODE(l.locked_mode,
          0, 'None',
          1, 'Null',
          2, 'Row-S (SS)',
          3, 'Row-X (SX)',
          4, 'Share',
          5, 'S/Row-X (SSX)',
          6, 'Exclusive', 
          TO_CHAR(l.locked_mode)
       ) lock_mode,
       o.status,                                                                                                                               
       to_char(o.last_ddl_time,'dd.mm.yy') last_ddl                                                                                            
FROM dba_objects o, gv$locked_object l, v$session v                                                                                            
WHERE o.object_id = l.object_id                                                                                                                
      and l.SESSION_ID=v.sid                                                                                                                   
order by 2,3;

I use the above query to see if actual locks exist in the DB (it runs pretty fast, so I use it often).

select l1.sid, ' IS BLOCKING ', 
       l2.sid 
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;

I use the above query to see which session is locked (very useful to detect dead-locks)

select s1.username || '@' || s1.machine 
  || ' ( SID=' || s1.sid || ' )  is blocking '
  || s2.username || '@' || s2.machine || ' ( SID=' 
  || s2.sid || ' ) ' AS blocking_status,
  a1.sql_text,
  a2.sql_text
from v$lock l1, 
     v$session s1, 
     v$lock l2, 
     v$session s2,
     v$sqlarea a1, 
     v$sqlarea a2
where s1.sid=l1.sid and 
      l1.BLOCK=1 and 
      s2.sid=l2.sid and 
      l2.request > 0 and 
      l1.id1 = l2.id1 and 
      l2.id2 = l2.id2 and 
      s1.sql_hash_value = a1.hash_value and 
      s1.sql_address = a1.address and 
      s2.sql_hash_value = a2.hash_value and 
      s2.sql_address = a2.address;

The above query is my favorite (alas, it is very slow…) as it shows the actual locking SQL – and the locked SQL. If you know your code – you can easily identify which query causes the locking problem.