Tuesday, January 20, 2009

Altering commit behaviour 10gR2 and up

In Oracle 10g Release 2 the COMMIT command has been enhanced with the WRITE clause to give a degree of control over the way redo information is written to the redo logs during the commit operation.
This can improve performance, but it should only be used for processes that meet the following criteria:
1) They result in large numbers of transactions that require redo log writes.
2) Data loss can be tolerated in the event of an instance crash during the process.
3) Waiting for redo log writes is a significant part of the waits associated with the process.

The available options for the COMMIT command and the WRITE clause are displayed below.
COMMIT;
COMMIT WRITE WAIT;
COMMIT WRITE NOWAIT;
COMMIT WRITE BATCH;
COMMIT WRITE IMMEDIATE;

The meanings of the WRITE clause values are listed below.
IMMEDIATE - The commit "prods" the LGWR process by sending a message, so that the redo is written imemdiately to the redo logs.
BATCH - The writes to the redo logs are buffered.
WAIT - The commit command is synchronous. It doesn't return until the relevant redo information is written to the online redo log.
NOWAIT - The commit command is asynchronous. It can return before the relevant redo information is written to the online redo log.

The action associated with the regular COMMIT command is defined by the COMMIT_WRITE parameter, which accepts a comma-separated list of values.
COMMIT_WRITE = '{IMMEDIATE BATCH},{WAIT NOWAIT}'

The COMMIT_WRITE parameter can be specified at instance or session level using the ALTER SYSTEM and ALTER SESSION commands respectively.

ALTER [SYSTEM SESSION] SET COMMIT_WRITE='WAIT';
ALTER [SYSTEM SESSION] SET COMMIT_WRITE='NOWAIT';
ALTER [SYSTEM SESSION] SET COMMIT_WRITE='IMMEDIATE';
ALTER [SYSTEM SESSION] SET COMMIT_WRITE='BATCH';
ALTER [SYSTEM SESSION] SET COMMIT_WRITE='BATCH,WAIT';
ALTER [SYSTEM SESSION] SET COMMIT_WRITE='BATCH,NOWAIT';
ALTER [SYSTEM SESSION] SET COMMIT_WRITE='IMMEDIATE,WAIT';
ALTER [SYSTEM SESSION] SET COMMIT_WRITE='IMMEDIATE,NOWAIT';

The default actions for the COMMIT_WRITE parameter and WRITE clause are the same, although at the time of writing the COMMIT_WRITE documentation incorrectly says they are not, so refer to the COMMIT documentations, which says:
"If you specify neither WAIT nor NOWAIT, then WAIT is the default. If you specify neither IMMEDIATE nor BATCH, then IMMEDIATE is the default."

No comments:

Post a Comment