Re: Asynchronous Commit in Oracle Database 10g R2

<snip>
> you will only have one call to LGWR, not 1000. That was revealed to me
> by Jonathan Lewis and I made my point to investigate that. I traced the
> log writer by strace and found Jonathan's statement to be true. All that
> COMMIT WRITE NOWAIT does is to legitimize already existing situation. As a
> matter of fact, what Oracle9i does in the loop like this is covered by
> COMMIT WRITE BATCH. It simply writes a commit record to the log buffer and
> continues.

</snip>

Is it really true and oracle never told us about this ? Could you please share your 9i test case with me or point me to some URL?

--KDB
http://itzkdb.blogspot.com

Mladen Gogala wrote:
> On Fri, 26 Aug 2005 12:42:25 -0700, orauser wrote:
>
> >
> > Just read this article about "Asynchronous Commit in Oracle Database
> > 10g R2" on
> > http://itzkdb.blogspot.com/2005/08/asynchronous-commit-in-oracle-database.html
> > .
>
>
> Actually, this only one of the optimizations from Oracle9i made legitimate.
> In Oracle9i, if you have a loop like this:
>
> declare
> i number;
> begin
> for i in 1000 loop
> insert into a values(i);
> commit;
> end loop;
> end;
> /
>
> you will only have one call to LGWR, not 1000. That was revealed to me
> by Jonathan Lewis and I made my point to investigate that. I traced the
> log writer by strace and found Jonathan's statement to be true. All that
> COMMIT WRITE NOWAIT does is to legitimize already existing situation. As a
> matter of fact, what Oracle9i does in the loop like this is covered by
> COMMIT WRITE BATCH. It simply writes a commit record to the log buffer and
> continues.
>
> Below are the new options to the commit statement, from Oracle 10R2 SQL
> ref. manual:
>
> BATCH The BATCH parameter causes the redo to be buffered to the redo log. No I/O is initiated.
>
> WAIT The WAIT parameter ensures that the commit will not return until the corresponding redo is persistent in the online redo log. If you specify neither WAIT nor NOWAIT, then WAIT is the default.
>
> NOWAIT The NOWAIT parameter allows the commit to return before the redo is
> persistent in the redo log.
>
> So, all you have here is an already existing optimization formalized and
> made official. No reasons to get excited.
> --
> http://www.mgogala.com
Received on Sun Aug 28 2005 - 01:13:57 CDT