Re: commit or not commit inside a cursor loop

Carsten Saager wrote:

> "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

There is no machine capable of running Oracle on which rbs/undo tablespaces can not be enlarge. Data files can be added anywhere, rollback segments are not confined to a single tablespace but can be put everywhere and anywhere. And hard disk costs next to nothing these days.

I would never give your advice to anyone for any reason. And though I wouldn't use Sybrand's less eloquent way of saying it ... he does capture the essence.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)