Thursday, April 20, 2006

Asynchronous Commit - a New Feature in ORACLE 10gR2

"In the old times" the LGWR had to write to the redo-logs with every COMMIT.

Starting with ORACLE 10gR2 we can use the initialization parameter
COMMIT_WRITE to change the commit behaviour
on SYSTEM- as well as on SESSION-LEVEL.

We can allow LGWR to consider itself
- when to write to disk and
- when control is returned to the client.

By specifying WAIT or NOWAIT you can influence when LGWR returns control to the client.
By specifiying IMMEDIATE or BATCH you can influence when LGWR will write to disk.


If you do not explicitly specify this advanced parameter:
- - the default is IMMEDIATE, WAIT
- the database by default will write commit records to disk for every commit
- control is returned to the client after the writing is completed.
- the client receives the massage COMMIT COMPLETE.

If you set COMMIT_WRITE to NOWAIT and you do not specify anything else:
- the default is IMMEDIATE, NOWAIT
- LGWR will write to the redologs directely after every commit
- it will not wait until the writing is completed before it sends the next writing process to the redologs
- the client will have control before the writing is completed.

If you set COMMIT_WRITE to BATCH and you do not specify anything else:
- the default is BATCH, WAIT
- LGWR will consider itself when to write to disk from the LOG_BUFFER
- LGWR will not write with every COMMIT
- the client must wait until the I/O of LGWR is completed

Changing this parameter has its drawbacks and advantages and it is you who has to descide which death to dye:
- the majour drawback is obviousely that you cannot be sure anymore that your changes are persistently saved even after commit which can mean that you might lose even committed data in the case of an instance crash.
- the advantage can be that concurrently committing sessions do not have to to wait for each other 's commit to complete => less wait events.
This parameter, again, is not a basic but an advanced parameter, which means that the default is o.k. in the first place and you would think about changing its value to whatever only after you encounter significant numbers of wait event with concurrent commits.
Because it is good to know that the committed changes are persistently changed and can be applied again in case of crash recovery.


You change the parameter by providing the values separated by a comma:
ALTER SYSTEM SET COMMIT_WRITE = BATCH, NOWAIT;

POSSIBLE COMBINATIONS:
- IMMEDIATE,NOWAIT
- IMMEDIATE,WAIT
- BATCH,NOWAIT
- BATCH,WAIT


5 Comments:

At Fri Apr 21, 12:13:00 PM, Anonymous Anonymous said...

Interesting, but exactly what's in the manual.

More interesting would be some analysis on the advantages and drawbacks of each option and when to use them.

 
At Fri Apr 21, 03:28:00 PM, Blogger lutz_hartmann said...

Hi anonymous (why anonymous?!),
you are right, it's alwayas good to learn what it is good for and what are the drawbacks.
So pls see the again , I have extended it so everybody can see it.

Thanks for you feedback! Welcome any time
=;-)

 
At Wed Aug 23, 11:50:00 AM, Anonymous Dallas said...

Am I correct in saying that the IMMEDIATE,NOWAIT option will virtually put an end to log file sync events. This has tremendous benefit for a busy OLTP system. However, I have seen a metalink note that suggests that your database could be un-recoverable in the event of an instance crash.

 
At Thu Sep 07, 01:40:00 PM, Blogger lutz_hartmann said...

Hi Dallas,
sorry for responding only with such a delay. Your comment had ended up in my spam folder by mistake.
Yes this is exactly what can happen:
you cannot be sure any more that your modifications have really been written to disk after a commit, which means that you can have more data loss in case of an instance crash! Now you can loose entire committed transactions if you do not use IMMEDIATE, WAIT! (which should be the default! test it with PL/SQL!!).
I would suggest to be very careful with this feature! This is something that can cause damage! And I also have the impression that there is still work in progress on this feature.

At least really test it thoroughly , especially in a RAC environment!
=;-)

 
At Thu Apr 24, 03:50:00 PM, Blogger chandra said...

Hey,

I moving data from 9i to 10g over database link, Commit_Write is set to blank in 10g (10.2) database.

when i execute commit from 9i.. my data is getting lost. any help ?

how to enable immediate wait after dblink level ? or any idea

 

Post a Comment

<< Home