Re: Asynchronous Commit in Oracle Database 10g R2

"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message news:1125456312.637905.86520_at_g43g2000cwa.googlegroups.com...
> yong321_at_yahoo.com wrote:
>> I don't get it either!, because I'm not sure about the exact condition.
>> But it looks like using an implicit vs. explicit cursor influences the
>> number of log file sync waits. My test case is at
>> http://rootshell.be/~yong321/oranotes/CursorImplicitExplicitCommit.txt
>
> Thanks, will run it on my systems as well to see what gives.
> The thing that worries me a bit is we may be classing this as
> an undocumented optimization when it might well be something
> else! The lack of any pattern to how it increments seems to
> point to an "undocumented feature" anyway.
> Not that it hasn't ever happened...
>

Sorry to delay posting on this - I've been away from home for a while, and only been able to look at Google for news from time to time.

This is an undocumented optimization.
You need only look at the sessions 'redo synch writes'.

This counts the calls made to the log writer to flush the log buffer and wait. It's always one - which is the one call at the end of the database call that contains the series of commits.

The randomness of the redo writes (which probably matches the messages sent and
received) is likely to be a random timing thing - apparently a session CAN send an 'unsynched' message to the log writer - and various things might cause this to happen.

If you want to see the session forced to send a 'redo synch write' on every commit in the loop, then make the DML reference the table through a loopback link - a distributed transaction has to commit 'properly'.

-- 
Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005