Cause:
Blocking sessions occur when one session holds an exclusive lock on an object and doesn't release it before another session wants to update the same data. This will block the second until the first one has done its work.
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 will often have to identify these sessions in order to improve your application performance to avoid as many blocking sessions as possible.
Solution:
The following query shows all the blocking sessions which can help you to identify the problem.
col WAIT_CLASS for a12;
select blocking_session,sid,serial#,wait_class,seconds_in_wait,status from v$session where blocking_session is not NULL order by blocking_session;
BLOCKING_SESSION SID SERIAL# WAIT_CLASS SECONDS_IN_WAIT STATUS
---------------- ---------- ---------- ------------ --------------- --------
5290 5527 590 Application 2146 ACTIVE
5438 5024 5728 Application 118 ACTIVE
5438 5369 4674 Application 2025 ACTIVE
5438 5251 2139 Application 2072 ACTIVE
5499 5438 2805 Application 2073 ACTIVE
5508 5901 4208 Application 6468 ACTIVE
5898 5290 4533 Application 2149 ACTIVE
5901 5019 11812 Application 5163 ACTIVE
5901 5135 1236 Application 6045 ACTIVE
5901 4934 1797 Application 792 ACTIVE
5901 5947 583 Application 1985 ACTIVE
5901 5204 4373 Application 241 ACTIVE
5901 5751 2989 Application 2171 ACTIVE
5901 5752 3331 Application 3641 ACTIVE
5993 5646 544 Commit 0 ACTIVE
5993 5486 3187 Commit 0 ACTIVE
16 rows selected.
In this case, we find that session 5901 is blocking 7 other sessions.
select sid,serial#,event,module,action,status,program,blocking_session,last_call_et from v$session where sid=&sid;
Enter value for sid: 5901
SID SERIAL# EVENT MODULE ACTION STATUS PROGRAM BLOCKING SESSION LAST_CALL_ET
-----------------------------------------------------------------------------------
5901 4208 enq: TX - row lock contention WSHINTERFACE Concurrent Request ACTIVE 5508 6493
From the above query we find that session 5901 is being blocked by session 5508.
select sid,serial#,event,module,action,status,program,blocking_session,last_call_et from v$session where sid=&sid;
Enter value for sid: 5508
SID SERIAL#
---------- ----------
EVENT
----------------------------------------------------------------
MODULE
------------------------------------------------
ACTION STATUS
-------------------------------- --------
PROGRAM BLOCKING_SESSION LAST_CALL_ET
------------------------------------------------ ---------------- ------------
5508 7300
SQL*Net message from client
OEXOETEL
FRM:CLIMAM:Receiptionist Parts - INACTIVE
7554
From the above query we find that session 5508 has become inactive.
In this case, we find that session 5901 is blocking 7 other sessions which in turn has been blocked by session 5508 and has been for 7554 seconds.
So, we kill this inactive session.
To kill the session from within Oracle, the sid and serial# values of the relevant session can then be substituted into the following statement:
SQL> alter system kill session ‘sid,serial#’;
alter system kill session'5508,7300';
System altered.
This command tells the specified session to rollback any un-committed changes and release any acquired resources before terminating cleanly. In some situations, this cleanup processing may take a considerable amount of time, in which case the session status is set to “marked for kill" until the process is complete and not killed immediately, the alter system kill session command can be forced by adding the immediate keyword:
SQL> alter system kill session 'sid,serial#' immediate;