Now let's take a look at what the various options actually do. The first commit_write option is either immediate or batch. The immediate option simply posts the log writer to write, which is the normal behavior. The batch option buffers the commits, allowing them to remain and accumulate in the redo log buffer while returning a commit completed response back to the server process. The second option is either wait or nowait. The wait option instructs the log writer background process to perform a synchronous write, which means control will not be returned to the log writer background process until its write has completed. This is normal behavior. The nowait option tells the log writer background process to write asynchronously, which means immediately after the log writer background process issues the write to the operating system, control is normally returned to the log writer, even though the write has not physically completed. This is typical asynchronous behavior, but not the default log writer background process and Oracle commit behavior. Now let's put these options together to do something potentially useful.

The batch option can be used with either the nowait or wait options. The batch option improves performance by allowing redo entries to build up-that is, to accumulate in the redo log buffer even after a commit is issued. Normally, every commit forces the log writer background process to flush the log buffer and write to the current online redo log group. There is overhead in this activity, so by reducing its frequency, the overhead is obviously reduced. If an application or a specific application session frequently commits, this option can be used to reduce the effective commit rate instead of modifying the application code (though personally, I would much rather have the code optimized to begin with).

The nowait option can also be used with either the immediate or batch options. Normally, the log writer background process writes synchronously, which means the operating system will not return control to the log writer until the write has been physically completed. Of course, the underlying IO subsystem could also be caching the write and telling the operating system the write has been physically completed when it has not. Asynchronous writes are typically many times faster than synchronous writes, resulting in a substantial performance improvement.