Log Writer (LGWR): Commit Durability, Flush Triggers, and Tuning

LGWR is the single background process that guarantees commit durability. It sequentially writes redo from the redo log buffer (in the SGA) to the online redo logs on disk. Data block writes are decoupled-those are DBWn’s job- so COMMIT returns success as soon as LGWR confirms the redo is on persistent storage (write-ahead logging).

When does LGWR write?

• On COMMIT (and some ROLLBACK/DDL): LGWR flushes all redo for the transaction; sessions can “group commit” if multiple commits pile up while a write is in progress.
• Timed / threshold flush: every few seconds and when the buffer reaches internal size thresholds, to keep latency predictable.
• Before DBWn writes: to uphold write-ahead logging (redo must be durable before dirty buffers can be written).
• Log switch boundaries: finishing the tail of redo when switching to the next online redo log.

What LGWR actually writes

• Redo records describing all DML/DDL changes.
• Commit record with the transaction’s SCN. The atomic persistence of this record marks a successful COMMIT.
• Multiplexed members: LGWR writes to all members of the current redo group; if one member fails, it continues with the others and logs the error.

Group commit, clarified

If commits arrive quickly, LGWR batches them into one I/O. Each transaction still observes full durability semantics, but many commits share the same physical write, lowering IOPS and latency variance.

LGWR vs. DBWn (don’t mix them up)

• LGWR: sequential redo writes; determines COMMIT latency and durability.
• DBWn: writes dirty data buffers later for throughput and checkpointing (not tied to COMMIT).
• CKPT: advances checkpoints and updates headers; does not write data blocks itself.

Data Guard note

In protected configurations, LGWR can stream redo to standbys (SYNC/ASYNC). Standby transport/ack policies affect COMMIT latency; choose settings that match RPO/RTO goals.

Monitoring and Diagnostics

-- High-level redo activity
SELECT name, value
FROM   v$sysstat
WHERE  name IN ('redo size', 'redo writes', 'redo write time',
                'user commits', 'redo wastage');

-- Commit and LGWR wait profiling (system-wide since startup)
SELECT event, total_waits, time_waited/100 AS seconds_waited
FROM   v$system_event
WHERE  event IN ('log file sync','log file parallel write')
ORDER  BY time_waited DESC;

-- Current online redo log status/cycling
SELECT l.group#, l.bytes/1024/1024 AS size_mb, l.status, lf.member
FROM   v$log l
JOIN   v$logfile lf USING (group#)
ORDER  BY l.group#, lf.member;

-- Recent switch cadence (helps right-size log files)
SELECT sequence#, first_time, next_time
FROM   v$log_history
ORDER  BY first_time DESC
FETCH FIRST 20 ROWS ONLY;

Tuning Playbook (commit latency and throughput)

1. Place redo on low-latency storage; separate from datafiles/flash for predictable write times.
2. Right-size online redo logs to achieve a steady switch interval (avoid thrash or hour-long gaps).
3. Avoid excessive per-row commits; batch work where semantics allow to benefit from group commit.
4. Validate async I/O and queue depth on the redo device; watch log file parallel write times.
5. Be cautious with COMMIT WRITE settings (e.g., NOWAIT/BATCH); only change if you fully understand durability and ordering implications.

Common misconceptions

• "DBWn makes the commit durable." False-only LGWR durability matters for COMMIT.
• “Bigger log buffer fixes commit stalls.” Usually false; stalls are typically storage-latency or switch-thrash issues.
• “More DBWn improves LGWR latency.” Unrelated subsystems; focus on redo path for LGWR.