Oracle DBA – A lifelong learning experience

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=’BATCH,WAIT’; 10 minutes then killed (only 21K written)
ALTER SESSION SET COMMIT_WRITE=’IMMEDIATE,WAIT’; 25 minutes then killed (only 75K written)

It does beg the question of why use WAIT unless data security is of far more importance than performance

6 Responses to “Commit_write in 11g – the impact”

  1. Polarski Bernard said

    I in the past for a markets rooms, sometime a single transaction worth a billion bucks. You won’t sleep well with commit_write=batch,nowait, this I promise you.

  2. John Hallas said

    I agree entirely Bernard.

    We are building a stored procedure that commits immediately on ‘important’ transactions but waits to commit on ‘unimportant’ ones (log messages etc). The commit immediate also commits all waiting commits as well and it does give a big performance benefit on high volume of transactions.
    There is a risk of losing some data, but not financial transactions themselves, only the logging of the messages around that transaction, which is manageable.

  3. I guess we agree that BATCH,NOWAIT is possible data loss. However me thinks that IMMEDIATE,NOWAIT can lose data as well. And this is the 11g default which is not ACI_D_?

  4. Arup Nanda said

    John – are you sure the default is “immediate, nowait”? In Oracle 10g R2 where it was introduced it used to be “immediate, wait”, which makes sense. It’s hard to think why they would change it in 11g.

  5. John Hallas said

    Good catch Arup, I have amended/corrected the text. However, I am sure that I (and someone else at work) found 11g documentation to support this. Looking around now I cannot find that evidence so I must have been wrong.

    I am on a different site now and I intend to redo those tests as they do seem a bit suspect to me, although I am certain they were correct at the time. Was OEL5 on HP AMD, now HP-UX on HP Itanium.

  6. Marica said

    Hi guys,

    since I upgraded from to on HP-UX iam facing some strange behaviuor:
    1. I get athe message in the alert.log
    COMMIT_WRITE is deprecated,use COMMIT_LOGGING and COMMIT_WAIT instead

    this parameter is not applied in my init.ora and I dont know how to get rid of this message.

    2. the following message bothers me much more:
    ORA-00060: Deadlock detected. More info in file …..

    Can someone help me?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: