Oracle – Find Blocking Sessions

Blocking sessions can occur when two transactions try to update the same set of data. The first transaction will attain a lock on the data and will become the “blocking” transaction. If a second transaction attempts to update that same data, it will not be able to complete until the first transaction terminates (via a COMMIT or ROLLBACK). Below is a simple example of such a scenario:
Transaction 1

SQL> DESC test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VALUE                                              VARCHAR2(30)

SQL> UPDATE test SET value = 'VALUE 1';

1 row updated.

Notice the lack of commit. This keeps the transaction open.

Transaction 2

SQL> UPDATE test SET value = 'VALUE 2';

Notice that this command does not return. It is blocked. Transaction 1 will either need to complete or be killed in order to allow transaction 2 to complete.

There are a number of different queries that will helpĀ find blocking sessions. Depending on the type of underlying block, only some of these will return the session that needs to be killed. Here are some of the common ones I use:

SELECT
  'ALTER SYSTEM KILL SESSION ''' || s_blocking.sid || ',' || s_blocking.serial# || ''';' kill_command,
  s_blocked.username blocked_username,
  s_blocked.osuser blocked_osuser,
  s_blocked.machine blocked_machine,
  SUBSTR( sql_blocked.sql_text, 1, 50 ) blocked_sql,
  s_blocking.username blocking_username,
  s_blocking.osuser blocking_osuser,
  s_blocking.machine blocking_machine,
  SUBSTR( sql_blocking.sql_text, 1, 50 ) blocking_sql
FROM v$session s_blocked, v$session s_blocking, v$sql sql_blocked, v$sql sql_blocking
WHERE s_blocked.blocking_session = s_blocking.sid
  AND s_blocked.sql_id = sql_blocked.sql_id(+)
  AND s_blocking.sql_id = sql_blocking.sql_id(+)
  AND s_blocked.blocking_session IS NOT NULL;
SELECT 
  'ALTER SYSTEM KILL SESSION ''' || s_blocking.sid || ',' || s_blocking.serial# || ''';' kill_command,
  s_blocked.username blocked_username,
  s_blocked.osuser blocked_osuser,
  s_blocked.machine blocked_machine,
  SUBSTR( sql_blocked.sql_text, 1, 50 ) blocked_sql,
  s_blocking.username blocking_username,
  s_blocking.osuser blocking_osuser,
  s_blocking.machine blocking_machine,
  SUBSTR( sql_blocking.sql_text, 1, 50 ) blocking_sql
FROM v$lock l_blocking, v$lock l_blocked, v$session s_blocking, v$session s_blocked, v$sql sql_blocking, v$sql sql_blocked
WHERE l_blocking.sid = s_blocking.sid
  AND l_blocked.sid = s_blocked.sid
  AND s_blocking.sql_id = sql_blocking.sql_id(+)
  AND s_blocked.sql_id = sql_blocked.sql_id(+)
  AND l_blocking.block = 1 
  AND l_blocked.request > 0
  AND l_blocking.id1 = l_blocked.id1
  AND l_blocking.id2 = l_blocked.id2;

This one is interesting as it helped me find a session preventingĀ a package from compiling. I could not get any “standard” blocking session queries to return. The root issue was a job that was hung and then killed using DBMS_SCHEDULER.DROP_JOB with the FORCE parameter set to true. The job was no longer present in the job views, but the cache pin remained. This query is adapted from The Oracle Community.

SELECT
  'ALTER SYSTEM KILL SESSION ''' || s_blocking.sid || ',' || s_blocking.serial# || ''';' kill_command,
  s_blocking.username blocking_username,
  kl.kglnaobj blocking_objectname,
  CASE kl.kglobtyp
    WHEN 1 THEN 'index'
    WHEN 2 THEN 'table'
    WHEN 3 THEN 'cluster'
    WHEN 4 THEN 'view'
    WHEN 5 THEN 'synonym'
    WHEN 6 THEN 'sequence'
    WHEN 7 THEN 'procedure'
    WHEN 8 THEN 'function'
    WHEN 9 THEN 'package'
    WHEN 11 THEN 'package body'
    WHEN 12 THEN 'trigger'
    ELSE 'others'
  END blocking_objtype,
  s_blocking.program blocking_program,
  sql_blocking.sql_text blocking_sql
FROM x$kglpn kpin, v$session s_blocking, x$kglob kl, v$session_wait seswait, v$sql sql_blocking
WHERE kpin.kglpnuse = s_blocking.saddr
  AND kpin.kglpnhdl = kl.kglhdadr
  AND kl.kglhdadr = seswait.p1raw
  AND seswait.event = 'library cache pin'
  AND s_blocking.sql_id = sql_blocking.sql_id;
Advertisements