log file sync: Wait: session waiting for LGWR to flush redo to disk on commit
OracleWhat does it mean?
When a session COMMITs, it waits for LGWR to write (flush) the redo records to the redo log files. If this wait is high, there is commit latency.
Why does it happen?
Slow redo log disks (high I/O latency), an application that commits too frequently (row-by-row commit), CPU pressure on the LGWR process, or very high redo volume.
How to fix it
Measure the average wait time with AWR/Statspack. Move the redo log files to fast, low-latency disks (SSD). Reduce unnecessary commit frequency in the application and group batch operations. If necessary, evaluate the COMMIT_LOGGING/COMMIT_WAIT (commit_write) options against business requirements; WARNING: NOWAIT/BATCH reduce durability, and transactions that appear committed can be lost in a crash. Use them only at session or statement level for work where loss is tolerable; do not enable them system-wide. Examine LGWR and storage latency together.
More from this engine
- ORA-01722Invalid number
- ORA-02049Timeout: distributed transaction waiting for lock
- enq: TX - row lock contentionWait: session blocked on a row-level lock held by another transaction
- db file sequential readWait: single-block physical read, typically an index or table lookup
- db file scattered readWait: multi-block physical read, typically a full table or index fast full scan
- buffer busy waitsWait: contention for a buffer already being read or modified by another session
- gc buffer busyWait (RAC): contention for a cache-fusion block being transferred between instances