Mastering Concurrency: A DBA Toolkit for Detecting, Analyzing, and Purging Locks in Oracle RAC & CDB

In a high-throughput, enterprise Oracle environment—especially one utilizing Real Application Clusters (RAC) and Multitenant Architecture (CDB/PDB)—concurrency issues like blocking locks and mutual waits can severely degrade performance. Left unchecked, they can bring business-critical applications to a grinding halt.

While Oracle automatically detects and resolves true deadlocks (raising an ORA-00060), it does not automatically resolve prolonged circular waits, application-level serialization, or cascading blocking chains.

This guide provides a production-ready toolkit to detect complex blocking scenarios, visualize lock chains, safely automate remediations using a parameterized PL/SQL framework, and audit sessions after they have been terminated.

1. Real-Time Blocking Report (RAC & CDB Aware)

When an application slows down, the first step is to isolate the root blocker. Relying solely on local v$ views in a RAC environment will mask blockers residing on other cluster nodes.

The query below uses gv$session and isolates the final blocking session across the entire cluster. It aggregates the total number of impacted waiters, resolves the container name (PDB), fetches the offending SQL statement, and automatically generates a ready-to-execute, cross-instance KILL command.

PROMPT ========================================================================
PROMPT MULTI-INSTANCE / CDB-AWARE ROOT BLOCKER REPORT
PROMPT ========================================================================

WITH blocked AS (
    SELECT
        s.inst_id,
        s.sid,
        s.serial#,
        s.con_id,
        s.event,
        s.wait_class,
        s.final_blocking_session         AS blocker_sid,
        s.final_blocking_instance        AS blocker_inst,
        s.final_blocking_session_serial# AS blocker_serial
    FROM gv$session s
    WHERE s.final_blocking_session IS NOT NULL
),
agg AS (
    SELECT
        b.blocker_sid,
        b.blocker_inst,
        b.blocker_serial,
        COUNT(*) AS blocked_count,
        MIN(b.wait_class) KEEP (DENSE_RANK LAST ORDER BY b.wait_class) AS any_wait_class
    FROM blocked b
    GROUP BY b.blocker_sid, b.blocker_inst, b.blocker_serial
)
SELECT
    bs.inst_id                    AS blocker_inst_id,
    bs.sid                        AS blocker_sid,
    bs.serial#                    AS blocker_serial,
    bs.con_id                     AS blocker_con_id,
    cp.name                       AS blocker_pdb_name,
    bs.username                   AS blocker_username,
    bs.osuser                     AS blocker_osuser,
    bs.machine                    AS blocker_machine,
    bs.program                    AS blocker_program,
    bs.sql_id                     AS blocker_sql_id,
    sq.sql_text                   AS blocker_sql_text,
    bs.event                      AS blocker_event,
    bs.wait_class                 AS blocker_wait_class,
    a.blocked_count,
    'ALTER SYSTEM KILL SESSION ''' || 
        bs.sid || ',' || bs.serial# || ',@' || bs.inst_id || ''' IMMEDIATE' AS kill_cmd
FROM agg a
JOIN gv$session bs
  ON bs.sid = a.blocker_sid
 AND bs.inst_id = a.blocker_inst
LEFT JOIN gv$sql sq
  ON sq.inst_id = bs.inst_id
 AND sq.sql_id  = bs.sql_id
LEFT JOIN v$containers cp
  ON cp.con_id   = bs.con_id
ORDER BY a.blocked_count DESC, bs.wait_class, bs.event;

Key Architectural Highlights:

• final_blocking_session Usage: Unlike blocking_session (which only shows the immediate upstream blocker), this identifies the ultimate root cause at the head of a cascading chain.
• CDB/PDB Visibility: Maps the con_id to v$containers, showing you exactly which Pluggable Database is generating the contention.
• Instance-Qualified Kill: The generated string uses the syntax @<inst_id>, allowing a DBA connected to Instance 1 to immediately terminate a session running on Instance 3.

2. Chain-Aware View: Exposing Mutual Waits

Simple session-based tracking sometimes misses complex lock patterns where sessions are interleaved on the same resources, or where multiple sessions are waiting on the exact same TX (Transaction) or TM (DML) enqueue.

By analyzing gv$lock, we can evaluate explicit Blockers (block = 1) vs. Waiters (request > 0) matching on the same resource parameters (type, id1, and id2). This structure maps out the exact topology of the contention and uncovers mutual waits.

PROMPT ========================================================================
PROMPT LOCK-CHAIN & MUTUAL WAIT DIAGNOSTIC VIEW
PROMPT ========================================================================

WITH locks AS (
    SELECT inst_id, sid, type, id1, id2, lmode, request, block
    FROM gv$lock
),
pairs AS (
    -- Intersect explicit blockers with explicit waiters on identical resources
    SELECT
        bl.inst_id   AS blocker_inst,
        bl.sid       AS blocker_sid,
        wl.inst_id   AS waiter_inst,
        wl.sid       AS waiter_sid,
        bl.type, bl.id1, bl.id2
    FROM locks bl
    JOIN locks wl
      ON wl.type = bl.type
     AND wl.id1  = bl.id1
     AND wl.id2  = bl.id2
    WHERE bl.block = 1
      AND wl.request > 0
),
enriched AS (
    SELECT
        p.blocker_inst,
        p.blocker_sid,
        p.waiter_inst,
        p.waiter_sid,
        bs.username        AS blocker_username,
        bs.osuser          AS blocker_osuser,
        bs.sql_id          AS blocker_sql_id,
        bsql.sql_text      AS blocker_sql_text,
        ws.username        AS waiter_username,
        ws.osuser          AS waiter_osuser,
        ws.sql_id          AS waiter_sql_id,
        wsql.sql_text      AS waiter_sql_text,
        bs.event           AS blocker_event,
        bs.wait_class      AS blocker_wait_class,
        ws.event           AS waiter_event,
        ws.wait_class      AS waiter_wait_class
    FROM pairs p
    JOIN gv$session bs
      ON bs.inst_id = p.blocker_inst AND bs.sid = p.blocker_sid
    JOIN gv$session ws
      ON ws.inst_id = p.waiter_inst  AND ws.sid = p.waiter_sid
    LEFT JOIN gv$sql bsql
      ON bsql.inst_id = bs.inst_id AND bsql.sql_id = bs.sql_id
    LEFT JOIN gv$sql wsql
      ON wsql.inst_id = ws.inst_id AND wsql.sql_id = ws.sql_id
)
SELECT
    blocker_inst,
    blocker_sid,
    waiter_inst,
    waiter_sid,
    blocker_username,
    blocker_osuser,
    blocker_sql_id,
    SUBSTR(blocker_sql_text, 1, 2000) AS blocker_sql_text,
    blocker_event,
    blocker_wait_class,
    waiter_username,
    waiter_osuser,
    waiter_sql_id,
    SUBSTR(waiter_sql_text, 1, 2000)  AS waiter_sql_text,
    waiter_event,
    waiter_wait_class,
    'ALTER SYSTEM KILL SESSION ''' || 
        blocker_sid || ',' || 
        (SELECT x.serial# FROM gv$session x WHERE x.inst_id = blocker_inst AND x.sid = blocker_sid) || 
        ',@' || blocker_inst || ''' IMMEDIATE' AS kill_cmd
FROM enriched
ORDER BY blocker_wait_class, blocker_event;

Application:

If Session A is blocking Session B on a TX lock, and Session B is blocking Session A on an unindexed foreign key TM lock, both directions will output here as individual rows. This clear lineage tells the DBA exactly which side of the lock matrix to safely terminate to break the loop.

3. Automated Mitigation: Threshold-Driven PL/SQL Framework

Manually resolving lock contentions during a major incident is error-prone. The PL/SQL procedure below provides a safe, parameter-driven mechanism to evaluate cluster-wide blockers.

By default, it operates in Dry-Run mode, logging intended actions via DBMS_OUTPUT. When flipped to active mode, it safely terminates blockers that exceed your specified thresholds.

PROMPT ========================================================================
PROMPT CREATING THRESHOLD-BASED AUTOMATED PURGE PROCEDURE
PROMPT ========================================================================

CREATE OR REPLACE PROCEDURE sys.purge_blockers_by_threshold (
    p_min_blocked_count   IN NUMBER   DEFAULT 3,
    p_wait_class_filter   IN VARCHAR2 DEFAULT 'Application',  -- Set to NULL for no filter
    p_event_like          IN VARCHAR2 DEFAULT 'enq: TX%',     -- Set to NULL for no filter
    p_dry_run             IN BOOLEAN  DEFAULT TRUE
) IS
BEGIN
    FOR r IN (
        WITH blocked AS (
            SELECT
                s.inst_id,
                s.sid,
                s.serial#,
                s.final_blocking_session         AS blocker_sid,
                s.final_blocking_instance        AS blocker_inst,
                s.final_blocking_session_serial# AS blocker_serial,
                s.event,
                s.wait_class
            FROM gv$session s
            WHERE s.final_blocking_session IS NOT NULL
        ),
        agg AS (
            SELECT
                blocker_sid, blocker_inst, blocker_serial,
                COUNT(*) AS blocked_count,
                MIN(wait_class) KEEP (DENSE_RANK LAST ORDER BY wait_class) AS any_wait_class,
                MIN(event) KEEP (DENSE_RANK LAST ORDER BY event)           AS any_event
            FROM blocked
            GROUP BY blocker_sid, blocker_inst, blocker_serial
        )
        SELECT
            a.blocked_count,
            bs.inst_id     AS blocker_inst,
            bs.sid         AS blocker_sid,
            bs.serial#     AS blocker_serial,
            bs.username    AS blocker_username,
            bs.osuser      AS blocker_osuser,
            bs.sql_id      AS blocker_sql_id,
            bs.event       AS blocker_event,
            bs.wait_class  AS blocker_wait_class
        FROM agg a
        JOIN gv$session bs
          ON bs.sid = a.blocker_sid
         AND bs.inst_id = a.blocker_inst
        WHERE a.blocked_count >= NVL(p_min_blocked_count, 0)
          AND (p_wait_class_filter IS NULL OR bs.wait_class = p_wait_class_filter)
          AND (p_event_like IS NULL OR bs.event LIKE p_event_like)
        ORDER BY a.blocked_count DESC, bs.wait_class, bs.event
    ) LOOP
        
        DBMS_OUTPUT.PUT_LINE(
            'Candidate blocker: inst=' || r.blocker_inst ||
            ' | sid=' || r.blocker_sid ||
            ' | serial=' || r.blocker_serial ||
            ' | blocked_count=' || r.blocked_count ||
            ' | user=' || NVL(r.blocker_username,'') ||
            ' | osuser=' || NVL(r.blocker_osuser,'') ||
            ' | sql_id=' || NVL(r.blocker_sql_id,'') ||
            ' | event=' || NVL(r.blocker_event,'') ||
            ' | wait_class=' || NVL(r.blocker_wait_class,'')
        );

        IF p_dry_run THEN
            DBMS_OUTPUT.PUT_LINE(
                '   [DRY-RUN] Would execute: ALTER SYSTEM KILL SESSION ''' ||
                r.blocker_sid || ',' || r.blocker_serial || ',@' || r.blocker_inst || ''' IMMEDIATE'
            );
        ELSE
            BEGIN
                EXECUTE IMMEDIATE
                    'ALTER SYSTEM KILL SESSION ''' ||
                    r.blocker_sid || ',' || r.blocker_serial || ',@' || r.blocker_inst || ''' IMMEDIATE';
                DBMS_OUTPUT.PUT_LINE('   [SUCCESS] Executed kill for sid ' || r.blocker_sid || ' on instance ' || r.blocker_inst);
            EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE('   [ERROR] Kill failed for sid ' || r.blocker_sid ||
                        ' on instance ' || r.blocker_inst || ': ' || SQLERRM);
            END;
        END IF;
    END LOOP;
END;
/

Execution Examples:

-- Test Run: Scan and output all targets causing >= 3 sessions to hang on application events
SET SERVEROUTPUT ON;
EXEC sys.purge_blockers_by_threshold(p_min_blocked_count => 3, p_dry_run => TRUE);

-- Active Remediation: Safely run in a crisis to clear extreme row-lock conditions
EXEC sys.purge_blockers_by_threshold(p_min_blocked_count => 5, p_event_like => 'enq: TX%', p_dry_run => FALSE);

4. Operational Best Practices & Safety Guardrails

⚠ Critical Risk Warning: Rollback Contention
When terminating a transaction holding a large volume of modified rows, Oracle must perform a single-threaded transactional rollback using Undo blocks. During this period, the session status changes to KILLED or PSEUDO-SESSION, but it continues to hold its locks until the rollback completes.

To mitigate operational risk, follow these rules of engagement:

• Evaluate Transaction Footprint: Before killing, look at v$transaction via the blocker's SID to check used_urec (Undo Records). If this value is in the millions, a kill command will trigger a massive, long-running rollback that maintains the lock.
• The “Softer” Approach: For long-running queries that are not modifying data but are holding metadata locks, consider using ALTER SYSTEM CANCEL SQL '<sid>,<serial>,@<inst_id>' first. This stops the current statement without breaking the user session or forcing a total transaction rollback.
• Identify the Smart Target: Do not blindly kill the highest root blocker if it represents an immutable core batch job. Instead, identify lower-priority application modules or ad-hoc reporting tools that can easily retry their transactions.

5. Post-Mortem Auditing: Investigating Killed Sessions

Once a session is terminated, its corresponding record vanishes from v$session and gv$session. To perform a proper root-cause analysis, a DBA must look into historical data repositories.

Method A: Active Session History (ASH)

Requires the Oracle Diagnostics Pack License.
ASH samples active sessions once per second, caching data into SGA memory before flushing it to disk (dba_hist_active_sess_history). This query surfaces what the session was running up to the exact moment it was killed.

PROMPT ========================================================================
PROMPT HISTORICAL INVESTIGATION VIA ASH (DIAGNOSTICS PACK REQ.)
PROMPT ========================================================================
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-RRRR HH24:MI:SS';

SELECT 
    sample_time,
    session_id AS sid,
    session_serial# AS serial#,
    sql_id,
    user_id,
    program,
    module,
    event,
    wait_class
FROM dba_hist_active_sess_history
WHERE user_id = (SELECT user_id FROM dba_users WHERE username = UPPER('&USR')) 
  AND sample_time > SYSDATE - 1 -- Adjust lookback interval as required
ORDER BY sample_time DESC;

Method B: Database Auditing (DBA_AUDIT_SESSION)

Requires standard database session auditing (audit_trail enabled).
If you lack the Diagnostics Pack license, you can look at the session connection lifecycle to find exact duration windows, logon timelines, and application terminal information.

PROMPT ========================================================================
PROMPT SESSION CONNECTION TIMELINE VIA STANDARD AUDITING
PROMPT ========================================================================

SELECT 
    username,
    os_username,
    userhost,
    terminal,
    TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS')   AS logon_time,
    TO_CHAR(logoff_time, 'YYYY-MM-DD HH24:MI:SS')  AS logoff_time,
    (logoff_time - timestamp) * 24 * 60            AS duration_minutes,
    sessionid                                      AS audit_session_id
FROM dba_audit_session
WHERE username = UPPER('&USR')
  AND timestamp > SYSDATE - 1
ORDER BY timestamp DESC;

6. Recommended Enhancements for Production

To graduate this framework into an enterprise monitoring utility, consider adding these architectural improvements:

1. PDB-Specific Control: Add a p_con_id parameter to the PL/SQL procedure to enable tenant-specific isolation, allowing individual application owners to clear locks inside their respective PDBs without looking at the wider container.
2. Persistent Audit Trail: Build a local logging table (e.g., sys.blocked_purge_history) and configure the PL/SQL exception handler to insert metadata records (timestamp, target_sid, sql_id, blocked_count) every time a session is killed for full accountability.
3. Automated Alerts: Wrap the lock-chain diagnostic query into an upstream monitoring agent (like Oracle Enterprise Manager metric extensions or Prometheus exporters) to fire an alert the moment a lock chain extends past 5 minutes.