关于修改 commit_write 提升性能的一点理解

10g R2：commit_write

OptionEffectWait(default)

Ensures that the commit returns only after the corresponding redo information is persistent in the online redo log. When the client receives a successful return from this COMMIT statement, the transaction has been committed to durable media.

A failure that occurs after a successful write to the log might prevent the success message from returning to the client, in which case the client cannot tell whether or not the transaction committed.

Nowait

The commit returns to the client whether or not the write to the redo log has completed. This behavior can increase transaction throughput.

Batch

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

Immediate(default)LGWR writes the transaction's redo information to the log. Because this operation option forces a disk I/O, it can reduce transaction throughput.

以上来自于Online Document,如果你不关心ACID的D(持久性),也就是不关心instance crash后丢失数据的风险,完全可以采用nowait,但我目前没有见过系统使用该参数,都为默认值.

当 改变原来 post wait or polling 机制后，设置commit_write=Batch，Nowait

Post/wait, traditional method for posting completion of writes to redo log

LGWR explicitly posts all processes waiting for the commit to complete.
The advantage of the post/wait method is that sessions should find out almost immediately when the redo has been flushed to disk.

Polling, a new method where the foreground process checks if the LGWR has completed the write.

redo log io 类型由原来的大量小IO ，变成了少量大IO 。log file sync 等待时间会明显减少，一方面是减小了对IO 子系统的压力；另一方面也是oracle 内在 维护 redolog 写 方面的latch 成本大大减少。对CPU 的开销也有一定的减少，从而也对oracle 的 每一次syscall 提升了性能。