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).

If rapid committing is an issue, it can be helpful to gain a good understanding of your application's commit or the transaction rate. The commit rate data is gathered from the user commits statistic from v$sysstat or v$sesstat. The transaction rate also includes the rollback statistic user rollbacks. In a Statspack or AWR report, commit and rollback rates can be found in the Instance Activity section, and the transaction rate is shown in the workload portion of the report. If you have been capturing performance information, there is a very good chance you have been capturing this data. If the log file sync wait event suddenly becomes a problem, check if the commit rate has also suddenly increased. If a correlation does exist, you can start looking for the reason the application is suddenly experiencing a significant commit rate increase.