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:
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.
Normal07.8 pt02falsefalsefalseMicrosoftInternetExplorer4
Property | Description |
Parameter type | String |
Syntax | COMMIT_WRITE = '{IMMEDIATE | BATCH},{WAIT |NOWAIT}' |
Default value | If this parameter is not explicitly specified, then database commit behavior defaults to writing commit records to disk before control is returned to the client. If only IMMEDIATE or BATCH is specified, but not WAIT or NOWAIT, then WAIT mode is assumed. If only WAIT or NOWAIT is specified, but not IMMEDIATE or BATCH, then IMMEDIATE mode is assumed |
Modifiable | Yes (at both session-level and system-level). Values supplied for COMMIT_WRITE in an ALTER SYSTEM or ALTER SESSION statement must be separated by a comma. |
Range of values | Single-quoted, comma-separated list of either IMMEDIATE or BATCH, and either WAIT or NOWAIT. |
Basic | No |
Real Application Clusters | Each instance may have its own setting |
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.
Property | Description |
Parameter type | String |
Syntax |
|
Default value | There is no default value. |
Modifiable | Yes (at both session-level and system-level) |
Basic | No |
Oracle RAC | Each instance may have its own setting |
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.
Property | Description |
Parameter type | String |
Syntax |
|
Default value | There is no default value. |
Modifiable | Yes (at both session-level and system-level) |
Basic | No |
Oracle RAC | Each instance may have its own setting |
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取代。
[@more@]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26651/viewspace-1040530/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26651/viewspace-1040530/