Re: HELP: Get ORA-1555 rollback too small

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Thu, 5 Dec 2002 05:28:08 +1100
Message-ID: <cGrH9.90409$g9.252370@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.

Received on Wed Dec 04 2002 - 12:28:08 CST