Database High "Log file sync"
Executive Summary
In a high-concurrency Oracle Database environments, log file sync storms almost always point to slow underlying storage. However, when kernel-level block layer metrics (biolatency, biotop) and SAN array reports prove that physical disk writes complete in under 0.5 milliseconds, while LGWR trace files simultaneously report 500ms to 1,017ms write stalls, the bottleneck lies in the software I/O stack above the block layer.
This case study documents a production incident on Oracle Database 19.23 (RAC) running on Red Hat Enterprise Linux (RHEL) 8.4, where Average Active Sessions (AAS) waiting on log file sync surged to 397–500 sessions (98% of database wait time). By correlating Oracle ASH data, LGWR trace logs, eBPF block-layer latency histograms, and Linux /proc/<pid>/stack , we isolated the root cause to the Oracle ASM Filter Driver (oracleafd) stalling inside kernel work queue flushes (disk_block_events -> flush_work) during synchronous I/O submission. Migrating the Redo Log ASM diskgroup from AFD to direct Linux udev block device symlinks immediately dropped log file sync waits from ~500 active sessions down to 6–8 sessions.
1. Environment Profile
|
Specification |
Component |
|
Oracle Database 19c Enterprise Edition Release 19.23.0.0.0 (RAC) |
Database Version |
|
Red Hat Enterprise Linux 8.4 (x86_64) |
Operating System |
|
Oracle ASM with ASM Filter Driver (oracleafd.ko) over Multipath (up-* block devices) |
Storage Management |
|
use_adaptive_log_file_sync = FALSE (Post/Wait notification mode disabled) |
Adaptive Log File Sync |
|
use_single_log_writer = TRUE (Single master LGWR process without LGnn worker slaves) |
Log Writer Mode |
2. Symptoms & Initial Database Investigation
Following reports of severe slowness in database, initial database investigation using Active Session History (ashtop) revealed an extreme serialization bottleneck on redo commit processing.
Evidence : ASH Top Wait Events During Incident
TOTALSEC AAS %This EVENT2 FIRST_SEEN LAST_SEEN
-------- -------- ------- ------------------------------------------ ------------------- -------------------
185880 397.2 98% | log file sync 2026-08-17 16:56:00 2026-08-17 17:03:47
920 2 0% | ON CPU 2026-08-17 16:56:00 2026-08-17 17:03:45
659 1.4 0% | latch: enqueue hash chains 2026-08-17 16:56:33 2026-08-17 17:03:45
447 1 0% | log file parallel write 2026-08-17 16:56:00 2026-08-17 17:03:47
230 0.5 0% | buffer busy waits [data block] 2026-08-17 16:57:23 2026-08-17 17:02:47
124 0.3 0% | buffer busy waits [1st level bmb] 2026-08-17 16:57:23 2026-08-17 17:02:37
119 0.3 0% | control file sequential read 2026-08-17 16:56:16 2026-08-17 17:03:19
[Evidence : LGWR Hidden Parameters Verification
SQL> show parameter _use_adaptive_log_file_sync
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_use_adaptive_log_file_sync string FALSE
SQL> show parameter single
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_use_single_log_writer string TRUE
Evidence : LGWR Trace File Slow Write Warnings
*** 2026-08-17T14:33:57.013482+03:30
Warning: log write elapsed time 505ms, size 904KB
*** 2026-08-17T14:34:00.597643+03:30
Warning: log write elapsed time 1017ms, size 1557KB
*** 2026-08-17T14:34:01.622148+03:30
Warning: log write elapsed time 510ms, size 2919KB
*** 2026-08-17T14:34:02.646038+03:30
Warning: log write elapsed time 508ms, size 2088KB
*** 2026-08-17T14:34:03.157788+03:30
Warning: log write elapsed time 509ms, size 1516KB
3. Storage & Linux Block Layer Verification
The storage team verified healthy sub-millisecond response times on the SAN array. We checked this at the Linux OS level using ps, biotop, and biolatency, proving that physical block I/Os for LGWR (PID 67319) were completing in 0.36ms to 0.51ms:
Evidence Figure: LGWR Process in Uninterruptible Sleep (D State)
[oracle@db1 tools]$ ps -eo pid,user,state,wchan:20,cmd | awk '$3 ~ /D/'
67319 oracle D - ora_lgwr_db1
Evidence: Per-Disk LGWR Latency via biotop
17:08:42 loadavg: 9.32 12.04 13.70 8/4600 122169
PID COMM D MAJ MIN DISK I/O Kbytes AVGms
67319 ora_lgwr_histad W 252 97 up-97 34 6633.5 0.51
67319 ora_lgwr_histad W 252 154 up-154 46 4686.5 0.39
67319 ora_lgwr_histad W 252 440 up-440 49 4412.5 0.36
67319 ora_lgwr_histad W 252 326 up-326 47 4011.5 0.36
[Evidence Figure: Block Device I/O Histogram via biolatency — File: Screenshot 2026-08-17 170944.png]
[oracle@hdb1 tools]$ sudo /usr/share/bcc/tools/biolatency
usecs : count distribution
32 -> 63 : 30571 |************************************** |
64 -> 127 : 18625 |*********************** |
128 -> 255 : 31735 |****************************************| --> Histogram shows most lgwr write times completes below 500 micro seconds.
256 -> 511 : 3889 |**** |
512 -> 1023 : 453 | |
4. Root Cause Analysis: Inspecting the LGWR Kernel Stack
Sampling /proc/67319/stack on the LGWR process revealed that LGWR was stalling inside the Oracle ASM Filter Driver (oracleafd) kernel module during ioctl-based I/O submission:
Evidence Figure: Kernel Stack Trace of LGWR (PID 67319)
[root@db1 ~]# cat /proc/67319/stack
[<0>] flush_work+0x11d/0x1d0
[<0>] __cancel_work_timer+0x105/0x190
[<0>] disk_block_events+0x6f/0x90
[<0>] __blkdev_get+0x70/0x510
[<0>] blkdev_get+0xaf/0x180
[<0>] blkdev_get_by_dev+0x2f/0x40
[<0>] safd_bio_submit+0x16b/0x240 [oracleafd]
[<0>] afdq_batch_submit+0xfb9/0x1300 [oracleafd]
[<0>] afdc_io+0xb3d/0xff0 [oracleafd]
[<0>] afdc_dispatch_ioctl+0x1af/0x1340 [oracleafd]
[<0>] afdc_execute_ioctl+0x1f3/0x550 [oracleafd]
[<0>] afd_ioctl+0x87/0x300 [oracleafd]
Inside safd_bio_submit [oracleafd], the driver invokes blkdev_get_by_dev() -> __blkdev_get() -> disk_block_events() -> __cancel_work_timer() -> flush_work(). Because this synchronous workqueue flush happens inside oracleafd BEFORE the BIO is handed off to the Linux block layer, biolatency recorded <1ms disk writes while LGWR suffered 500ms+ stalls.
5. Remediation & Results
We provisioned a new Redo ASM diskgroup directly using Linux udev symlinks (bypassing Oracle AFD) and migrated the Online Redo Logs online. Immediately after switching redo logs to the udev-backed diskgroup, active sessions on log file sync dropped from ~500 down to 6–8.2:
Evidence : Post-Remediation ASH Wait Profile Total
Seconds AAS %This EVENT2
------- ------- ------- ---------------------------------------------
2459 8.2 43% | log file sync
1404 4.7 25% | ON CPU
693 2.3 12% | db file sequential read
296 1.0 5% | db file async I/O submit
261 .9 5% | log file parallel write