SEO

Dear Gurus,

Just read this article about "Asynchronous Commit in Oracle Database
10g R2" on
http://itzkdb.blogspot.com/2005/08/a...-database.html
..

Can some of you comment whether all the information/warnings given in
this article are true?

Cheers,
orauser

On Fri, 26 Aug 2005 12:42:25 -0700, orauser interested us by writing:

> Dear Gurus,
>
> Just read this article about "Asynchronous Commit in Oracle Database
> 10g R2" on
> http://itzkdb.blogspot.com/2005/08/a...-database.html
> .
>
> Can some of you comment whether all the information/warnings given in
> this article are true?
>

Look at the Oracle Database 10g Release 2 SQL Reference manual, under
COMMIT and the reference to the App Developer's Guide and decide for
yourself.

<rant>
Funny thing is that Oracle provides caveats, but the typical developer
will not both to look at them. And then wonders why things don't go the
way expected. (Whose fault is that?)
</rant>

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***

HansF wrote:

> <rant>
> Funny thing is that Oracle provides caveats, but the typical developer
> will not both to look at them. And then wonders why things don't go the
> way expected. (Whose fault is that?)
> </rant>

<sarcasm>
Oracle's of course. When you buy a car doesn't the dealer also teach
you how to drive it and maintain it?
</sarcasm>
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

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/a...-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

orauser wrote:
> Dear Gurus,
>
> Just read this article about "Asynchronous Commit in Oracle Database
> 10g R2" on
> http://itzkdb.blogspot.com/2005/08/a...-database.html
> .
>
> Can some of you comment whether all the information/warnings given in
> this article are true?
>
> Cheers,
> orauser


>From the Oracle 10gR2 documentation for COMMIT, found at
http://download-east.oracle.com/docs...ments_4010.htm
:

"WRITE Clause

Use this clause to specify the priority with which the redo information
generated by the commit operation is written to the redo log. This
clause can improve performance by reducing latency, thus eliminating
the wait for an I/O to the redo log. Use this clause to improve
response time in environments with stringent response time requirements
where the following conditions apply:

* The volume of update transactions is large, requiring that the redo
log be written to disk frequently.

* The application can tolerate the loss of an asynchronously committed
transaction.

* The latency contributed by waiting for the redo log write to occur
contributes significantly to overall response time."

Note the second bulleted comment, regarding the toleration of
transaction loss due to asynchronous writes. This is not for
everybody, nor is it for everyday use in 99.9% or better of production
systems currently running. Committing a transaction in a manner which
may or may not get it to the redo logs is questionable and deserves
CAREFUL thought and consideration before implementation. This is very
much like operating an enterprise accepting only credit card sales and
doing so in a fashion whereby the credit card company may or may not
have a record of the transaction to keep your sales brisk, making
reimbursement to you impossible for some sales. Could YOU run a
business in that fashion and tolerate lost revenue? I would take great
caution with that scenario, and likely not accept it. So would I take
great care in considering asynchronous commits. The end result does
not appear to be desirable in my viewpoint.

To answer your question, yes, the caveats presented are real and
verifiable. Why do you find it so difficult to believe the truth? Or,
do you choose to believe the truth when it matches your concept, and
question it when it flies in the face of your limited knowledge? Read
through the documentation at tahiti.oracle.com, carefully. There is
much you need to learn.


David Fitzjarrell

On Sat, 27 Aug 2005 14:30:26 -0700, fitzjarrell interested us by writing:

> Committing a transaction in a manner which
> may or may not get it to the redo logs is questionable and deserves
> CAREFUL thought and consideration before implementation.

AND yet .... when loading a warehouse, or any place in which all
transactions are repeatable, this might be perfectly acceptable. If the
system crashed during a bulk load, wipe and restart might be better than
recovery.

As you state eelsewhere in your reply, it requires careful consideration
and an awareness of what is right for the application.

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***

HansF wrote:
> On Sat, 27 Aug 2005 14:30:26 -0700, fitzjarrell interested us by writing:
>
> > Committing a transaction in a manner which
> > may or may not get it to the redo logs is questionable and deserves
> > CAREFUL thought and consideration before implementation.
>
> AND yet .... when loading a warehouse, or any place in which all
> transactions are repeatable, this might be perfectly acceptable. If the
> system crashed during a bulk load, wipe and restart might be better than
> recovery.
>
> As you state eelsewhere in your reply, it requires careful consideration
> and an awareness of what is right for the application.
>
> --
> Hans Forbrich
> Canada-wide Oracle training and consulting
> mailto: Fuzzy.GreyBeard_at_gmail.com
> *** I no longer assist with top-posted newsgroup queries ***

I should have said 'production OLTP applications', as that was at the
forefront of my mind as I was typing my response. Certainly a
datawarehouse is a suitable choice for asynchronous commits; such could
decrease the load time and bring the warehouse to availability much
more rapidly than standard commits.

I still stand by my statements with regard to OLTP applications. I
apologise for not making that sentiment clear in my previous response.


David Fitzjarrell

On Sat, 27 Aug 2005 18:45:47 -0700, fitzjarrell interested us by writing:

>
> I still stand by my statements with regard to OLTP applications. I
> apologise for not making that sentiment clear in my previous response.

I thought that was the case - simply wanted to clarify *and support* your
comment.

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***

<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/a...-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

Somehow this reminds me of the introduction of loading data with NOLOGGING.

Some malicious developers then used NOLOGGING inappropriately.

And along came NO FORCE LOGGING.

So wait for NO ASYNC COMMIT to be introduced.


--
Marc Blum
mailto:blumXXXSPAMXXX@marcblum.de
http://www.marcblum.de