Tuesday, 25 December 2012

How to detect blocking query in oracle?


How to find blocking queries in Oracle.
-------------------------------------------
Below are the few queries to find out session which are blocking other sessions. First one is the general query which gives information regarding which session is blocking another session.

 SELECT s1.username , s1.machine, ' ( SID=' || s1.sid || ' )  is blocking ',s2.username , s2.machine , ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    FROM v$lock l1, v$session s1, v$lock l2, v$session s2
    WHERE s1.sid=l1.sid AND s2.sid=l2.sid
    AND l1.BLOCK=1 AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l2.id2 = l2.id2 ;



Query to find out the text of blocking session


Next query gives you the sql which is blocking your session. Replace <query_sid> with the session id of blocking session to get the sql.

select sql_text from v$sqltext where hash_value=( select prev_hash_value from v$session where sid=<query_sid>);

Query to find out the machine name and username of blocking session


This is the query which gives you the username and machine which is blocking your session. Replace <query_sid> with the session id of blocking session to get the details.

select sid||' - '||serial#||' - '||osuser||' - '||username||' - '||machine||' - '||status||' - '||logon_time
from v$session where sid=<query_sid>;

Query to find out holder and waiter session


This one tells you all the information related to holder and waiter sessions. It tells you the mode in which the session is being holded/waiting.

select decode(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type from v$lock
where (id1, id2, type) IN (SELECT id1, id2, type from v$lock where request>0) ORDER BY id1, request;

No comments:

Post a Comment

back to top