Asynchronous Commit for Heavy Transaction

Note that in 10.2, the version that introduced these features, there is a single parameter (COMMIT_WRITE) to control them.
For example, it is possible to set it to “NOWAIT, BATCH”.

Oracle 11.2 onwards

The COMMIT_WRITE parameter is deprecated. It is retained for backward compatibility only. It is replaced by the COMMIT_LOGGING and COMMIT_WAIT parameters.

Introduced two new initialization parameters COMMIT_WAIT and COMMIT_LOGGING
For example, COMMIT_WAIT = NOWAIT and COMMIT_LOGGING = BATCH:

http://antognini.ch/2012/04/commit_wait-and-commit_logging/

“Note:
The default PL/SQL commit behavior for nondistributed transactions is BATCH NOWAIT
if the COMMIT_LOGGING and COMMIT_WAIT database initialization parameters have not been set.”

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#LNPLS592

In summary, COMMIT_WAIT and COMMIT_LOGGING are not commonly used parameters but, in some specific situations, using them it might be beneficial to avoid wait events related to commits.

Beware 🙂

It can violate the durability of ACID (Atomicity, Consistency, Isolation, Durability) transactions if the database shuts down unexpectedly.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s