Re: Important note about asynchronous commit

I wouldn't know how to write a very reliable application that guarantees that it IS ALWAYS able to replay transactions in case of Instance failure. === oh yes ! There is one such : The Oracle RDBMS Engine itself. To date (except for one particular buggy 6.0.28 (or was it 6.0.29 ?) release), I have seen Oracle Instances CRASH AND RECOVER consistently.

Play around with the guaranteed mechanism and you are playing dangerous with your data.

See http://orafaq.com/node/93
and a quote there
Submitted by Zlatko Sirotic (not verified) on Fri, 2005-10-21 08:18.

To quote C.J.Date (Database In Depth, O'Reilly 2005, page 130-131):

"The emphasis in the commercial world always seems to be on performance, performance, performance; other objectives, such as ease of use, data independence, and in particular integrity, seem so often to be sacrificed to – or at best to take a back seat to – that overriding goal. But what's the point of a system performing well if we can't be sure the information we're getting from it is correct? Frankly, I don't care how fast a system runs if I don't feel I can trust it to give me the right answers to my questions.".

Regards,

Zlatko Sirotic

 From some recent postings we have seen on this list, some people are really trying (or "playing around with" ?) BATCH,NOWAIT.

I wouldn't risk my database, even if I was running a benchmark (eg when testing different hardware platforms).

I do know that, *in theory* in a batch environment you __should__ be able to identify and replay transactions. I haven't seen a "batch only, no other
transactions" environment -- even the administrator just connecting via sqlplus to update some setup / master table would be running a transaction that may not be recoverable if the instance were to crash. Tom Kyte's example at
http://www.oracle.com/technology/oramag/oracle/05-sep/o55asktom.html is about
"What about a system that is processing incoming records as fast as possible, perhaps from a sensor or a network feed? This program's goal in life is to batch up a couple records, INSERT them, COMMIT them (to make them visible), and continue on. It doesn't really care to wait for the COMMIT to complete; in fact, by waiting, this program is not running as fast as it can. " and
"
But there are many classes of
applications­including high-speed data ingest programs whose only goal is to get the stream of data into the database­where
commit-but-don't-wait is not only acceptable but very desirable performancewise. "

I would emphasis "only goal is to get the stream of data into the database" again.
Contrast that with the quote from C J Date, above.

Hemant

At 09:12 PM Tuesday, Juan Carlos Reyes Pacheco wrote:
>Hi
>I want to share this because I didn't fully understood it,
>
>Tom Kyte clarifyme the risk of using asynchronous commit
><http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:60447282988010>http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:60447282988010
>
>
>asynchronous commits should never be used in oltp.
>
>
>async commits are most DEFINITELY not for
>transaction processing databases, they
>basically mean "when you commit and you return from commit, the commit may or
>may NOT have happened yet, if the system fails
>after you return from commit but
>
>before the commit actually happens - you have to be prepared to REPLAY that
>transaction if necessary - something you can do
>in a batch load easily, but not
>so in a transactional system"
>
>

Hemant K Chitale
http://web.singnet.com.sg/~hkchital

--
http://www.freelists.org/webpage/oracle-l