Oracle 'log file sync' waits: slow commits, LGWR, and the redo path

Every COMMIT in Oracle is a synchronous handshake with the Log Writer (LGWR). The foreground session hands off its redo, waits for LGWR to flush that redo to the online redo logs, and only then returns control to the application. The wait event that covers this round trip is log file sync. When LGWR is slow, every committing transaction is slow. This is the most common “everything is uniformly slow” pattern in Oracle and the single most important latency signal to monitor.

The diagnostic power comes from comparing two events. log file sync is the client’s view: it spans posting LGWR, LGWR scheduling, the actual disk write, and LGWR posting back. log file parallel write is LGWR’s own view: pure I/O time. If both are high, storage is the bottleneck. If log file sync is high but log file parallel write is low, the problem is LGWR scheduling, CPU starvation, or the adaptive log file sync mechanism thrashing between post/wait and polling modes.

On modern SSD or NVMe storage, average log file sync should sit under 1ms. Above 3ms warrants investigation. Above 10ms is critical and means every commit in the system is paying a visible tax. SAN-backed redo logs tolerate higher baselines (under 5ms normal, above 20ms critical), but the same comparison logic applies regardless of storage class.

The signal in V$SYSTEM_EVENT is a cumulative average since instance startup. Variance matters as much as the average. A 2ms average with regular 100ms outliers usually indicates storage queueing or adaptive sync switching, both of which need different fixes than steady-state latency. In RAC, each instance has its own LGWR and its own redo thread, so a redo problem on one node can be invisible in cluster-wide aggregate views.

Common causes

Quick checks

-- log file sync average (system-wide, cumulative since startup)
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 = 'log file sync';

-- LGWR's actual I/O time (the comparison event)
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 = 'log file parallel write';

-- Sessions waiting on log file sync right now
SELECT SID, SERIAL#, EVENT, SECONDS_IN_WAIT, SQL_ID
FROM V$SESSION
WHERE EVENT = 'log file sync' AND STATE = 'WAITING';

-- Commit rate (sample twice, compute delta over interval)
SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'user commits';

-- Redo generation rate (sample twice, compute bytes/sec)
SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'redo size';

-- Redo log group configuration
SELECT GROUP#, THREAD#, MEMBERS, BYTES/1048576 AS size_mb, STATUS
FROM V$LOG ORDER BY GROUP#;

-- Redo log member locations (check for slow members)
SELECT GROUP#, MEMBER FROM V$LOGFILE ORDER BY GROUP#;

# OS-level redo device latency (Linux sysstat package)
iostat -x 1 5

# Check adaptive log file sync setting (hidden parameter; requires SYSDBA)
sqlplus -S / as sysdba <<'EOF'
SELECT KSPPINM, KSPPSTVL
FROM X$KSPPI k, X$KSPPCV v
WHERE k.INDX = v.INDX
AND KSPPINM = '_use_adaptive_log_file_sync';
EOF

How to diagnose

1. Confirm log file sync is actually elevated. On SSD/NVMe, anything above 3ms sustained warrants investigation; above 10ms is critical. Compare against log file parallel write rather than reading the absolute number alone.

2. Pull log file parallel write from the same view. This is the single most important comparison in Oracle performance diagnosis. The ratio between the two events tells you where the time is going.

flowchart TD
    A["log file sync elevated"] --> B{"log file parallel write also high?"}
    B -- "Yes, both high" --> C["Storage I/O bottleneck"]
    C --> C1["iostat await on redo device"]
    C --> C2["Redo on dedicated LUN?"]
    C --> C3["SAN contention or SSD wear"]
    B -- "No, sync high only" --> D["LGWR scheduling or CPU"]
    D --> D1["CPU run queue saturated"]
    D --> D2["Adaptive sync thrashing"]
    D --> D3["LGWR starved by scheduler"]

3. If log file parallel write is also elevated, the bottleneck is storage I/O. Skip to step 5.

4. If log file parallel write is normal but log file sync is high, the bottleneck is LGWR scheduling or CPU. Check the OS CPU run queue (uptime, vmstat 1, top). Verify LGWR is getting CPU time. Check the adaptive log file sync setting (the hidden parameter _use_adaptive_log_file_sync). LGWR posting latency is inflating the client view even though the underlying write is fast.

5. For storage I/O bottlenecks, verify with OS-level tools. iostat -x 1 5 on the redo device shows await and %util. Confirm redo logs are on a dedicated volume, not shared with data files. Check for SAN path degradation, SSD wear-out, or filesystem journaling overhead. ext4 with data=journal adds latency to redo writes. On VMware, check CPU ready time in esxtop (%RDY), because vCPU scheduling delay shows up inside Oracle wait events.

6. Check commit volume. A commit storm (autocommit applications, chatty ORMs committing after every row) multiplies the impact of any per-commit latency. Sample user commits from V$SYSSTAT twice to compute commits per second.

7. If Data Guard is configured with SYNC transport, check standby-related events. LNS wait on SENDREQ and LGWR-LNS wait on channel on the primary indicate it is waiting on the standby. SYNC AFFIRM mode means every commit waits for the standby to write redo to its own standby redo logs before acknowledging.

8. Check redo log group configuration. Undersized redo logs cause frequent switches, which amplify latency. checkpoint not complete messages in the alert log indicate DBWn cannot keep up with log switches, not an LGWR problem. That is a DBWn write-path issue and has a different fix.

9. If symptoms are spiky and high-variance, suspect adaptive log file sync. The mechanism switches LGWR between post/wait (semaphore-based) and polling (foreground sleeps and polls an SGA variable) modes. Known bugs in this switching can cause multi-second log file sync outliers on otherwise fast storage.

Metrics and signals to monitor

Fixes

Redo log storage latency

If log file parallel write confirms storage is the bottleneck, the fix is faster or dedicated storage.

• Move redo logs to a dedicated LUN or ASM disk group, separate from data files. Redo writes are sequential and latency-sensitive; data file reads and DBWn writes are a different I/O profile and compete for the same spindles or flash channels.
• On SAN, verify the redo LUN is on the fastest tier and check for path degradation. Storage vMotion or snapshot operations on the redo LUN add invisible latency.
• On filesystems, use data=writeback for ext4 or raw ASM. data=journal doubles write traffic and adds latency.
• On NVMe or persistent memory, Oracle 21c+ supports “fast log file sync” for redo logs on PMEM, using adaptive sleep and spin in foreground processes for low microsecond commit latencies.

LGWR CPU starvation

If log file parallel write is normal but log file sync is high, LGWR is not getting CPU time when it needs to write or post back.

• Check CPU saturation system-wide. LGWR is a single process per instance and competes with all user server processes for CPU. On a saturated box, LGWR can be starved even though it runs at high priority.
• Verify hugepages are configured for the SGA. Without hugepages, page table overhead consumes memory and TLB misses add latency to every SGA access, including LGWR’s redo log buffer scans.
• Consider LGWR process priority tuning only if CPU contention is chronic. This is environment-specific and should be tested carefully before production rollout.

Adaptive log file sync thrashing

The hidden parameter _use_adaptive_log_file_sync (default TRUE from 11.2.0.3) lets LGWR switch between post/wait and polling modes. The intent is to pick the lower-overhead mode, but the switching itself can cause severe latency outliers.

• If you see spiky, high-variance log file sync with normal log file parallel write, test disabling adaptive sync:

  -- Dynamic change; does not require a restart.
  -- WARNING: hidden parameter; test on a non-production node first and check support impact.
  ALTER SYSTEM SET "_use_adaptive_log_file_sync" = FALSE;
  

  This forces post/wait mode.

• Many production DBAs run with this parameter set to FALSE permanently after experiencing thrashing. Post/wait overhead is negligible on modern systems.

Commit storms and oversized redo

If the commit rate itself is the problem, the fix is application-side.

• Batch commits in OLTP loops. Committing after every row in a bulk insert multiplies LGWR round trips by the row count.

• For non-critical data, consider asynchronous commit via COMMIT_LOGGING = BATCH with COMMIT_WAIT = NOWAIT at the session level (COMMIT_WRITE is deprecated and retained for backward compatibility). This returns control to the application before LGWR confirms the write, trading durability for latency. Never use this for financial or transactional data where durability is required.

• Check for unintended redo generation. Supplemental logging (required for GoldenGate) can multiply redo volume 2-5x. NOLOGGING operations generate minimal redo but leave objects unrecoverable without a fresh backup and corrupt blocks on a standby unless the standby is rebuilt afterward.

Data Guard SYNC transport

If the primary’s log file sync is elevated and Data Guard is configured with SYNC (Maximum Availability or Maximum Protection mode), the primary is waiting for standby acknowledgment.

• Check LNS wait on SENDREQ and LGWR-LNS wait on channel events on the primary.
• Verify network bandwidth between primary and standby is sufficient for the redo generation rate.
• Consider switching to ASYNC (Maximum Performance mode) if the business allows. This decouples primary commit latency from standby and network performance.
• AFFIRM means the standby must write redo to its own standby redo logs before acknowledging. NOAFFIRM acknowledges on receive. AFFIRM is safer but slower.

Redo log sizing and grouping

Undersized redo logs cause frequent switches, which amplify latency and can trigger checkpoint not complete.

• Add more redo log groups. This gives DBWn more time to checkpoint before LGWR needs to reuse a log.
• Increase redo log group size. A common target is 15-30 minutes between switches under normal load. More than one switch per minute almost guarantees checkpoint issues.
• Check switch frequency with V$LOG_HISTORY.

-- Log switch frequency by hour
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;

These changes buy time but do not fix underlying storage latency. If the redo device is slow, larger logs just delay the problem.

Prevention

• Monitor log file sync and log file parallel write together as a ratio. Alert on either exceeding thresholds, but diagnose using the comparison.
• Keep redo logs on dedicated, fast storage. This is the single highest-value architectural decision for Oracle write performance.
• Establish a baseline for commit latency during normal operation. Static thresholds are noisy; baseline deviation by hour of day catches regressions earlier.
• Test adaptive log file sync behavior under load before relying on it. If your workload triggers thrashing, disable it preemptively.
• For Data Guard SYNC deployments, size network and standby I/O for at least 2x peak redo generation rate.
• Review redo log switch frequency quarterly as workload grows. Sizing that was correct at 1000 TPS may be wrong at 5000 TPS.

How Netdata helps

• Per-second collection of log file sync and log file parallel write makes the ratio that localizes the bottleneck visible without manual SQL sampling during an incident.
• ML-based anomaly detection flags latency spikes and variance changes that static thresholds miss, including the spiky pattern characteristic of adaptive log file sync thrashing.
• Correlating Oracle wait events with OS-level disk latency, CPU run queue, and CPU utilization in a single timeline shows whether log file sync spikes line up with storage contention, CPU saturation, or neither.
• For Data Guard environments, transport lag metrics on the standby correlate with primary log file sync to confirm whether SYNC transport is the source.
• Composite alerting on log file sync (latency elevated, log file parallel write also elevated, TPS meaningful) reduces false positives from transient spikes that do not indicate real problems.

See Oracle Database monitoring with Netdata for the integrated view.

Related guides

• Oracle slow commit cascade: when redo storage degrades and every transaction waits
• Oracle ‘Checkpoint not complete’: redo log sizing, DBWn, and log-switch stalls
• Oracle redo log switch frequency: undersized logs and checkpoint pressure
• Oracle redo generation rate: capacity planning for archiving and Data Guard
• Oracle Database monitoring checklist: the signals every production instance needs
• How Oracle Database actually works in production: a mental model for operators
• Oracle Database monitoring maturity model: from survival to expert
• ORA-00257: archiver error, connect internal only until freed
• Oracle ‘Thread N cannot allocate new log’: the archive hang that masquerades as up
• Oracle archive log destination full: V$ARCHIVE_DEST_STATUS, the ERROR state, and space
• Oracle Fast Recovery Area full: db_recovery_file_dest_size, reclaimable space, and DELETE OBSOLETE
• Oracle RMAN backup failures: V$RMAN_BACKUP_JOB_DETAILS and silent RPO loss