DBMS_STATS Locked 200 Sessions Out for 8 Minutes

Thursday, 09:12. The order processing system stopped accepting new orders. Every INSERT into the orders table hung indefinitely. No errors returned — just silence. The connection pool exhausted within two minutes as active connections accumulated and no connections returned to the idle pool. From the application's perspective, the database had stopped responding. From the database's perspective, every session was simply waiting for a lock.

We had seen brief lock events before — a long-running report occasionally held a row lock for a few seconds. But this was different: the lock holder was SYS, not an application user, and the lock type was on the dictionary cache, not a row or table lock. Dictionary cache locks are invisible to the standard blocking query that joins V$SESSION to itself — you need V$LOCK with specific lock type filters to see them. Every INSERT into the orders table hung. No errors — just no response. The application health check showed the database connection pool at 100% utilization with all connections in "active" state. Within three minutes, 200 sessions were queued.

The Alert

SELECT
  w.sid AS waiting_sid,
  w.event AS wait_event,
  w.seconds_in_wait,
  h.sid AS blocking_sid,
  h.username AS blocking_user,
  h.program AS blocking_program,
  h.status AS blocking_status,
  SUBSTR(sq.sql_text, 1, 200) AS blocking_sql
FROM v$session w
JOIN v$session h ON h.sid = w.blocking_session
LEFT JOIN v$sql sq ON sq.sql_id = h.sql_id
WHERE w.blocking_session IS NOT NULL
ORDER BY w.seconds_in_wait DESC;

The blocker: SID 342, username SYS, program DBMS_SCHEDULER, status ACTIVE. The SQL was DBMS_STATS.GATHER_TABLE_STATS on the ORDERS table — a 400-partition, 800-million-row table. We verified three times at 30-second intervals: same blocker, session count growing from 147 to 178 to 203. SID 342 was gathering statistics and blocking every DML.

First Hypothesis: Application-Level Lock

We assumed an application bug had left a long-running transaction open. But V$SESSION showed the blocker was SYS running DBMS_STATS. The nightly stats job — which should have run at 02:00 — was running at 09:12. We checked the scheduler job log three times to confirm.

SELECT
  job_name, actual_start_date, run_duration, status
FROM dba_scheduler_job_run_details
WHERE job_name LIKE '%GATHER%STATS%'
ORDER BY actual_start_date DESC
FETCH FIRST 5 ROWS ONLY;

The stats job had been starting at 02:00, running for 6 hours, and finishing at 08:00 — but two days ago, a new partitioned table was added to the gather list, extending runtime to 9 hours. The job was still running at 09:12, now hitting the ORDERS table during peak traffic. Verified three times from the job log: runtime increased from 6h to 9h after the new table was added.

The Discovery

DBMS_STATS.GATHER_TABLE_STATS acquires an exclusive lock on the data dictionary cache entry for the table during partition-level statistics gathering. On a 400-partition table with the default GRANULARITY='AUTO', it gathers stats for each partition sequentially, holding the dictionary lock across the entire operation. Every INSERT and UPDATE on ORDERS requires a dictionary cache lookup — which is blocked by the exclusive lock.

We confirmed the lock type three times using V$LOCK: the library cache lock (type=N) on the ORDERS table object was held by SID 342 in exclusive mode. All 200 waiting sessions were requesting shared mode on the same object.

Incident Timeline

Root Cause

The stats gathering job overran its maintenance window due to a newly added table. It reached the ORDERS table during peak traffic and acquired exclusive dictionary locks that blocked all DML. The combination of: a 400-partition table, sequential partition processing, exclusive dictionary locking, and no maintenance window cutoff created an 8-minute production outage.

The Fix

-- Kill the blocking session
ALTER SYSTEM KILL SESSION '342,18744' IMMEDIATE;

-- Set a maintenance window end time
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'MAINTENANCE_WINDOW_GROUP',
    attribute => 'window_priority',
    value => 'LOW');
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL, window_name => NULL);
END;
/

-- Use DBMS_STATS with NO_INVALIDATE and incremental
EXEC DBMS_STATS.SET_TABLE_PREFS('PROD','ORDERS','INCREMENTAL','TRUE');

Prevention

The automatic stats collection window now has a hard end time of 07:00 — any job still running is killed. INCREMENTAL statistics are enabled on all partitioned tables. With incremental gathering, Oracle only gathers stats for partitions where the modification count exceeds the staleness threshold, then aggregates at the table level using synopsis data structures. On the ORDERS table with 400 partitions, this reduced gathering time from 35 minutes (scanning all partitions) to 90 seconds (scanning the 3 partitions modified since last gather).

The ORDERS table uses DBMS_STATS.SET_TABLE_PREFS with NO_INVALIDATE=TRUE to prevent plan invalidation cascades during stats updates. When statistics change, Oracle normally marks all dependent cursors as invalid, triggering hard parses on next execution. With NO_INVALIDATE=TRUE, existing cursors continue using their current plans until natural aging removes them from the shared pool. New cursors pick up the updated stats. This eliminates the hard-parse storm that compounds the dictionary lock contention.

We also separated the stats gathering into two tiers. Critical OLTP tables — orders, inventory, payments — get stats gathered in a dedicated 02:00–03:00 window when DML volume is near zero, so the dictionary locks have no contending sessions. Historical and reporting tables get stats gathered in the broader 03:00–07:00 window. This tiered approach ensures the highest-contention tables are processed when lock impact is minimal, even if the overall job overruns. — any job still running is killed. INCREMENTAL statistics are enabled on all partitioned tables — Oracle only gathers stats on changed partitions, reducing runtime from hours to minutes. The ORDERS table uses DBMS_STATS.SET_TABLE_PREFS with NO_INVALIDATE=TRUE to prevent plan invalidation cascades during stats updates.