Asynchronous Commit :commit_write

Apps team feedback that few heavy jobs run much slower after upgrade to 10g.

After two hours investigation, I notice there is lots of wait event of "log file sync" , definitely the root cause is too many COMMIT. However, this still can explain the behavior in 9i.

There must be something different.

After compare all parameters , I notice that there is no commit_write in 9i , while in 10g the value is set 'BATCH,WAIT'.

That must be the thing I want.

check metalink, oracle docs ...

starting testing ...

found the significant difference. With NOWAIT or not set commit_wait at all, no more wait event "log file sync" observed. The testing SQL can finish within 1.5mins versus problematic 1 hours.
Wow, what a nice day. There should be many happy face tomorrow ...

Table 2-1 Initialization Parameter and COMMIT Options for Managing Commit Redo
Option Specifies that . . .
WAIT The commit does not return as successful until the redo corresponding to the
commit is persisted in the on line redo logs (default).
NOWAIT The commit should return to the application without waiting for the redo to be written
to the on line redo logs.
IMMEDIATEThe log writer process should write the redo for the commit immediately (default). In
other words, this option forces a disk I/O.
BATCH Oracle Database should buffer the redo. The log writer process is permitted to write
the redo to disk in its own time.