This snippet first shows the current commit_write setting, which is null, meaning it has not been set. The second entry alters only the session, and the third alters the entire instance. Then the parameter is once again shown to reflect the alterations. Finally, the modified commit statement was entered directly, overriding any instance parameter, session, or system setting.

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