The log writer background process's job is to quickly flush the redo log buffer. As with the database writer, multiple events trigger the log writer background process to write. Because Oracle promises that committed transaction information has been written to disk (as far as Oracle knows), the obvious log writer background process requirement is to flush the redo log buffer when a transaction commits. But to keep the redo flowing smoothly and quickly, there are also a number of other events that trigger the log writer background process into action.4

While redo is generated and copied into the redo log buffer as part of DML processing, when a commit is issued, control is not returned to the server process until all of the associated transaction redo has been successfully written to an online redo log group. While the server process is waiting for the log writer to give it control once again, it will post the log file sync wait event. In fact, the average log file sync wait time is a good indication of the average commit time from an Oracle perspective.

Once when I was doing some on-site consulting work, the DBA received a call from a user complaining that saving images in an application was taking much longer today than it did yesterday. The caller wanted to know what was wrong with the Oracle system. Oracle had been gathering Statspack data, so the DBA ran a quick query that listed the log file sync times from the previous day and also the current day. We could both see that the log file sync times were essentially the same. This meant any save-related issues the user was experiencing were not because of Oracle, but caused somewhere between the database and the end user. After doing some investigation, the DBA discovered the problem was blockage at the application server level. So it wasn't a database issue after all! This is yet another creative application of Oracle's wait interface.