Commit_write in 11g – the impact
Posted by John Hallas on April 3, 2008
Oracle has 4 permutations for the commit statement by using BATCH|IMMEDIATE and WAIT|NOWAIT
• IMMEDIATE – The commit “prods” the LGWR process by sending a message, so that the redo is written immediately to the redo logs.
• BATCH – The writes to the redo logs are buffered.
• WAIT – The commit command is synchronous. It doesn’t return until the relevant redo information is written to the online redo log.
• NOWAIT – The commit command is asynchronous. It can return before the relevant redo information is written to the online redo log.
The 11G default commit command uses IMMEDIATE,WAIT
Note that the specification of the NOWAIT and BATCH options allows a small window of vulnerability in which the Oracle database can roll back a transaction that the application views as committed. The application must be able to tolerate the following scenarios:
1) The database host crashes, which causes the database to lose redo that was buffered but not yet written to the online redo logs.
2) A file I/O problem prevents log writer from writing buffered redo to disk. If the redo logs are not multiplexed, then the commit is lost.
The test I have put together below shows the impact of the various permutations. It is not very subjective and we will shortly be running similar tests on the real code at high volume which will provide more variance and observability of the impact.
PL/SQL code loops 500,000 times, inserting data into 2 tables and updating another table then commits within the loop
ALTER SESSION SET COMMIT_WRITE=’WAIT’; 52 minutes then killed (only 150K written)
ALTER SESSION SET COMMIT_WRITE=’NOWAIT’; 179 s
ALTER SESSION SET COMMIT_WRITE=’IMMEDIATE’; 168 s
ALTER SESSION SET COMMIT_WRITE=’BATCH’; 173 s
ALTER SESSION SET COMMIT_WRITE=’BATCH,WAIT’; 10 minutes then killed (only 21K written)
ALTER SESSION SET COMMIT_WRITE=’BATCH,NOWAIT’; 196 s
ALTER SESSION SET COMMIT_WRITE=’IMMEDIATE,WAIT’; 25 minutes then killed (only 75K written)
ALTER SESSION SET COMMIT_WRITE=’IMMEDIATE,NOWAIT’; 165 s
It does beg the question of why use WAIT unless data security is of far more importance than performance