Re: Asynchronous Commit in Oracle Database 10g R2

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