Decreasing log file sync waits Oracle Tips by Burleson Consulting

 

 

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. 

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.