Oracle的非同步commit設定

In Oracle 10gR2, you can now choose to break this fourth property. Oracle's new asynchronous commit feature essentially allows you to trade durability for speed on a transaction-by-transaction basis. You can choose to break ACID compliance in this way by default, at the instance level; or you can choose to break it at the individual transaction level.

By default, Oracle's commits are durable. Oracle writes your changes to disk and doesn't return control of the session to you until it's done. The normal commit process is as follows:

1. User begins transaction.
2. While user is issuing DML, Oracle generates redo entries corresponding to the data changes. These redo entries are buffered in memory while the transaction is occurring.
3. When the user issues a COMMIT, Oracle immediately writes this buffered redo to disk, along with redo for the commit. Ie, a disk I/O is forced.
4. Oracle does not return from the commit until the redo has been completely written to disk (to the online redo log).

There are two important aspects to note here: the redo information is written to disk immediately, and the session waits for the process to complete before returning.

Oracle now lets you change both of these aspects. You can let the log writer write the redo information to disk in its own time, instead of immediately; and you can have the commit return to you before it's completed, instead of waiting.

Default COMMIT behavior		New option
IMMEDIATE	The redo information is written to disk immediately (forcing a disk I/O for each commit).	BATCH	Oracle buffers the redo information. The log writer will write this redo information to disk, but in its own time. Thus, several I/Os can be "batched".
WAIT	Oracle does not return from the commit until the commit has completed successfully.	NOWAIT	Oracle doesn't wait for the commit to complete, but returns right away.

You can change Oracle's commit behavior at the statement level by simply specifying one or both of the new options above with your commit. Use the new WRITE clause of the commit statement for this purpose. For example:

COMMIT WRITE BATCH NOWAIT ;

will commit your transaction without forcing a disk I/O, and will return control to you without waiting for the commit operation to complete.

The full syntax of the new WRITE clause is:

COMMIT [WRITE [IMMEDIATE | BATCH] [WAIT | NOWAIT] ]

By default, if no WRITE clause is specified, a naked COMMIT is equivalent to

COMMIT WRITE IMMEDIATE WAIT;

You can even change the default behaviour of COMMIT at the instance level, so that the default behaviour of a naked COMMIT statement is not COMMIT WRITE IMMEDIATE WAIT. A new initialization parameter, COMMIT_WRITE, lets you set default commit immediacy to IMMEDIATE or BATCH, and default commit wait to WAIT or NOWAIT:

COMMIT_WRITE='{ IMMEDIATE | BATCH } , { WAIT | NOWAIT }'

修改引數的命令：
alter system set commit_write=batch,nowait scope=both;

在oracle 11g中該引數被標誌為遺棄，由兩個引數取代：

commit_logging和commit_wait

可以使用

alter system set commit_logging=batch scope=both;
alter system set commit_wait=nowait scope=both;

得到同樣的效果

[@more@]

oracle 非同步 commit commit_write commit_logging commit_wait