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

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.”

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.

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.