Identify sessions that are blocking other sessions

by Granville Bonyata on December 11, 2015

Sometimes an application will be plagued by sessions that are abandoned partway through a transaction, leaving locks on rows. Other sessions may try to update/delete these locked rows and will hang while waiting for the locks to be release.

Here is a simple query against the ASH view that will find any sessions that have blocked another session for more than 8 seconds:

SELECT DISTINCT
       blocked_sessions.blocked_seconds,
       blocked_sessions.blocking_session_serial#,
       blocked_sessions.last_blocked_time,
       blocked_sessions.blocking_session,
       blocked_sessions.inst_id,
       blocked_sessions.session_id,
       blocked_sessions.session_serial#,
       s.username
  FROM (
        SELECT COUNT(ash_blocked.sql_exec_id)
            OVER (partition by ash_blocked.sql_exec_id,ash_blocked.blocking_session) blocked_seconds,
       MAX(ash_blocked.sample_time)
            OVER (partition by ash_blocked.sql_exec_id,ash_blocked.blocking_session) last_blocked_time,
       ash_blocked.*
        FROM gv$active_session_history ash_blocked
       WHERE ash_blocked.blocking_session IS NOT NULL
       ) blocked_sessions,
       gv$session s
 WHERE blocked_sessions.blocked_seconds > 8
   AND blocked_sessions.session_id = s.sid
   AND blocked_sessions.session_serial# = s.serial#
   AND blocked_sessions.inst_id = s.inst_id
   AND blocked_sessions.blocking_session = s.blocking_session
ORDER BY blocked_sessions.blocked_seconds,
         blocked_sessions.blocking_session_serial#,
         blocked_sessions.last_blocked_time,
         blocked_sessions.blocking_session,
         blocked_sessions.inst_id,
         blocked_sessions.session_id,
         blocked_sessions.session_serial#
/

Previous post:

Next post: