Figure 8-7. Shown is OSM report swpctx.sql, which is a v$system_event wait event report. This report indicates a classic wait event situation with severe log file sync contention. Notice the second top wait event is the typical paired event, log file parallel write.

Solving log file synchronization issues can be painful. First of all, it's a server process (and rarely, but possibly, the database writer background process) that posts the log file sync wait event, not the log writer background process. While there may be a technical reason for the log writer to post a log file sync, in a real production system, I have never seen this occur. This means our solution will nearly always be focused on the server process, which is running application code. Remember that our overall strategy is to reduce commit times.

Rapid commits are nearly always the underlying issue. The good news is that there are multiple ways of addressing the problem. So, even if the application is designed poorly, the performance analyst has a good chance of solving the problem. Figure 8-8 shows the classic Oracle application developer botch. The developer is conscientiously ensuring that the transaction is quickly committed. But unless the application requires the immediate commit, the code forces the log writer background process to immediately flush the redo log buffer, issue an IO write request, and then wait for the IO subsystem to return control to it. Simply moving the commit outside the loop (which would never be executed in the example in Figure 8-8) may be all that is necessary to solve this problem. So, when encountering a log file sync wait event, always suspect the application is issuing rapid commits.