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.

Figure 8-8. Shown is a fantastic way to cause severe log file sync performance issues. Just have a few Oracle schemas run this script, and there is a very good chance of seeing log file sync as your top wait event. This is one of the scripts used during the commit write facility experiment presented earlier in this chapter (with the results shown in Table 8-1).