| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: HELP: Get ORA-1555 rollback too small
"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,
>
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
> 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.
>
>
> > >Received on Fri Dec 06 2002 - 01:01:59 CST
 |
 |