Re: HELP: Get ORA-1555 rollback too small

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<ZOXH9.91432$g9.255407_at_newsfeeds.bigpond.com>...
> "Paul Brewer" <paul_at_paul.brewers.org.uk> wrote in message
> news:3defdeaa_2_at_mk-nntp-1.news.uk.worldonline.com...
> > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> > news:cGrH9.90409$g9.252370_at_newsfeeds.bigpond.com...
> > >
> > > "Igor Izvekov" <igoriz_at_cmtk.net> wrote in message
> > > news:9f17469e.0212031232.6d37b9c3_at_posting.google.com...
> > > > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> news:<F3OG9.88862$g9.249416_at_newsfeeds.bigpond.com>...
> > > > > "Ban Spam" <ban-spam_at_operamail.com> wrote in message
> > > > > news:Xns92D8438ADF7DSunnySD_at_68.6.19.6...
> > > > > > "A. Dischner" <dischner_at_klch.med.uni-muenchen.de> wrote in
> news:dischner-
> > > > > > CE4085.15331002122002_at_hydra1.fw.med.uni-muenchen.de:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > with Oracle 8.1.7.0.1 i get ORA-1555 which means
> Rollback-segment
> is
> > > > > > > too small.
> > > > > > >
> > > > > > > I have really big rollback segments: Tablespace 4gb, initial
> 4MB,
> next
> > > > > > > 4MB, optimal 16MB, max 4096, number of RBsegs: 4
> > > > > > >
> > > > > > > Tablespace usage is always < 1% as seen in Enterprise manager.
> > > > > > >
> > > > > > > There are no critical locks.
> > > > > > >
> > > > > > > any ideas? hints?
> > > > > > >
> > > > > > > Toni
> > > > > > >
> > > > > >
> > > > > > ORA-01555 can also mean that the code is issuing COMMITs
> > > > > > too frequently.
> > > > >
> > > > >
> > > > > Not really. If you had an infinitely large rollback segment, then
> you
> could
> > > > > commit every nanosecond and you still wouldn't loop round the
> segment
> and
> > > > > start over-writing the earlier rollback.
> > > > >
> > > > > Of course, given that rollback segments are of finite size, then
> yes:
> > > > > frequent commits give the segment the go-ahead to overwrite the
> committed
> > > > > rollback, and thus give the 1555 a chance to occur. And so it is
> definitely
> > > > > the case that inappropriate commits contribute to the problem. But
> it
> still
> > > > > comes down, fundamentally to size. After all: what will happen if we
> don't
> > > > > commit so frequently? Then the rollback segment won't be permitted
> to
> > > > > overwrite the earlier rollback, and the segment will begin to grow
> > > > > dynamically in response.
> > > > >
> > > > > Regards
> > > > > HJR
> > > >
> > > > What if you have optimal size on a rollback, so potentially it can
> > > > grow big, but is not so big initially ? If you commit too often, how
> > > > will Oracle know that it should extend rollback ?
> > >
> > > It won't. Therefore it will stay small, you will over-write the
> committed
> > > rollback, and you will probably get 1555s.
> > >
> > > Notice that it is because it *stays small* that the 1555s occur.
> > >
> > > If you made the segment huge to begin with, and had a huge optimal
> setting,
> > > you wouldn't loop round, you wouldn't over-write, and you probably
> wouldn't
> > > get 1555s.
> > >
> > > It's still a size thing at the end of the day.
> > >
> > > >It will happily
> > > > recylce it instead, because it it is allowed to do so by your commit.
> > >
> > > No... it recycles because your segment is too small. Yes, your commits
> > > permit the recycling. But it only needs to be permitted because the
> thing's
> > > too small to start with.
> > >
> > > I'm not advocating frequent commits, by the way.
> > >
> > > HJR
> > >
> > > >
> > > > Igor Izvekov.
> > > >
> > > > P.S. E-Mail address is fake.
> > >
> > >
> > Howard,
> >
> > I hope I have misunderstood, and I hate to contradict. I fully expect that
> > it is I who have misunderstood.
> >
>
> Certainly is, I think.
>
> > But surely business transactions should commit according to business
> > requirements. If a whole raft of updates need to succeed or fail as a
> > logical unit, then so should it be.
> >
>
> I wasn't saying anything about committing, one way or another. Of course a
> transaction is a transaction, and should be committed when it is
> appropriate. Quite what committing has to do with anything I wrote, I'm not
> sure.
>
> I have been arguing here that 1555s are because of the size of the rollback
> segment. Someone else said "If you commit too often, how will Oracle know
> that it should extend rollback", thus bring the frequency at which you
> commit into the equation.
>
> Like you, I believe the frequency of commit is (or should be) governed by
> business logic, and is therefore 'difficult' to modify. That's why it's SIZE
> which is the cause of 1555s (over which you have control) and not commit
> rates.
>
> HJR
You can commit at whatever rate your heart desires, just keep in mind that whenever you do this you increase risk of 1555 for some other long running query against the same table. Having LARGE rollback segment is certainly an option, but sometimes DBAs prefer to specify an OPTIMAL size, so that not to waste disk space, I beleive. I agree that in a perfect world rollback segments should be of infinite size, or Oracle should not recycle them if there are queries needing data from them. And of course business rules should govern database activity - in a perfect world that is.

Igor Izvekov.

>
> > And if a second transaction needs enough resources in order to wait for
> the
> > first, then allocate them. Alternatively, defer the second transaction
> until
> > the first has made up its mind.
> >
> > This may, of course, be total rubbish. But if so, please correct me.
> >
> > Regards,
> > Paul
> >
> >
> >
Received on Fri Dec 06 2002 - 11:25:22 CST