Managing Commit Redo Action

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 process (LGWR) 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 action introduces a latency in the commit because the application must wait for the redo to be persisted on disk.

Suppose that you are writing an application that requires very high transaction throughput. If you are willing to trade commit durability for lower commit latency, then you can change the default COMMIT options so that the application need not wait for the database to write data to the online redo logs.

Oracle Database enables you to change the handling of commit redo depending on the needs of your application. You can change the commit action in the following locations:

• COMMIT_WRITE initialization parameter at the system or session level
• COMMIT statement

The options in the COMMIT statement override the current settings in the initialization parameter. Table 2-1 describes redo persistence options that you can set in either location.

Caution:

With the NOWAIT option of COMMIT or COMMIT_WRITE, a failure that occurs 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.

Table 2-1 Options of COMMIT Statement and COMMIT_WRITE Initialization Parameter

The following example shows how to set the commit action to BATCH and NOWAIT in the initialization parameter file:

COMMIT_WRITE = BATCH, NOWAIT

You can change the commit action at the system level by executing ALTER SYSTEM as in the following example:

ALTER SYSTEM SET COMMIT_WRITE = BATCH, NOWAIT

After the initialization parameter is set, a COMMIT statement with no options conforms to the options specified in the parameter. Alternatively, you can override the current initialization parameter setting by specifying options directly on the COMMIT statement as in the following example:

COMMIT WRITE BATCH NOWAIT

In either case, your application specifies that log writer does not have to write the redo for the commit immediately to the online redo logs and need not wait for confirmation that the redo was written to disk.

Note:

You cannot change the default IMMEDIATE and WAIT action for distributed transactions.

If your application uses OCI, then you can modify redo action by setting the following flags in the OCITransCommit function within your application:

• OCI_TRANS_WRITEBATCH
• OCI_TRANS_WRITENOWAIT
• OCI_TRANS_WRITEIMMED
• OCI_TRANS_WRITEWAIT

Caution:

There is a potential for silent transaction loss when you use OCI_TRANS_WRITENOWAIT. Transaction loss occurs silently with shutdown termination, startup force, and any instance or node failure. On a RAC system asynchronously committed changes might not be immediately available to read on other instances.

The specification of the NOWAIT and BATCH options has a small window of vulnerability in which Oracle Database can roll back a transaction that your application view as committed. Your application must be able to tolerate the following scenarios:

• The database host fails, which causes the database to lose redo that was buffered but not yet written to the online redo logs.
• A file I/O problem prevents log writer from writing buffered redo to disk. If the redo logs are not multiplexed, then the commit is lost.

Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4

COMMIT_WRITE is an advanced parameter used to control how redo for transaction commits is written to the redo logs. The IMMEDIATE and BATCH options control how redo is batched by Log Writer. The WAIT and NOWAIT options control when the redo for a commit is flushed to the redo logs.

COMMIT_WAIT is an advanced parameter used to control when the redo for a commit is flushed to the redo logs.

Be aware that the NOWAIT option can cause a failure that occurs after the database receives the commit message, but before the redo log records are written. This can falsely indicate to a transaction that its changes are persistent. Also, it can violate the durability of ACID (Atomicity, Consistency, Isolation, Durability) transactions if the database shuts down unexpectedly.

If the parameter is set to FORCE_WAIT, the default behavior (immediate flushing of the redo log buffer with wait) is used. If this is a system setting, the session level and transaction level (COMMIT_WRITE) options will be ignored. If this is a session level setting, the transaction level options will be ignored. If COMMIT_WAIT is altered after it has been set to FORCE_WAIT, then the FORCE_WAIT option is no longer valid.

COMMIT_LOGGING is an advanced parameter used to control how redo is batched by Log Writer.

If COMMIT_LOGGING is altered after setting COMMIT_WAIT to FORCE_WAIT, then the FORCE_WAIT option is no longer valid.

commit_write引數只在Oracle 10gR2中有效，在oracle 11g中被commit_logging和commit_wait取代。