4 Best Ways to Find Blocking Sessions in Oracle 11g

Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn't release it before another sessions wants to update the same data. This will block the second until the first one has done its work. It mainly happens when a session issues an insert, update or delete command that changes a row. When the change occurs, the row is locked until the session either commits the change or rolls the change back.

From the view of the user it will look like the application completely hangs while waiting for the first session to release its lock. You'll often have to identify these sessions in order to improve your application to avoid as many blocking locks as possible.

.You can see where problems might occur, for example a user might make a change and then forget to commit it and leaves for the weekend without logging off the system.

Oracle provide views like, DBA_BLOCKERS and V$LOCK using which we can easily find the blocking locks. Here, we will try to find blocking locks using V$LOCK view which is faster to query and makes it easy to identify the blocking session.

SQL> select * from v$lock ;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST    CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- -------------------------------------
AF8E2C4C AF9E2C50      419 TX     141028      15289      0              6                    675          0
ADDF7EC8 ADDF8EE0    542 TM    77529          0            3              0                    687          0
ADDF7F74 ADDF7F8C     419 TM    77529          0            3              0                    675          0
ADEBEA20 ADEBEB4C   542 TX     141028     152899     6             0                    687          1

Here we are interested in the BLOCK column. If a session holds a lock that's blocking another session, BLOCK=1. Further, you can tell which session is being blocked by comparing the values in ID1 and ID2. The blocked session will have the same values in ID1 and ID2 as the blocking session, and, since it is requesting a lock it's unable to get, it will have REQUEST > 0.

In the query above, we can see that SID 542 is blocking SID 419. SID 542 corresponds to Session 1 in our example, and SID 419 is our blocked Session 2. To avoid having to stare at the table and cross-compare ID1's and ID2's, put this in a query:

 Query to find Blocking sessions using v$lock
SQL> select l1.inst_id,l1.sid, ' IS BLOCKING ', l2.sid,l1.type,l2.type,l1.lmode,l2.lmode,l2.inst_id
from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

To Get Detailed information on blocking locks
SQL> SELECT 'Instance '||s1.INST_ID||' '|| s1.username || '@' || s1.machine
   || ' ( SID=' || s1.sid || ','|| s1.serial#||s1.status||  '  )  is blocking '
   || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' ||s2.sql_id
    FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
   WHERE s1.sid=l1.sid AND
    s1.inst_id=l1.inst_id AND
    s2.sid=l2.sid AND
    s2.inst_id=l2.inst_id AND
    l1.BLOCK=1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l2.id2 = l2.id2 ;

Finding blocking sessions with v$session
set lines 1234 pages 9999
col inst_id for a10
col serial# for a10
col machine for a30
col username for a10
col event for a20
col blocking_session for 999999
col blocking_instance for 999999
col status for a10
col INST_ID for 9999
col SERIAL# for 999999

SQL> select inst_id,sid,serial#, machine, username, event, blocking_session, blocking_instance, status, sql_id from gv$session where status ='ACTIVE'and username is not null;

Finding SQL_ID from SID using v$session

SQL> select sql_id from v$session where sid=4120;


SQL> select sql_fulltext from v$sql where sql_id ='xxxxx';

Killing Oracle Sessions
Be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.

There are a number of ways to kill blocking sessions both from Oracle sql prompt and externally.

Identify the Session to be Killed
The Windows Approach
The UNIX Approach

This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.

In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';


The DISCONNECT SESSION command kills the dedicated server process, which is equivalent to killing the server process from the operating system

kill -9 spid

 To identifying blocked objects

The view v$lock we've already used in the queries above exposes even more information. There are differnet kind of locks - check this site for a complete list: http://download.oracle.com/docs/cd/B13789_01/server.101/b10755/dynviews_1123.htm#sthref3198

If you encounter a TM lock is means that two sessions are trying to modify some data but blocking each other. Unless one sessions finished (commit or rollback), you'll never have to wait forever.

The following queries shows you all the TM locks:

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

The ID you get from this query refers to the actual database object which can help you to identify the problem, look at the next query:

SELECT object_name FROM dba_objects WHERE object_id=20127

These queries should help you to identify the cause of your blocking sessions!

No comments:

Post a Comment



Email *

Message *