Oracle slow commit cascade: when redo storage degrades and every transaction waits

Every write transaction is slow. TPS is down, p99 commit latency is up, and the slowdown hits writes uniformly across transaction types. No single SQL_ID is the culprit. CPU is underutilized while sessions pile up on a wait event. This is the Oracle slow commit cascade: the storage behind your online redo logs can no longer service LGWR’s commit flushes fast enough, and every COMMIT in the database pays the price.

The mechanism is the redo write path. Every COMMIT triggers LGWR to flush the redo log buffer to the online redo logs, and the committing session blocks on log file sync until LGWR confirms the write is on disk. When redo storage degrades, LGWR’s own I/O wait (log file parallel write) climbs, and the foreground log file sync follows it upward. Because the bottleneck is on the universal commit path, the impact is global, not query-specific.

This article covers how to confirm the cascade, distinguish it from lookalikes (LGWR CPU starvation, plan regression, lock contention), and what to do under pressure when every minute of slow commits is a minute of user-visible latency.

What this means

In a healthy OLTP system on SSD or NVMe, average log file sync sits under 1 ms and log file parallel write is similar. When redo storage degrades, both climb together. The relationship between the two events is the diagnostic anchor:

• log file sync is the foreground session’s view: time from issuing COMMIT until LGWR posts completion.
• log file parallel write is LGWR’s view: the actual disk write latency for the redo flush.
• If both are elevated, the bottleneck is physical I/O on the redo devices.
• If log file sync is high but log file parallel write is low, LGWR is being starved of CPU or delayed by scheduling. That is a different problem with a different fix.

The cascade propagates as follows:

1. Redo storage latency rises (failing SSD, SAN path contention, redo sharing a device with data files, I/O scheduler or filesystem regression).
2. LGWR’s log file parallel write climbs.
3. Foreground log file sync waits follow.
4. Every COMMIT takes longer, so every write transaction takes longer, regardless of which SQL it runs.
5. Redo logs cycle faster relative to DBWn’s checkpoint progress, and Checkpoint not complete appears in the alert log.
6. TPS declines uniformly while CPU stays comparatively idle, because sessions are blocked on I/O rather than computing.

flowchart TD
    A[Redo storage degrades] --> B[log file parallel write climbs]
    B --> C[log file sync climbs]
    C --> D[Every COMMIT waits longer]
    D --> E[TPS declines uniformly]
    B --> F[DBWn checkpoint falls behind]
    F --> G[Checkpoint not complete]
    G --> H[log file switch waits]

The defining feature is uniformity. Plan regression spikes specific SQL_IDs. Lock contention chains behind a single blocker. CPU saturation shows high run-queue with low I/O wait. The slow redo cascade shows all write transactions slower, both redo wait events elevated together, and physical read latency on data files often unaffected.

Common causes

Quick checks

Run these first. They are all read-only.

-- Compare the two redo wait events side by side
SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO,
       ROUND(TIME_WAITED_MICRO / NULLIF(TOTAL_WAITS, 0) / 1000, 2) AS avg_ms
FROM V$SYSTEM_EVENT
WHERE EVENT IN ('log file sync', 'log file parallel write');

-- Dominant non-idle wait class right now
SELECT NVL(WAIT_CLASS, 'ON CPU') AS wait_class, COUNT(*) AS sessions
FROM V$SESSION
WHERE STATUS = 'ACTIVE' AND TYPE = 'USER' AND WAIT_CLASS != 'Idle'
GROUP BY NVL(WAIT_CLASS, 'ON CPU')
ORDER BY COUNT(*) DESC;

-- Redo generation rate (sample twice, divide delta by seconds)
SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'redo size';

-- Transactions per second (sample twice)
SELECT VALUE FROM V$SYSSTAT WHERE NAME IN ('user commits', 'user rollbacks');

-- Log switch frequency in the last day
SELECT TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24') AS hour, COUNT(*) AS switches
FROM V$LOG_HISTORY
WHERE FIRST_TIME > SYSDATE - 1
GROUP BY TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24')
ORDER BY 1;

-- Checkpoint-related waits
SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO
FROM V$SYSTEM_EVENT
WHERE EVENT LIKE 'log file switch%';

# OS-level redo device latency; look at await and %util for the redo LUN
iostat -x 1 5

# Alert log for checkpoint pressure
adrci exec="show alert -tail 200"
# Or grep the alert log directly:
grep -i "checkpoint not complete\|cannot allocate new log" \
  $ORACLE_BASE/diag/rdbms/*/*/trace/alert_*.log | tail -30

How to diagnose it

1. Confirm the cascade pattern. Query V$SYSTEM_EVENT for both log file sync and log file parallel write. Both should be elevated. If only log file sync is high and log file parallel write is low, stop. The problem is LGWR scheduling or CPU, not redo storage, and the fix is different.
2. Verify the impact is uniform. Check V$SQL for top SQL by elapsed time per execution. In a slow redo cascade, no single SQL_ID has a dramatically worsened plan. Per-execution buffer gets are stable. This rules out plan regression.
3. Check the dominant wait class. The fastest triage signal is the wait class breakdown from V$SESSION. If Commit dominates active sessions, you are in this pattern. If Concurrency or Application dominates, you have a lock problem.
4. Measure the redo device at the OS layer. iostat -x 1 5 on the redo device. Compare await against the storage class. For SSD it should be in the low single-digit milliseconds; for SAN, typically under 5 ms. Sustained await well above the storage’s expected latency confirms physical I/O as the bottleneck.
5. Map redo log file placement. Cross-reference V$LOGFILE member paths with V$DATAFILE paths. If redo and data files share a LUN, volume, or array port, you have structural contention and the fix is relocation.
6. Check for filesystem and scheduler regressions. Inspect /proc/mounts for the redo filesystem. ext4 with data=journal is known to add redo latency; redo performs best on data=writeback or raw ASM. Confirm the I/O scheduler on the redo block device is none, noop, or deadline, not cfq or bfq.
7. Look for Checkpoint not complete in the alert log. Its presence alongside rising redo waits confirms DBWn is being starved behind the same degraded storage. It is a downstream symptom, not the root cause.
8. Inspect redo log group count and sizing. Pull switch frequency from V$LOG_HISTORY. Frequent switches plus checkpoint pressure suggest the redo logs are undersized for the current write rate, which amplifies any storage latency problem.

Metrics and signals to monitor

Fixes

Move redo logs to dedicated fast storage

This is the actual fix. Redo logs are the most latency-sensitive I/O path in Oracle. Putting them on a dedicated device removes contention with DBWn and direct-path reads, and lets you size the device to the redo workload rather than to the union of all database I/O. ASM on raw devices or a dedicated LUN is the cleanest target. A filesystem is supported but introduces journaling and allocation overhead that can hurt tail latency. If you must use a filesystem, mount with data=writeback on ext4 and verify direct I/O is in effect.

Moving redo logs is online in terms of database availability, but it involves log switches. Plan it for a maintenance window. Add the new redo log groups on the new storage, switch logs, and drop the old groups once they are no longer current or required for recovery.

Add redo log groups and resize them upward

This does not fix bad storage, but it buys time. More and larger groups give DBWn a longer window to checkpoint before LGWR needs to wrap, which suppresses Checkpoint not complete and reduces log switch frequency. It is a holding action while you arrange better storage, not a substitute.

Address filesystem and scheduler regressions

If the cascade started after a kernel upgrade, remount, or udev rule change, suspect the I/O stack. Verify the redo filesystem is not in data=journal mode. Verify the scheduler on the redo block device is appropriate. Verify multipathing is healthy and that the path you expect is the path being used.

Remove slow multiplexed members

If a redo log group has a member on slow storage (a spun-down disk, an overloaded SAN LUN, an NFS mount), every group write pays that latency, because LGWR must write all members before posting completion. Drop the slow member or move it to storage with latency comparable to the other members.

Reduce commit frequency where you can

If storage cannot be improved immediately and the workload permits, batching commits reduces the number of times the slow path is exercised per unit of work. This is a workaround with application-semantics implications, not a fix. The commit path is still slow; you are simply hitting it less often.

Prevention

• Keep redo on dedicated storage. Treat redo log placement as an architectural decision, not a default. Do not colocate redo with data files, temp, archive, or anything else that competes for I/O.
• Monitor both redo wait events, not just log file sync. A slow commit cascade is only confirmable when you can see log file parallel write climbing alongside it. Single-signal monitoring will mislead you toward LGWR tuning when the problem is underneath it.
• Alert on uniform TPS decline. A drop in TPS with stable application load, combined with elevated commit-class waits, is the early signature. Catch it before Checkpoint not complete appears.
• Track OS-level redo device latency. Oracle wait events are necessary but not sufficient. iostat -x on the redo device is ground truth.
• Watch redo log sizing relative to switch frequency. If switch frequency creeps up and you start seeing checkpoint pressure, resize before storage latency turns a sizing problem into a cascade.
• Validate after kernel and storage changes. Most cascades start with an external change: a remount with different options, a scheduler change, a SAN firmware update, a VM snapshot. Re-baseline redo latency after any storage-touching change.

How Netdata helps

• Per-second log file sync and log file parallel write latency, side by side, makes the cascade confirmable in seconds rather than after a delta computation against cumulative counters.
• TPS and redo generation rate are collected at the same per-second resolution, so a uniform TPS decline correlates cleanly against a commit-class wait spike.
• OS-level disk latency (await, %util, queue depth) is collected on the same host and the same timeline as the Oracle wait events, so a redo-device problem shows up in both layers simultaneously.
• ML anomaly detection flags deviation from the per-instance baseline for each wait event, which catches slow-moving storage degradation before it crosses a static threshold.
• The alert log signal from the Oracle integration surfaces Checkpoint not complete and related messages alongside the metrics, so the downstream symptom is visible in the same view as the upstream cause.
• Container, VM, and host layers are correlated, which matters when the trigger is a VMware snapshot, a storage vMotion, or a noisy neighbor rather than the database itself.

Netdata’s Oracle Database monitoring brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• Oracle ’log file sync’ waits: slow commits, LGWR, and the redo path
• Oracle Database monitoring checklist: the signals every production instance needs
• Oracle Database monitoring maturity model: from survival to expert