Once when I was doing some on-site consulting work, the DBA received a call from a user complaining that saving images in an application was taking much longer today than it did yesterday. The caller wanted to know what was wrong with the Oracle system. Oracle had been gathering Statspack data, so the DBA ran a quick query that listed the log file sync times from the previous day and also the current day. We could both see that the log file sync times were essentially the same. This meant any save-related issues the user was experiencing were not because of Oracle, but caused somewhere between the database and the end user. After doing some investigation, the DBA discovered the problem was blockage at the application server level. So it wasn't a database issue after all! This is yet another creative application of Oracle's wait interface.

It is important to understand that Oracle trusts the operating system when it indicates that the redo has been written to disk successfully. If the operating system returns a successful write, yet that write did not physically occur, and then a media failure occurs, the database may not be completely recoverable. I have seen failures of lithium backup batteries, disk mirroring software, and physical drives result in the database system becoming unavailable. While optimal performance may not be achieved, it may be in everyone's best interest to ensure when Oracle receives the message, "Sure, the data has been written to disk," it actually has been written to disk. Achieving this performance versus availability balance can be very tricky indeed.

Oracle Database 10g Release 2 introduced the commit write facility, which gives the appearance a commit has completed when it really hasn't. If commit times are taking too long (log file sync will be the top wait event) and the application can survive the possibility of lost data5 in an unfortunate instance failure, then technically, one of the commit write options may provide the extra performance boost you need. But please, ensure your application and the business can tolerate the potential loss of committed data. As I tell people, slower performance is better than no performance.