Identifying and Resolving Blocking Sessions in Oracle Database

Blocking sessions in an Oracle database occur when one session holds a lock on a resource that other sessions need, causing them to wait. This can lead to performance bottlenecks and user complaints. Identifying and resolving these sessions is a critical task for database administrators.

1. Identifying Blocking Sessions

Oracle provides several views and tools to help DBAs detect blocking sessions:

1.1 Using V$SESSION View

The V$SESSION view helps identify sessions waiting for a resource due to blocking.

1. Query to Find Blocking and Blocked Sessions:

   SELECT
       s1.username AS blocker_user,
       s1.sid AS blocker_sid,
       s1.serial# AS blocker_serial,
       s1.blocking_session AS blocked_by,
       s2.username AS blocked_user,
       s2.sid AS blocked_sid,
       s2.serial# AS blocked_serial,
       s2.event AS blocked_event
   FROM
       v$session s1
   JOIN
       v$session s2
   ON
       s1.sid = s2.blocking_session
   WHERE
       s2.blocking_session IS NOT NULL;

1. Interpretation of Results:
   • blocker_user: User holding the lock.
   • blocker_sid: SID of the blocking session.
   • blocked_user: User being blocked.
   • blocked_sid: SID of the blocked session.
   • blocked_event: Wait event for the blocked session.

1.2 Using V$LOCK View

The V$LOCK view provides detailed information about lock types and states.

1. Query to Identify Blocking Locks:

   SELECT
       l1.sid AS blocker_sid,
       l2.sid AS blocked_sid,
       l1.type AS lock_type,
       l1.id1 AS lock_id1,
       l1.id2 AS lock_id2
   FROM
       v$lock l1
   JOIN
       v$lock l2
   ON
       l1.id1 = l2.id1
       AND l1.id2 = l2.id2
       AND l1.block = 1
       AND l2.request > 0;

1. Interpretation of Results:
   • blocker_sid: Session holding the lock.
   • blocked_sid: Session waiting for the lock.
   • lock_type: Type of lock (e.g., TM for DML, TX for transactions).

1.3 Using Automatic Workload Repository (AWR)

1. Generate an AWR report during the time of contention:

   • Run the following command in SQL*Plus:

    @$ORACLE_HOME/rdbms/admin/awrrpt.sql
   

• Look for "Blocking Sessions" in the report.

1. Analyze session statistics and wait events in the report.

1.4 Using Enterprise Manager

1. Navigate to Performance > Blocking Sessions.
2. View the graphical representation of blocking sessions and dependencies.

2. Resolving Blocking Sessions

Once blocking sessions are identified, you can take steps to resolve them. Ensure you understand the business impact before proceeding.

2.1 Kill the Blocking Session

If the blocking session is idle or causing severe issues, you can terminate it.

1. Find SID and Serial#: Use the V$SESSION view to identify the SID and SERIAL# of the blocking session:

   SELECT sid, serial# FROM v$session WHERE sid = <blocker_sid>;

1. Kill the Session:

   ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>';

Replace <SID> and <SERIAL#> with values from the query.

1. Force Kill (if needed): If the session doesn’t terminate, use the IMMEDIATE option:

   ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>' IMMEDIATE;

2.2 Identify and Resolve the Root Cause

1. Long-Running Transactions:

   • Query active transactions to identify long-running or uncommitted ones:

    SELECT * FROM v$transaction WHERE status = 'ACTIVE';
   

• Request the application team to commit or rollback the transaction.

1. Optimize Queries:

   • Analyze queries causing locks using V$SQL or AWR:

    SELECT sql_text FROM v$sql WHERE sql_id = '<sql_id>';
   

• Optimize poorly written queries or reduce lock contention.

1. Resolve Deadlocks:
   • Identify deadlocks using the trace file generated in the USER_DUMP_DEST directory.
   • Modify application logic to prevent circular dependencies.

2.3 Adjust Lock Timeout

If the blocked session can wait, adjust the lock timeout to avoid indefinite waits.

ALTER SESSION SET DDL_LOCK_TIMEOUT = <seconds>;

2.4 Implement Indexing

Poorly indexed tables can lead to full-table scans and increased lock contention. Add appropriate indexes to reduce contention.

3. Best Practices to Prevent Blocking

1. Commit Transactions Promptly:

   • Ensure applications commit or rollback transactions promptly to avoid holding locks unnecessarily.

2. Use Row-Level Locking:

   • Use row-level locks instead of table locks for DML operations whenever possible.

3. Implement Deadlock Detection and Retry:

   • Use application logic to detect and handle deadlocks gracefully.

4. Schedule Resource-Intensive Operations:

   • Run heavy operations during off-peak hours to reduce contention.

5. Monitor Regularly:

   • Set up monitoring tools like Oracle Enterprise Manager or scripts to identify blocking sessions early.

Conclusion

Blocking sessions are a routine component of database operations; however, they can lead to serious problems if not addressed swiftly. By actively monitoring and managing these blocking sessions, you can maintain the optimal operation of your Oracle database. Adopting best practices can help avert blocking issues from developing into more substantial performance bottlenecks.