10g R2:commit_write
Option | Effect |
Wait(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 提升了性能。