Blocking Sessions in Oracle2 min read

Oracle is known for its tuning, reliability, and robustness. But unlike any complex setup, it can face blocking session issue which directly impacts on its performance for SQL statements.

Blocking session occurs when one session holds the resources which are requested by another session.

Another session has no option other than to wait for the resource. This becomes the reason for deadlock, causing performance bottlenecks and system slowness.

In this session we will check how to check for blocking sessions and provide solution to it.

To check for Locks, we can use the below queries:

--Find blocking sessions
SELECT a.SID AS "Blocking Session", b.SID AS "Blocked Session"
FROM gv$lock a
JOIN gv$lock b ON a.SID != b.SID AND a.ID1 = b.ID1 AND a.ID2 = b.ID2
WHERE b.request > 0 AND a.block = 1;


--More readable
SELECT
  s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' ||
  s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
FROM
  gv$lock l1
JOIN
  gv$session s1 ON s1.sid = l1.sid
JOIN
  gv$lock l2 ON l1.id1 = l2.id1 AND l1.id2 = l2.id2
JOIN
  gv$session s2 ON s2.sid = l2.sid
WHERE
  l1.BLOCK = 1
  AND l2.request > 0;

Use below query to check wait time:

--Wait time in seconds
SELECT
  blocking_session AS "BLOCKING_SESSION",
  sid AS "BLOCKED_SESSION",
  serial# AS "BLOCKED_SERIAL#",
  seconds_in_wait AS "WAIT_TIME(Seconds)"
FROM
  gv$session
WHERE
  blocking_session IS NOT NULL
ORDER BY
  blocking_session;

--Wait time in minutes
SELECT
  blocking_session AS "BLOCKING_SESSION",
  sid AS "BLOCKED_SESSION",
  serial# AS "BLOCKED_SERIAL#",
  seconds_in_wait/60 AS "WAIT_TIME(Minutes)"
FROM
  gv$session
WHERE
  blocking_session IS NOT NULL
ORDER BY
  blocking_session;

Resolving Locks

In order to resolve lock session which has locked the rows or the Blocking sessions needs to perform Commit or Rollback.
This will remove the lock while the session can remain active.
If the session disconnects without Rollback or Commit then internal rollback will be made releasing the lock.

If DBA needs this lock to be removed oin forceful manner, then they have the ability to kill the session. It can be done using below queries:

--Find details of session to be killed
-- SID would have been identified when you were checking for blocking sessions
	SELECT SID,SERIAL# FROM GV$SESSION WHERE SID=&Session_SID; 
--To kill session:
ALTER SYSTEM KILL SESSION 'SID, SERIAL# ' IMMEDIATE;

--TO identify details in case of RAC
  SELECT INST_ID,SID,SERIAL# FROM GV$SESSION WHERE SID=&Session_SID; 
--In RAC Environment
ALTER SYSTEM KILL SESSION 'SID, SERIAL#, @INSTANCE_NUMBER ' IMMEDIATE;