rs_arwar_at_hotmail.com (Rauf Sarwar) wrote in message news:<92eeeff0.0305282253.4c5ba51e_at_posting.google.com>...
> 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
Thank you all for your responses. According to the advice from Oracle
site, do NOT commit in a cursor for loop. Get bigger disk drive to
accommodate your rollback segment if necesssary. This is the way
Oracle designs. If you don't like this, write your own DBMS :-)
Received on Thu May 29 2003 - 10:53:01 CDT
