Re: Oracle Auto Commit during transaction.

On Mar 15, 10:00 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Mar 14, 3:23 pm, PowerOne <salazar.wi..._at_gmail.com> wrote:
>
> > Hi,
>
> > If I send an Update like this:
>
> > UPDATE    MyTable    SET    Field1 = value
> > WHERE Field2 = value2;
>
> > there is a way that Oracle make a commit every  X records updated?
>
> > Note: this update can alter thousand of records at the same time.
>
> > Thank you.
>
> No, and you don't want to do that.  It's a sure technique to generate
> ORA-01555 errors and, thus, kill your overall update.  Read here:
>
> http://oratips-ddf.blogspot.com/2008/02/dreaded-ora-01555.html
>
> then stop thinking about this.
>
> David Fitzjarrell

The primary determining factor for if a large update can be performed as a single operation providing enough undo space to support the transaction exists is if there is concurrent update demand for the rows. If you have online applications that will in fact update rows that are updated by the long running transaction then having to commit within the loop is a must. You cannot expect an online transaction to wait 30 seconds for a row to be committed. It is simply too long of a delay. Under traditional rollback segment management as long as you had sufficiently large rbs segments so as not to wrap around too fast the 1555 was unlikely. With an undo tablespace the error is even less likely. This providing you use some intelligence in choosing the commit size.

However, when ever possible a single DML statement should normally be done as a single transaction. Though you need to watch the consistent read statistics on any task that revisits table (or index) blocks because sometimes by placing a few commits in the task you can greatly cut down on the number of read consistent block views that have to be built resulting in significant run time improvement. This is an often overlooked issue since one of the primary reasons to use a single transaction to perform a DML operation is the performance benefit of doing so. Sometimes there is actually a performance penalty for using a single transaction.

Like everything else in Oracle there are trade offs involved in choosing the size of the unit of work to commit, that is, in choosing the transaction size. You have to balance available undo space demand, concurrent access requirements, task restart ability, and evaluate the performance implications of both approaches.

HTH -- Mark D Powell -- Received on Sat Mar 15 2008 - 11:28:24 CDT