What commit_write=wait,batch means to my transactions?
723096Sep 17 2009 — edited Mar 23 2011We upgraded our database to 10.2.0.4 a month ago. Since then we were having issue with the database performance. In AWR report, we see log file sync event always in the Top 5 Timed Events. Someone suggested to set commit_write=batch.
According to REFERENCE section of oracle documentation: for commit_wirte "If only IMMEDIATE or BATCH is specified, but not WAIT or NOWAIT, then WAIT mode is assumed." Also, in oracle documentation: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_sqlproc.htm#sthref185
WAIT: The commit does not return as successful until the redo corresponding to the commit is persisted in the online redo logs (default).
BATCH: Oracle Database should buffer the redo. The log writer process is permitted to write the redo to disk in its own time.
So what will happen to a transaction that ended with commit, after I set commit_wirte=batch? Will it wait for LGWR to flush the redo from log buffer to online redo log file as defined by "WAIT", or it will not wait for LGWR and let LGWR write the redo at later time in batch (or "its own time", as defined by "BATCH")?
Any comment is appreciated.