Author: Natalka Roshak

Oracle 10gR2 asynchronous commit feature enables database administrators to trade durability for transaction speed. Asynchronous commit offers much faster throughput for high-frequent transactions with the potential, occasional loss of committed data. Asynchronous commit allows transactions to complete more quickly.

COMMIT Behavior:

In the normal commit process the transaction is started by a user. Oracle generates redo entries corresponding to the data changes while user is issuing DML. These redo entries are buffered in memory while the transaction is occurring. When the user issues a COMMIT, Oracle immediately writes this buffered redo to disk, along with redo for the commit. Oracle does not return from the commit until the redo has been completely written to the online redo log. The redo information is written to disk immediately and the session waits for the process to complete before returning.

Oracle enables you to 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.

Changing Oracle COMMIT Behavior:

You can change Oracle's commit behavior at the statement level by simply specifying one or both of the options with your commit. Use the new WRITE clause of the commit statement for this purpose. The full syntax of the WRITE clause is as under

COMMIT [WRITE [IMMEDIATE | BATCH] [WAIT | NOWAIT] ]

The below command will commit your transaction without forcing a disk I/O, and will return control to you without waiting for the commit operation to complete.

COMMIT WRITE BATCH NOWAIT ;

By default, if no WRITE clause is specified, a naked COMMIT is equivalent to

COMMIT WRITE IMMEDIATE WAIT;

COMMIT_WRITE parameter:

You can change the default behavior of COMMIT at the instance level, so that the default behavior of a naked COMMIT statement is not COMMIT WRITE IMMEDIATE WAIT. A new initialization parameter, COMMIT_WRITE, lets you set default commit propinquity to IMMEDIATE or BATCH, and default commit wait to WAIT or NOWAIT.

COMMIT_WRITE='{ IMMEDIATE | BATCH } , { WAIT | NOWAIT }'

Benefits of Asynchronous COMMIT:

• A normal commit forces a disk I/O and does not return until the commit is complete. However you can save time if you batch your commits and don't wait until the commits actually complete.
• If your application is continually issuing small commits, you should look at your code to see if this design is really necessary, or if you might be able to batch your commits in the code.
• If your commits are taking a long time because of complex indexes and triggers then you can save entered data to an intermediate, raw-data table and batching writes from that table to the schema tables, rather than writing directly to the schema tables while the user waits on the commit.

Pitfalls of Asynchronous COMMIT:

• An asynchronous commit returns before the data has actually been written to disk, so if the database crashes before some buffered redo is written to the logs, or a file I/O problem prevents the log writer from writing buffered redo to a non-multiplexed log, then the commit is lost.
• The COMMIT_WRITE initialization parameter is documented as being able to hold different values for different instances in a RAC. However if COMMIT_WRITE has different values for different instances in your cluster then load balancing or failover of your services could have unexpected and disastrous results.
• There is no initialization parameter to let the DBA disable asynchronous commit.
• There is no way to keep any given database user from executing an asynchronous commit. This could be a serious data reliability issue for systems that have knowledgeable ad-hoc users modifying data directly. Any user can issue an asynchronous commit for their work, which means that work could potentially disappear in case of system failure.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   A Guide for Individual Objects Tuning for Databases
   Tricky Password Features in Oracle 11g!!
   Go beyond running exp and imp - Follow Database Migration Tips!!
   Performance Gains by using Oracle Regular Expressions