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;

oracle scn

SCN can refer to:

System Change Number - A number, internal to Oracle that is incremented over time as change vectors
are generated, applied, and written to the Redo log.

System Commit Number - A number, internal to Oracle that is incremented with each database COMMIT.


Note: System Commit Numbers and System Change Numbers share the same internal sequence generator.

The system change number (SCN) is an ever-increasing value that uniquely identifies
a committed version of the database. Every time a user commits a transaction,
Oracle records a new SCN. You can obtain SCNs in a number of ways,
from the alert log. You can then use the SCN as an identifier for purposes of
recovery. Oracle uses SCNs in control files, datafile headers, and redo records. Every redo log
file has both a log sequence number and low and high SCN. The low SCN records
the lowest SCN recorded in the log file, while the high SCN records the highest SCN
in the log file.

It is an unique incremental number in the database (as your clock time).

The SCN number is incremented every 3 seconds. This number is very useful while recovering the database or instance.
All the datafile headers will have the same scn number when the instance is shutdown normally.
You can get the current scn number from dbms_flashback.get_system_change_number or if you are using 10g your can query v$database