High Commit Rate and log file sync Latency
A 19c OLTP database runs on a 16-core host with 64 GB RAM. Between 10:00 and 11:00, application timeouts occur. The AWR report for that hour shows DB time of 54,000 seconds over 3,600 seconds elapsed. The top wait is log file sync: 58% of DB time, about 5,000 waits per second, average 3 ms. log file parallel write averages 0.4 ms. The application commits after every single-row insert; average redo is 120 MB/s. Redo logs are four groups of 512 MB, and log switches occur about 20 times per hour. The storage team reports 0.3 ms write latency on the redo LUNs. The DBA proposes increasing log_buffer from 128 MB to 1 GB and adding redo log groups.
1. What does the gap between log file sync and log file parallel write tell you, and why is log_buffer unlikely to help?
Read the worked answer
5,000 waits/s x 3 ms = 15 seconds of wait per second, about 15 average active sessions; that is consistent with the 54,000 s DB time. Since log file parallel write is only 0.4 ms, the redo write itself is not the bottleneck. The remaining about 2.6 ms per commit is spent after the write: waking the foreground, scheduling it on CPU, and running post-commit code. Increasing log_buffer does not change commit durability; it only gives LGWR more staging space. At 120 MB/s with 128 MB buffer, buffer space waits are unlikely; check log buffer space in the AWR to confirm. More redo groups might reduce log switch waits but not log file sync. The fix is to reduce commit frequency or reduce CPU/scheduling latency for the foreground and LGWR.
2. Which diagnostics would you run next, and what would each prove?
Read the worked answer
Use ASH (V$ACTIVE_SESSION_HISTORY) filtered on event='log file sync', grouping by blocking_session, session_state, and program; if the foreground is runnable rather than waiting, CPU starvation is likely. Check Host CPU and Load Profile in AWR for run queue and CPU usage; 15 AAS on 16 cores leaves little headroom if the host is also running other work. Check LGWR priority and whether LGWR is competing with foregrounds. Query V$EVENT_HISTOGRAM for log file sync to see whether the average hides a long tail. Check log file switch completion and log buffer space for redo configuration problems. Compare commit rate and redo size with V$SYSMETRIC_HISTORY. If ASH shows ON CPU inside log file sync, tuning the redo LUNs will not help.
3. Recommend changes in priority order, including application-side and database-side options.
Read the worked answer
First, reduce commit frequency: batch the single-row inserts into transactions of 50-100 rows or use a deferred commit pattern; this can cut waits by an order of magnitude and is the highest-leverage change. Second, check CPU: if run queue is high, reduce concurrent sessions or move work; ensure LGWR gets CPU and I/O priority. Third, confirm redo I/O is not shared with data files and that the LUN latency is stable at peak. Fourth, if log switches or checkpoints contribute, add redo groups or increase size, but 20 switches/hour is not excessive. Do not use COMMIT_WRITE=NOWAIT unless the business accepts lost transactions; it trades durability for latency. Increasing log_buffer is low priority. Re-measure log file sync average and p95 after each change.