Question: I have “log file sync waits” in my top-5 timed events. How do I tune to reduce the log file sync wait events?
Answer: The log file sync wait occurs at the end of a transaction and the database writer (DBWR) must wait for the log file sync. Oracle guru Steve Adams notes details on how Oracle processes log file sync waits:
“Before writing a batch of database blocks, DBWn finds the highest high redo block address that needs to be synced before the batch can be written.
DBWn then takes the redo allocation latch to ensure that the required redo block address has already been written by LGWR, and if not, it posts LGWR and sleeps on a log file sync wait.”
Some solutions to log file waits include moving the online redo logs to super-fast SSD storage and increasing the log_buffer size above 10 megabytes.
Some also suggest that you can remove the “log file sync waits” by moving your redo logs to SSD.