Re: HELP: Get ORA-1555 rollback too small

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

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.

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 Thu Dec 05 2002 - 16:45:01 CST