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.

There are times when the application truly demands an immediate commit, which can result in a tremendous commit rate. For example, I know of systems where financial transactions arrive from multiple sources. The transactions are small, but because they are independent transactions, the legal and business rules require that each individual incoming transaction must be immediately committed to the database. In this situation, you have little choice but to devise a solution that does not involve changing the application.