Oracle COMMIT options from 11gR2

COMMIT_LOGGING and COMMIT_WAIT

Since Oracle 11gR2 new options to COMMIT were added:

===================

COMMIT_LOGGING

===================
COMMIT_LOGGING specifies whether the log writer writes redo data in batches or at commit.

Options are IMMEDIATE and BATCH

IMMEDIATE is the default behavior.

COMMIT_LOGGING=IMMEDIATE;
The IMMEDIATE parameter causes the log writer process (LGWR) to write the transaction's redo information to the log. 

This operation option forces a disk I/O and Oracle does the log writer operation for each commit. 

COMMIT_LOGGING=BATCH;
The BATCH parameter causes the redo to be buffered to the redo log, along with other concurrently executing transactions. 
When sufficient redo information is collected, a disk write of the redo log is initiated. 
This behavior is called "group commit", as redo for multiple transactions is written to the log in a single I/O operation.

This causes less traffic, but indices risk because an instance crash may cause loss of data that is being batched inside the log buffer.

===================
COMMIT_WAIT

===================
Options are WAIT and NOWAIT
WAIT is the default behavior.

This clause specify when control returns to the user.


COMMIT_WAIT=WAIT
The WAIT parameter ensures that the commit will return only after the corresponding redo is persistent in the online redo log. 
Whether in BATCH or IMMEDIATE mode, when the client receives a successful return from this COMMIT statement, the transaction has been committed to durable media. 

COMMIT_WAIT=NOWAIT
The NOWAIT parameter causes the commit to return to the client whether or not the write to the redo log has completed. 
This behavior can increase transaction throughput.
With NOWAIT, a crash occurring after the commit message is received, but before the redo log record(s) are written, 
Can falsely indicate to a transaction that its changes are persistent.

===================

Syntax

===================

ALTER SESSION | SYSTEM SET COMMIT_WAIT=NOWAIT;
ALTER SESSION | SYSTEM SET COMMIT_WAIT=WAIT;

ALTER SESSION | SYSTEM SET COMMIT_LOGGING=BATCH;
ALTER SESSION | SYSTEM SET COMMIT_LOGGING=IMMEDIETE;

===================

Example

===================

SQL> show parameter COMMIT_WAIT

NAME                    TYPE                  VALUE

----------------------- --------------------- -----------------------

commit_wait             string

SQL> ALTER SYSTEM SET COMMIT_WAIT=NOWAIT;

System altered.

SQL> show parameter COMMIT_WAIT

NAME                    TYPE                  VALUE

----------------------- --------------------- -----------------------

commit_wait             string                NOWAIT

================

Golden Gate

================

In Golden Gate Replicat:

REPLICAT SOME_REPLICAT

USERID xxxxxx, PASSWORD xxxxxx

INSERTALLRECORDS

DBOPTIONS INTEGRATEDPARAMS(parallelism 4)

SQLEXEC "alter session set commit_wait = 'NOWAIT'";

MAP....