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.