Re: commit or not commit inside a cursor loop

Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.193ee24f64d8d07498979a_at_news.la.sbcglobal.net>...
> carsten_at_saager.org said...
> >
> > "Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> schrieb im Newsbeitrag
> > news:tms9dvop5bpq1hfaasom3ims26br486vt1_at_4ax.com...
> > > You should NOT commit inside a loop.
> > > a COMMIT ends a transaction, and releases all locks.
> > > Your read consistent view will be released, so the data will be
> > > possibly overwritten, resulting in ORA-1555 in your transactions.
> > > Just search the google archives and you will find hundreds of posts
> > > explaining why you shouldn't do it. Just search on ora-1555 or read
> > > the paper on Metalink with respect to ora-1555
> >
> > There isn't any choice if you cannot enlarge your RBS/UNDO tablespaces for
> > this transaction. The main problem is that Oracle has to maintain the
> > read-consistency cursor so you might a receive (randomly) a 1555 anyway if
> > other sessions are making changes to the data your cursor relies on. The
> > problem here is RBS-contention. We got around this problem by running our
> > program (VERY LARGE PL/SQL-program) in restricted mode. After migrating to
> > Oracle 9i (and switching to UNDO-Management) the problem was finally solved.
> >
> > BTW: Our main loop performs a COMMIT/ROLLBACK for each record, because every
> > action is regarded as a transaction that might succeed or fail. What I want
> > to say is: If the transactions are _enclosed_ in the loop it is OK to
> > perform COMMITs to save disk-space. Most batch-jobs have such a loop where
> > single contracts/etc. are transformed in the loop-body.
> >
> > If performance comes into play: Do not COMMIT too often, since a COMMIT
> > forces some synchronization in the engine so that there is a system specific
> > limit for the tps.
> >
> > Carsten
> >
> It does seem counter-intuitive, doesn't it, to only commit when you
> ABSOLUTELY need to? Thinking logically, I'd assume it's a good thing to
> commit every so often. However, it turns out that's a myth. Oracle's
> whole architecture is setup to take advantage of infrequently issuing a
> COMMIT. There are almost no reasons for doing it inside a loop (I'll
> stop short of saying "never").
>
> For a lengthy discussion of the why's and wherefore's, see Tom Kyte's
> book Oracle Expert One-On-One or visit his web site:
>
> http://asktom.oracle.com
>
> Tom has many, many examples that prove a commit takes almost no time at
> all to perform, since the "synchronization" you mention takes place in
> the background every few seconds. I didn't believe it either until I saw
> the proof.

OP did not mention two important things... 1) Is this a one time event? and 2) What time of day is he planning to run this sort of an update?

Judging from his post, I assume it's a one time affair. If so, then next important thing is what time he wants to run it.

If he wants to run it in the middle of the day when everyone and their kitchen sink is logged in, then he should also worry about other potential issues beside "Snapshot too old" errors.

IMO, a better approach would be to run it at an off-peak time during a maintenance window when no users are logged in. If rollback segments are adequately setup, this can pretty much eliminate the possibility of ORA-01555.

Regards
/Rauf Sarwar Received on Thu May 29 2003 - 01:53:50 CDT