Understanding Log File Sync Wait Events

When a user commits or rolls back a transaction, the session's redo information must be written to the redo log files. The user process signals the LGWR (Log Writer) to perform the write operation, and the LGWR notifeis the user process upon completion. The "log file sync" wait event represents the time the user process spends waiting for this notification from LGWR.

For rollback operations, this event measures the durtaion from when the user issues the rollback command until the rollback is complete.

Excessive occurrences of this wait event may indicate inefficient LGWR performance or overly frequent commit operations. To investigate, monitor:

• The "log file parallel write" wait event
• Statistics such as "user commits" and "user rollbacks" to assess transaction frequency

Resolution Strategies

1. Enhance LGWR Performance: Utilize high-speed storage and avoid placing redo log files on RAID 5 arrays.
2. Implement Batch Commits: Reduce the frequency of individual commit operations.
3. Leverage NOLOGGING/UNRECOVERABLE Options: Where appropriate, use these options to minimize redo generation.

Calculating Average Redo Write Size

Use the following formula to determine the average redo write size:

avg_redo_write_size = (redo_blocks_written / redo_writes) * 512

A high volume of redo generation coupled with small write sizes typically indicates that LGWR is being activated too frequently. This can lead to contention for redo-related latches and may prevent Oracle from effectively utilizing piggyback functionality.

Case Study: Performance Analysis

Examining performance data reveals concurrent "log file sync" and "db file parallel write" wait events, suggesting disk I/O bottlenecks when redo logs and data files share the same RAID storage.

Key Statistics

redo blocks written: 93,853
redo writes: 14,572
redo size: 41,776,508 bytes
user commits: 14,136
user rollbacks: 512

Average Redo Write Calculation

avg_redo_write_size = (93,853 / 14,572) * 512 ≈ 3KB

This small average write size confirms excessive commit frequency.

Latch Contention Analysis

Frequent commits trigger excessive LGWR activation, resulting in "redo writing" latch contention. The redo writing latch ensures that LGWR is not unnecessarily signaled by other processes. When awakened, LGWR:

• Acquires the redo writing latch to update SGA variables indicatinng its activity status
• Takes the redo allocation latch to assess available redo for writing
• Checks for incomplete redo copies and may wait for copy latches to be released
• Updates SGA variables after write completion to free log buffer blocks