Asynchronous COMMIT

While user is issuing DML transaction the redo entrie for this transaction Is store to the buffer in to SGA. And after commit issue redo data written to the redo log file which physical located on hard disk. This is normal practice and it is called synchronous Commit. Oracle does not return control until the transaction physically written.

But this practice not very much acceptable for high speed Transition. this point of view oracle introduce Asynchronous commit in 10g release 2.

Now Asynchronous COMMIT:

Oracle has introduce two options:

a. One is to return immediately after the COMMIT is issued, rather than waiting for the log activity to complete.

b. Another option batches multiple transactions together in memory before writing to the disk.
The full syntax of the new WRITE clause is:

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

By default, if no WRITE clause is specified, a normal COMMIT is equivalent to and following syntax is default.

COMMIT WRITE IMMEDIATE WAIT;

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

COMMIT WRITE BATCH NOWAIT;

ALTER SYSTEM SET commit_write = BATCH, NOWAIT;