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.

As Figure 8-9 shows, looking for the log file sync wait event SQL is very straightforward. The first method shown samples directly from v$session. Starting with Oracle Database 10g, real-time wait event details are included directly in v$session, allowing for SQL identification based on any wait event. However, if you do not want to repeatedly sample from v$session (which is not recommended), and if you have a license to use the active session history (ASH) facility (discussed in Chapter 5), the second example shows how you can also easily locate log file sync-related SQL from v$active_session_history. Since ASH data is buffered for perhaps less than an hour, if you want prior data, you can sample from the dba_hist_active_sess_history view, as shown in the third example in the figure. If you are performing your analysis based on a Statspack or AWR report, look for the highest total elapsed time DML SQL. While solving log file sync issues can be difficult, identifying the associated SQL is not.

Figure 8-9. Shown are three ways to gather the SQL associated with the wait event log file sync, or with a slight modification, any wait event.