Log File Sync Averaging 10 ms While Redo Writes Average 0.6 ms
SALESDB is a 19c single-instance OLTP database with a 4-hour peak from 10:00 to 14:00. The AWR report for that window shows 240 minutes elapsed and 1,920 minutes of DB time. Foreground waits break down as DB CPU 40 percent, log file sync 30 percent with an average of 10 ms at about 240 commits per second, db file sequential read 15 percent with an average of 0.3 ms, and enq: TX - row lock contention 3 percent. The log file sync histogram is strongly bimodal: 96 percent of waits complete in under 1 ms, while 4 percent take 180 to 250 ms. The background event log file parallel write averages 0.6 ms. Redo consists of four groups of 512 MB, switching about 30 times per hour, and the online redo logs share a LUN with the fast recovery area. An RMAN archived log backup runs from 10:00 to 14:00 each day.
1. Is the redo I/O subsystem the bottleneck? Support the conclusion with the numbers in the report rather than with general best practice.
Read the worked answer
Do the arithmetic before touching storage. The 4-hour window is 14,400 seconds; DB time of 1,920 minutes is 115,200 seconds, so average active sessions are 8. Log file sync at 30 percent of DB time is 34,560 seconds of foreground wait. At 240 commits per second over 14,400 seconds there are about 3.46 million commits, so 34,560 / 3.46 million gives the reported 10 ms average.
The average is the wrong statistic here. If 96 percent of waits finish in under 1 ms, those 3.3 million waits account for roughly 2,700 seconds. The remaining 4 percent, about 138,000 waits, absorb about 31,800 seconds, or roughly 220 ms each. So the entire problem is a tail, not a baseline, and log file parallel write at 0.6 ms says the write call itself is fast when it is fast.
A uniform disk latency problem would raise the baseline and log file parallel write together. It would not produce a bimodal histogram. The 0.6 ms background average is a periodic average that hides spikes, and it cannot rule the storage in or out on its own. Cross-check with V$EVENT_HISTOGRAM for log file sync, the ASH top events minute by minute, and the p3 value of log file sync, then look for what else is active during the tail.
2. The tail waits cluster in 10- to 20-minute blocks inside the peak. What is your leading hypothesis, and what would you change first?
Read the worked answer
The most likely cause is the overlapping archived log backup. It shares a LUN with the online redo logs and saturates the path for sustained periods, so LGWR's write occasionally queues behind large sequential I/O; when that happens the foreground waits a long time, and between bursts it waits almost not at all. That matches both the bimodality and the clustering.
The first change is therefore scheduling, not hardware: move the archived log backup out of the 10:00 to 14:00 window, or redirect it to a different LUN, and if it must run in the window, reduce its impact by backing up incrementally and by using a separate device. Only after that should you resize redo: with 30 switches per hour and 512 MB groups, a switch happens every two minutes, which is frequent enough that a stall at switch time can create a burst of sync waits, so growing the groups to 2-4 GB and reducing switches per hour is worthwhile, but it is not the fix for the tail.
Separately, 240 commits per second from an application that commits per row is expensive by construction. Agreeing on a batched commit boundary reduces the wait count, but it must be an explicit durability decision with the business, not something done unilaterally.
3. Which specific AWR and ASH evidence would confirm or refute the hypothesis, and what would a wrong fix look like?
Read the worked answer
Confirm by aligning the two datasets. In ASH, group samples by minute for the peak window and filter on session state waiting for log file sync; the minutes with high sample counts should coincide with the backup's active intervals in V$RMAN_BACKUP_JOB_DETAILS or V$SESSION_LONGOPS. Check whether the top background session in those minutes is the RMAN channel rather than LGWR sitting on I/O. Also pull the AWR foreground wait histogram and the instance CPU section: if the host is only about 34 percent busy across 16 cores, CPU starvation of LGWR is an unlikely explanation, and the case for contention on the shared LUN strengthens.
A wrong fix looks decisive and changes nothing measurable. Moving the redo logs to "faster" disks when the write path already completes in 0.6 ms treats the average and ignores the tail. Adding redo groups without changing the backup schedule leaves the real contention in place. Setting COMMIT WRITE NOWAIT or IMMEDIATE trades a performance number for durability risk.
After the change, rerun the same 4-hour AWR report and compare log file sync total DB time and the 99th percentile, not just the average, because the average was already misleading before the fix.