In the Oracle database, when a user commits or rolls back a transaction, the redo data for the session (that’s currently held in memory) has to be written to the redo log file on disk.<\/p>\n
The user session tells the LGWR process to write the contents of the log buffer to the redo log file. Note that all currently unwritten redo data is written, not just that belonging to this user session.<\/p>\n
After the LGWR process has finished writing the redo data to the redo log file it notifies the user session that it’s finished. While the user session is waiting to be notified that all redo data has been written, it waits on the ‘log file sync’ wait event.<\/p>\n
Any other sessions that also want to commit during the log file sync will also wait on the ‘log file sync’ wait event.<\/p>\n
If your database is experiencing lots of waits for the ‘log file sync’ wait event, there are two common reasons:<\/p>\n
1. The LGWR process is slow writing to disk.<\/p>\n
2. Your application is doing too many commits.<\/p>\n
To check if the LGWR process is being slow when writing to disk, check the ‘log file parallel write’ background wait event. The LGWR process waits on this event while the actual write to disk of the redo data takes place. So if you see high waits for both ‘log file sync’ and ‘log file parallel write’ then most of the wait for ‘log file sync’ is due to waiting for the actual physical write to disk, i.e. I\/O.<\/p>\n
If this is the case, there are a number of possible steps you can take. For example, make sure your redo logs are on dedicated disks, move the redo logs to faster disks, check the size of the log buffer and the redo logs. If your redo logs are too small, log switches will be happening too often. It’s recommended that you aim for log switches every 15 – 20 minutes. If the log buffer is too big, the LGWR process may have to write too many blocks at a time.<\/p>\n
If the average wait time for ‘log file sync’ is much higher than the average wait time for ‘log file parallel write’ then most of the time is not being spent waiting for the redo data to be written to disk. Instead most time is being spent waiting for CPU time and this indicates your application may be committing too often.<\/p>\n
Similarly, if the ‘log file sync’ waits is high but the average wait time is low, this is also an indication that too many commits are taking place.<\/p>\n
If this is the case, you should check if your application is doing lots of short transactions that could be grouped together to reduce the number of commits.<\/p>\n","protected":false},"excerpt":{"rendered":"