High "log file sync"

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