10gR2 New Feature: Asynchronous Commit

Again a very good post on a 10gR2 feature by Natalka Roshak. This time she writes about the Asynchronous Commit in 10gR2. See: 10gR2 New Feature: Asynchronous Commit

Asynchronous Commit

By default, Oracle’s commits are durable. Oracle writes your changes to disk and doesn’t return control of the session to you until it’s done. The normal commit process is as follows:

1. User begins transaction.
2. While user is issuing DML, Oracle generates redo entries corresponding to the data changes. These redo entries are buffered in memory while the transaction is occurring.
3. When the user issues a COMMIT, Oracle immediately writes this buffered redo to disk, along with redo for the commit. Ie, a disk I/O is forced.
4. Oracle does not return from the commit until the redo has been completely written to disk (to the online redo log).

There are two important aspects to note here: the redo information is written to disk immediately, and the session waits for the process to complete before returning.

Oracle now lets you change both of these aspects. You can let the log writer write the redo information to disk in its own time, instead of immediately; and you can have the commit return to you before it’s completed, instead of waiting.

Asynchronous commit offers much faster throughput for high-frequent transactions – with the potential, occasional loss of committed data. The article by Natalka clearly explains what the feature entails, how it can be used, what it means and what its dangers are including the administration point of view. Worth a read!