Re: commit or not commit inside a cursor loop

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.

-- 
/Karsten
DBA > retired > DBA