Asynchronous commit (New Feature in Oracle 10g)

When a transaction updates the database, it generates a redo entry corresponding to this update. Oracle Database buffers this redo in memory until the completion of the transaction. When the transaction commits, the log writer (LGWR) process writes redo for the commit, along with the accumulated redo of all changes in the transaction, to disk. By default Oracle Database writes the redo to disk before the call returns to the client. This behavior introduces latency in the commit because the application must wait for the redo to be persisted on disk.
The full syntax of the new WRITE clause is:

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


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


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


In Above syntax NOWAIT and BATCH options are for Asynchronous commit, means it will not write data from redo logs to log file when commit is fired. By Using this option I/O speed of the operation will be removed by sacrificing Durability of ACID behavior of Oracle Database.


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.

By using above options, we can allow LGWR to consider itself
       - when to write to disk and
       - when control is returned to the client.
By specifying WAIT or NOWAIT you can influence when LGWR returns control to the client.

By specifying IMMEDIATE or BATCH you can influence when LGWR will write to disk.


COMMIT WRITE IMMEDIATE WAIT:

• The database by default will write commit records to disk for every commit and control is returned to the client after the writing is completed.
• It is default option for COMMIT.

COMMIT WRITE IMMEDIATE NOWAIT:

• At this time database commit behavior will be changed to IMMEDIATE NOWAIT.
• At this time LGWR will write to the redo logs directly after every commit. It will not wait until the writing is completed before it sends the next writing process to the redo logs
• The client will have control before the writing is completed.

COMMIT WRITE BATCH [WAIT | NOWAIT] :

• Oracle buffers the redo information.
• The log writer will write this redo information to disk, but in its own time. Thus, several I/Os can be "batched". It will store database changes as they occur and store in pre allocated files.

I am taking Example over here.
SQL> DECLARE
 2    SD DATE;
 3  BEGIN
 4   FOR I IN 1..150000 LOOP
 5    UPDATE EMP SET counter = counter + 1 WHERE EMPno=7499;
 6    commit;
 7   END LOOP;
 8  END;
 9  / 
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:15.02
SQL> DECLARE
 2    SD DATE;
 3  BEGIN
 4   FOR I IN 1..150000 LOOP
 5    UPDATE EMP SET counter = counter + 1 WHERE EMPno=7499;
 6    commit write batch nowait;
 7   END LOOP;
 8  END;
 9  / 
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:14.09
SQL> DECLARE
 2    SD DATE;
 3  BEGIN
 4   FOR I IN 1..150000 LOOP
 5    UPDATE EMP SET counter = counter + 1 WHERE EMPno=7499;
 6    commit write immediate wait;
 7   END LOOP;
 8  END;
 9  / 
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:02:20.06