We would expect the combined batch and nowait options to perform the best, as the redo entries can be remain buffered longer in the log buffer, resulting in more efficient writes, and the log writer background process can perform asynchronous writes, resulting in a near immediate commit command response time. Just to give you a taste of how well the commit write facility can work, I performed a simple experiment. Table 8-1 shows the results.

The work was measured by the number of average commits performed each second. To ensure the experiment was statistically significant, multiple 600-second samples were taken, and the standard deviation calculated. The workload placed on the four-CPU core Linux Oracle Database 10g Release 2 system was a mix of two sessions performing rapid, small updates and two sessions performing small inserts. The rapidness was produced by placing the update or the insert within a very tight PL/SQL loop. After each update, a commit was issued; after 50,000 inserts, a commit was issued.

As a reminder, the immediate, wait option is normal Oracle behavior and should serve as a baseline. As expected and as Table 8-1 shows, the top wait event is clearly log file sync, which is the server process waiting for the commit to finish. The log file sync percentage change is not overly relevant because the workload was allowed to increase. So, any response-time gain could be offset by an increase in throughput. This trade-off will be discussed in detail in the next chapter. The second top wait event, also shown in Table 8-1, was always log file parallel write, which is the time the log writer background process is actually waiting for the write to complete.