Scops
Docs Download

Oracle problems

Log File Sync Waits: Slow Redo Disks or Too Many Commits, the Queries That Tell Them Apart, and the Fix

3 November 2026

Every commit waits for the log writer to put the transaction's redo on disk, and log file sync is that wait. When it climbs, users feel it on every save button at once. It has two causes that look the same at the top of the wait list, redo that is slow to write and an application that commits far too often, and three queries tell them apart.

What the wait means

On commit, the session asks the log writer (LGWR) to write the redo from the log buffer to the online redo log, and waits until LGWR confirms it. The wait covers the write and the confirmation. A few milliseconds is normal on good storage; tens of milliseconds are felt in every OLTP screen, because every screen commits.

Two neighbours in the same list:

The disk or the commits

Both events since startup, from V$SYSTEM_EVENT. Two readings a minute apart give the current average rather than the lifetime one:

SELECT event, total_waits,
       ROUND(time_waited_micro / 1e6)              AS seconds,
       ROUND(time_waited_micro
             / NULLIF(total_waits, 0) / 1000, 2)   AS avg_ms
FROM   v$system_event
WHERE  event IN ('log file sync', 'log file parallel write')
ORDER  BY event;

Read the two averages together. Parallel write at 8 ms and sync at 9 ms: the redo disk. Parallel write at 1 ms and sync at 25 ms: LGWR is fast and the sessions still wait, which is the commit rate, or a host so busy that LGWR waits for CPU like everyone else. The rate itself is in V$SYSMETRIC, no pack needed:

SELECT metric_name, ROUND(value, 1) AS per_second
FROM   v$sysmetric
WHERE  metric_name IN ('User Commits Per Sec',
                       'User Transaction Per Sec',
                       'Redo Generated Per Sec')
AND    group_id = 2;

Who commits that often

Commits per session since logon, with the rate. A batch job at hundreds a second commits every row:

SELECT s.sid, s.username, s.program, s.module, s.sql_id,
       st.value                                   AS commits,
       ROUND(st.value / GREATEST((SYSDATE - s.logon_time)
                                 * 86400, 1), 2)  AS per_second
FROM   v$sesstat  st
JOIN   v$statname n ON n.statistic# = st.statistic#
                   AND n.name = 'user commits'
JOIN   v$session  s ON s.sid = st.sid
WHERE  s.type = 'USER'
AND    st.value > 0
ORDER  BY per_second DESC
FETCH FIRST 10 ROWS ONLY;

The signature of a row-by-row loader is a single-row INSERT or UPDATE with binds as the current SQL_ID, and a commit count that climbs by the second. The redo log switch rate says whether the logs keep up with the volume at all:

SELECT TO_CHAR(first_time, 'DD HH24') AS day_hour,
       COUNT(*)                          AS switches
FROM   v$log_history
WHERE  first_time > SYSDATE - 1
GROUP  BY TO_CHAR(first_time, 'DD HH24')
ORDER  BY 1;

More than a few switches an hour at a busy time means the logs are small for the rate, and the switch waits join the sync waits.

The fix, in order

  1. Commit less often. This removes the most wait and costs nothing: the COMMIT moves out of the loop and runs once per batch of rows, an ORM runs one transaction per request instead of one per statement. Every commit is one LGWR write; a thousand rows in one commit is one write instead of a thousand.
  2. Put redo on the fastest storage you have. When log file parallel write averages more than a few milliseconds, the redo logs sit on slow or busy devices. Flash, dedicated volumes, away from datafiles and archive destinations; multiplexed members on the same slow device double the wait.
  3. On a Data Guard primary, look at the transport mode. With SYNC transport, the commit also waits for the standby to acknowledge the redo across the network, and the primary shows it as log file sync. FASTSYNC (SYNC NOAFFIRM) lets the standby acknowledge on receipt rather than after its own write; ASYNC removes the wait where the recovery point objective allows it.
  4. Asynchronous commit, only for data you can reload. COMMIT WRITE BATCH NOWAIT, or COMMIT_WAIT = NOWAIT and COMMIT_LOGGING = BATCH for a session, return before the redo is on disk. Oracle's reference says plainly that a failure between the commit and the write loses transactions the application believes are committed: staging loads and log tables, never orders or money.
  5. Give LGWR its CPU. When the host runs flat out, LGWR queues for CPU like every other process and sync time grows with a fast disk underneath. The fix is the CPU problem, not the redo.
  6. Size the redo logs for the rate. Logs that switch every minute at peak add log file switch waits on top; a few switches an hour is the usual target.

See it coming

Commit time is one number per database, and Radar Engine, the detection layer of Scops, watches it live against the usual level of that hour, without an agent and without AWR. Scops samples the sessions every 5 seconds, so the wait history by hour is in the Activity chart on any edition:

DetectorClockWhen it fires
Slow commitsLive, at 5 commits a second or moreWarning. Eight times the usual and 50 ms or more: Critical.
Sudden wait eventLiveA sudden large share of the waits: Watch. The largest one, if it continues: Warning.
Wave in the Commit classLive, against the hour beforeTwice the hour before, well above its usual spread: Watch. With a second sign, such as commits three times slower: Warning.
Watched waitsLivelog file switch (archiving needed), log file switch (checkpoint incomplete), free buffer waits, write complete waits. From 1 active session (0.3 for archiving): Warning. Still rising: Critical.

The finding names the database, the commit time against its usual, and the hour it started, which is usually the hour a loader was deployed or a storage path changed.

Questions

Is log file sync always a disk problem?
No. Compare it with log file parallel write: nearly equal means the disk; many times longer means the commit rate, CPU starvation of LGWR, or a SYNC standby.

What is a normal log file sync time?
A few milliseconds on flash. Tens of milliseconds are felt on every OLTP screen. Radar calls it Critical at eight times the usual level and 50 ms or more.

Does COMMIT NOWAIT fix it?
It hides it. The commit returns before the redo is written, and Oracle documents that a crash in between loses transactions the application was told were committed. Use it only for data you can load again.

Why is log file sync high only on the primary of a Data Guard pair?
Because with SYNC transport the commit waits for the standby's acknowledgement over the network as well. FASTSYNC or ASYNC, where the recovery point objective allows, takes that wait out.

Related problems

Sources