Trade risk for speed with Oracle 10g's asynchronous COMMIT

A new option to the COMMIT statement in Oracle 10g can defer the writing of committed data to the online redo log files. This involves a risk of data loss, but it speeds up the transaction response time. Find out more details in this tip.

One thing that Oracle DBAs know for a fact is that a transaction isn't committed until it is written to the online redo log files. If a COMMIT statement in an application completes, the application can trust that the data is protected against instance failure, and can be recovered by "rolling forward" the log files. In Oracle 10g, that's no longer always true.

For all the safety and comfort level of Oracle's default COMMIT behavior, it does introduce a delay in transaction processing. Very high speed transaction environments are slowed down by the need to confirm writing each individual transaction to the log files before the application can continue with the next transaction.

Weekly Oracle tips in your inbox
TechRepublic's free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more.
Automatically sign up today!

To accommodate such applications, Oracle 10g now includes an option on the COMMIT statement to return immediately after the COMMIT is issued, rather than waiting for the log activity to complete. Another option batches multiple transactions together in memory before writing to the disk.

The COMMIT statement has a new clause, WRITE, which indicates these options. The default COMMIT statement is the same as:


To specify that the transaction should be written individually but that the application should not wait for it, specify:


or just


Likewise, to specify that Oracle's log writer process (LGWR) is allowed to batch multiple transactions together before writing, specify:


The option can also be set system-wide by modifying the commit_write instance parameter. Multiple options are separated by commas, as:

SET commit_write = BATCH, NOWAIT;

It's critical to understand the implications of these options before choosing to use them in an application. If an instance were to crash after the COMMIT statement returns to the application, but before the actual write to disk, the application would believe that transaction had committed. The SMON background process, however, would roll the transaction back during automatic recovery when the instance is restarted. The physical log files would not contain a COMMIT marker because one was never actually written.

Fortunately, Oracle instances don't crash that often, and multiplexed online redo logs provide a measure of protection against a single log file failure. If the appropriate checks are built into the application, this option can be of benefit.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.