Eliminating Oracle log file sync Wait Events & Redo Log Buffer Bottlenecks (Oracle 19c/23ai)

Executive Summary: The #1 Oracle Performance Bottleneck

n high-concurrency Oracle OLTP databases, log file sync is consistently the most frequent and disruptive wait event reported in AWR (Automatic Workload Repository) and ASH (Active Session History) reports.

When a user session issues a COMMIT statement in PL/SQL or SQL, the transaction cannot complete until the Log Writer (LGWR) process flushes all redo entries for that transaction from the Redo Log Buffer in SGA to the physical Redo Log Files on disk.

If LGWR is delayed by slow disk I/O, CPU starvation, or excessive micro-commits, database foreground sessions freeze in a log file sync wait state, causing application commit latency spikes and session queue pileups.

---

Technical Architecture & Redo Flush Flow

<div class="process-flow">
  <div class="process-step">
    <div class="step-number">Phase 1</div>
    <div class="step-title">User Issue COMMIT (:P10_ID)</div>
  </div>
  <div class="process-arrow">➔</div>
  <div class="process-step">
    <div class="step-number">Phase 2</div>
    <div class="step-title">LGWR / LGNN Worker Flushes SGA Redo Buffer</div>
  </div>
  <div class="process-arrow">➔</div>
  <div class="process-step">
    <div class="step-number">Phase 3</div>
    <div class="step-title">Physical Redo Log Write on NVMe / SAN Storage</div>
  </div>
</div>

---

🔍 Diagnostic Checklist: Is log file sync Bottlenecking Your DB?

Run these diagnostic queries in SQL*Plus or SQL Developer as a DBA user to evaluate redo log performance:

Diagnostic 1: Compare log file sync vs. log file parallel write

A critical distinction in Oracle performance tuning:

• log file sync: Time the user session waits for LGWR to post completion back to the foreground session.
• log file parallel write: Time LGWR actually takes to write the redo block to the physical storage disk.

-- Compare log file sync vs log file parallel write wait times
SELECT 
    event, 
    total_waits, 
    time_waited_micro / 1000 AS total_time_ms,
    ROUND((time_waited_micro / total_waits) / 1000, 2) AS avg_wait_ms
FROM v$system_event
WHERE event IN ('log file sync', 'log file parallel write')
ORDER BY event;

Interpretation of Results:

1. If log file parallel write average wait is HIGH (> 5ms): The root cause is storage I/O latency. Your physical disk storage (SAN/EBS) cannot handle LGWR write speeds.
2. If log file parallel write is LOW (< 2ms) BUT log file sync is HIGH (> 10ms): The root cause is CPU starvation or application micro-commit antipatterns (the disk write is fast, but CPU context switching delays LGWR from posting the user session).

---

Step-by-Step Production Resolution Runbook

1. Fix Application Micro-Commit Anti-Patterns

The most common application cause of log file sync is executing COMMIT inside a tight loop:

-- BAD ANTI-PATTERN: Committing on every single loop iteration
FOR i IN 1..100000 LOOP
    INSERT INTO audit_log VALUES (i, SYSDATE);
    COMMIT; -- Triggers 100,000 separate LGWR disk flush requests!
END LOOP;

The Fix: Batch Commits

-- RECOMMENDED: Batch commits every 5,000 records
FOR i IN 1..100000 LOOP
    INSERT INTO audit_log VALUES (i, SYSDATE);
    IF MOD(i, 5000) = 0 THEN
        COMMIT;
    END IF;
END LOOP;
COMMIT;

---

2. Optimize Commit Logging Parameters (COMMIT_LOGGING & COMMIT_WAIT)

For non-financial high-volume logging or staging tables, you can configure asynchronous batch commits at the session or system level:

-- Configure asynchronous batch commits for the bulk loading session ONLY
ALTER SESSION SET COMMIT_LOGGING = BATCH;
ALTER SESSION SET COMMIT_WAIT = NOWAIT;

⚠️ DURABILITY WARNING: Setting COMMIT_WAIT = NOWAIT returns control to the user session immediately before LGWR flushes redo to disk. If an instance crash occurs before LGWR completes the physical write, committed transactions in that window could be lost. Never issue COMMIT_WAIT = NOWAIT instance-wide via ALTER SYSTEM; reserve it strictly at the ALTER SESSION level for non-financial staging or ETL workloads.

---

3. Dedicated Storage & NVMe RAID-10 for Online Redo Logs

Online Redo Logs perform sequential, synchronous write operations. Placing redo log files on storage shared with datafiles or temp files creates disk contention.

Best Practices for Redo Storage:

• Dedicated Flash / NVMe LUNs: Isolate online redo log diskgroups (+RECO or +REDO) onto dedicated high-speed NVMe or SSD storage.
• RAID 10 Over RAID 5/6: Never place online redo logs on RAID 5 or RAID 6 arrays. Parity generation delays LGWR writes. Always use RAID 10 or raw ASM mirror groups.
• 4K Sector Size: Format ASM diskgroups housing redo logs with 4KB sector size (SECTOR_SIZE=4096) to match modern NVMe drive architecture:

-- Create dedicated Redo Diskgroup with 4KB sector size
CREATE DISKGROUP REDO EXTERNAL REDUNDANCY
  ATTRIBUTE 'sector_size'='4096'
  DISK '/dev/oracleasm/disks/NVME_REDO_01';

---

4. Optimize Redo Log File Sizing

If redo log files are undersized, Oracle issues frequent log switches, triggering log file switch (completion) and checkpoint incomplete wait events.

-- Check frequency of redo log switches per hour
SELECT 
    TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS log_hour,
    COUNT(*) AS switches_per_hour
FROM v$log_history
WHERE first_time >= SYSDATE - 1
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY log_hour DESC;

• Rule of Thumb: Redo log switches should occur no more than 2 to 4 times per hour during peak load. If log switches exceed 10+ per hour, resize online redo logs to 4GB or 8GB members.

-- Add larger 4GB Redo Log Groups
ALTER DATABASE ADD LOGFILE GROUP 4 ('+REDO') SIZE 4G;
ALTER DATABASE ADD LOGFILE GROUP 5 ('+REDO') SIZE 4G;
ALTER DATABASE ADD LOGFILE GROUP 6 ('+REDO') SIZE 4G;

📚 Official Documentation & Technical References

• Oracle Database Database Performance Tuning Guide 19c - Resolving Redo Log & Wait Event Bottlenecks — Official documentation on tuning LGWR performance and wait events (log file sync, log file parallel write).
• Oracle Database Reference 19c - V$SYSTEM_EVENT — Reference guide for analyzing instance-wide wait event statistics.
• Tanel Põder: Troubleshooting Log File Sync Waited Too Long — Technical analysis of redo log buffer flushing mechanisms and OS storage I/O queues.

---

Need an AWR/ASH performance review of your Oracle Database wait events or LGWR storage latency? Contact our Performance Tuning Specialists or explore our Enterprise Health Audits.