Diagnosing Oracle Blocked Sessions: A Complete Guide

Amaresh Pelleti

Database performance is critical for business operations, and one of the most common issues Oracle DBAs face is dealing with blocked sessions. In this comprehensive guide, we’ll explore how to identify, analyze, and resolve Oracle blocked sessions with practical examples and ready-to-use scripts.

What Are Blocked Sessions in Oracle?

When one session in an Oracle database acquires a lock on a resource (such as a table row or object), other sessions requesting access to the same resource may have to wait. These waiting sessions are referred to as “blocked sessions.” The session holding the lock is the “blocking session.”

Blocked sessions can lead to:

  • Degraded application performance
  • Timeouts and errors
  • Poor user experience
  • In extreme cases, complete application unavailability

How to Identify Blocked Sessions in Oracle

Let’s look at practical methods to detect blocked sessions in your Oracle database.

Method 1: Using V$SESSION Views

This query provides comprehensive information about current blocked sessions:

SELECT 
    blocking_session.sid blocker_sid,
    blocking_session.serial# blocker_serial,
    blocking_session.username blocker_username,
    blocking_session.machine blocker_machine,
    blocking_session.program blocker_program,
    blocking_session.logon_time blocker_logon_time,
    blocked_session.sid waiting_sid,
    blocked_session.serial# waiting_serial,
    blocked_session.username waiting_username,
    blocked_session.machine waiting_machine,
    blocked_session.program waiting_program,
    blocked_session.logon_time waiting_logon_time,
    w.wait_class waiting_class,
    w.event waiting_event,
    w.seconds_in_wait wait_seconds,
    sql_text.sql_text blocked_sql
FROM 
    v$session blocked_session
    JOIN v$session blocking_session ON (blocked_session.blocking_session = blocking_session.sid)
    JOIN v$session_wait w ON (w.sid = blocked_session.sid)
    LEFT JOIN v$sql sql_text ON (sql_text.sql_id = blocked_session.sql_id)
WHERE 
    blocked_session.blocking_session IS NOT NULL
ORDER BY 
    w.seconds_in_wait DESC;

Method 2: Historical Analysis with Active Session History

To investigate blocking issues that occurred during a specific time period:

SELECT 
    ash.sample_time,
    ash.session_id blocked_session,
    ash.session_serial# blocked_serial#,
    ash.user_id blocked_user,
    ash.blocking_session blocker_sid,
    ash.blocking_session_serial# blocker_serial#,
    ash.event blocking_event,
    ash.wait_class wait_class,
    ash.sql_id sql_id,
    ash.sql_plan_hash_value plan_hash,
    ash.module blocked_module,
    ash.action blocked_action
FROM 
    dba_hist_active_sess_history ash
WHERE 
    ash.blocking_session IS NOT NULL
    AND ash.sample_time BETWEEN 
        TIMESTAMP '2025-03-08 10:00:00'
        AND TIMESTAMP '2025-03-08 11:00:00'
ORDER BY 
    ash.sample_time;

Method 3: Visualizing Blocking Chains

Sometimes blocking creates a cascade effect. This query helps visualize the complete blocking tree:

WITH session_tree AS (
    SELECT 
        LEVEL lvl,
        sid,
        blocking_session,
        username,
        sql_id,
        event,
        seconds_in_wait,
        LPAD(' ', (LEVEL-1)*2) || sid session_tree
    FROM 
        v$session
    WHERE 
        sid IN (
            SELECT blocking_session FROM v$session
            UNION
            SELECT sid FROM v$session WHERE blocking_session IS NOT NULL
        )
    CONNECT BY PRIOR sid = blocking_session
    START WITH blocking_session IS NULL
)
SELECT 
    session_tree,
    username,
    sql_id,
    event,
    seconds_in_wait
FROM 
    session_tree
ORDER BY 
    lvl;

Real-Time Example: Analyzing a Production Blocking Scenario

Let’s walk through a real-world example to demonstrate how these techniques can be applied.

Scenario: Application Timeout During Month-End Processing

Problem: During month-end financial processing, several users reported that their reports were taking unusually long to generate, with some users receiving timeout errors.

Investigation Process:

  1. Initial Detection: Using the V$SESSION query, we identified multiple blocked sessions:
BLOCKER_SID BLOCKER_USERNAME WAITING_SID WAITING_USERNAME WAITING_EVENT       WAIT_SECONDS
------------ --------------- ----------- --------------- ------------------- ------------
         123 FINANCE_ADMIN          234 REPORT_USER     enq: TX - row lock          302
         123 FINANCE_ADMIN          247 REPORT_USER     enq: TX - row lock          287
         123 FINANCE_ADMIN          251 REPORT_USER     enq: TX - row lock          265
  1. Root Cause Analysis: After examining the blocker’s SQL with the following query:
SELECT sql_text 
FROM v$sql 
WHERE sql_id = (SELECT sql_id FROM v$session WHERE sid = 123);

We discovered an uncommitted transaction that was updating a large number of rows in the MONTHLY_TOTALS table. The transaction had been running for over 15 minutes without being committed.

  1. Resolution: We contacted the finance administrator who had left for lunch without completing their transaction. They returned and committed their work, immediately releasing all locks and allowing the blocked sessions to proceed.
  2. Prevention Measures:
    • Implemented a policy to avoid large updates during report generation hours
    • Added transaction timeout parameters
    • Created a monitoring alert for long-running transactions

Common Causes of Blocked Sessions

  1. Long-Running Transactions: Transactions that modify data but aren’t committed promptly
  2. Improper Transaction Management: Applications that don’t handle commits and rollbacks efficiently
  3. Table/Row Locking Contention: Multiple sessions competing for the same resources
  4. Missing or Inefficient Indexes: Forcing full table scans that escalate locking
  5. Application Design Flaws: Poor connection pooling or transaction boundary management

Best Practices to Prevent Blocked Sessions

  1. Optimize Transaction Design:
    • Keep transactions short and focused
    • Commit frequently, especially after bulk operations
    • Avoid user input during open transactions
  2. Implement Proper Indexing:
    • Ensure queries use appropriate indexes
    • Regularly review execution plans for full table scans
  3. Use Appropriate Isolation Levels:
    • Consider READ COMMITTED for most operations
    • Use SERIALIZABLE only when necessary
  4. Database Configuration:
    • Set appropriate timeouts (DDLTIMEOUT, DISTRIBUTIONTIMEOUT)
    • Configure deadlock detection intervals
  5. Monitoring and Alerting:
    • Implement proactive monitoring for blocking sessions
    • Set up alerts for long-running transactions

Conclusion

Blocked sessions can significantly impact database performance and user experience. By understanding the causes and implementing proper detection methods, you can quickly identify and resolve these issues. The SQL scripts provided in this post are valuable tools for any Oracle DBA’s toolkit.

Remember that prevention is always better than cure. Implementing the best practices outlined here will help minimize blocking incidents in your Oracle database environment.

Further Resources