Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Snapshot Too Old Reported - But We Doubt It

Re: Snapshot Too Old Reported - But We Doubt It

From: Alan <alan_at_erols.com>
Date: Mon, 2 Aug 2004 15:18:51 -0400
Message-ID: <2n7icrFu37o7U1@uni-berlin.de>


See notes in-line

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:410e902b$0$10612$afc38c87_at_news.optusnet.com.au...
>
> "Alan" <alan_at_erols.com> wrote in message
> news:2n7ak8Fr1bvqU1_at_uni-berlin.de...
> > Situation:
> >
> > Long running update (2 hrs, 10 min) across a dblink
> >
> > Both servers Windows 2000, Oracle 8.1.7.4.1
> >
> > Problem:
> >
> > For the past few months, the query has completed with no problems.
Lately,
> > it will often/usually (but not always) fail with a "snapshot too old"
> error:
> >
> > ORA-01555: snapshot too old (rollback segment too small)
> >
> > The message is followed by an ORA-02063, indicating that the problem is
on
> > the foreign server.
> >
> > The reported RBS varies, so we doubt it is an RBS corruption.
> >
> > We are pretty certain that the problem is not too small RBS. There are
~90
> > RBS, all sized the same:
> >
> > STORAGE(INITIAL 1M
> > NEXT 1M
> > MINEXTENTS 20
> > MAXEXTENTS 32765
> > OPTIMAL 20M)
> >
> > And near as we can tell, they don't get anywhere near maxing out.
>
> Irrelevant. 1555s are not eliminated by setting a massive MAXEXTENTS,
> because that simply means: this is how big you can grow if you ever cycle
> back on top of transactions which haven't committed yet. Whereas 1555s are
> caused by recycling over the top of committed transactions. In other
words,
> it is not the *potential* size of your rollback segments that therefore
> contribute to alleviating or suffering from 1555s, but their *current* or
> actual size.

I see...

>
> It is, incidentally, also irrelevant that you have 90 such rollback
> segments, since a transaction only starts in one of them, and once started
> in one of them, it must finish in that one. That there are 89 lots of
> completely empty rollback segment sitting idly by will not prevent your
> update from recycling its way around the one segment it happened to be
> assigned to.

Yes, I know this. I was just providing info.

>
> > Any ideas what is wrong and how to solve it? TIA.
>
> Oddly enough, the error message is probably more wise on the matter than
you
> give it credit for being: what is wrong is your starting size for your
> rollback segments. How you solve it is to re-create your rollback segments
> with more extents in the first place (ie, change MINEXTENTS) or leave
> MINEXTENTS as she is, but make the extents larger. MAXEXTENTS does not and
> cannot help. Because you cannot know which one of the 90 segments will be
> used to house your long-running update's undo, you must make all 90 of
your
> segments the same larger size.
>
> Or, if you care to use the 'set transaction use rollback segment XXX'
> command as the very first line of your transaction, you can create a
single,
> large rollback segment, and direct your transaction's undo to that,
leaving
> the other 90 segments at their existing size.

I have done this with other updates in the past, but I didn't think that was the issue here. Thanks!

>
> Or you can visit www.ixora.com.au, search for 1555, and use Steve's
scripts
> to raise 90 dummy blocking transactions just prior to your update. That
will
> prevent the undo for your transactions cycling over the top of other undo,
> and at that point your large setting for MAXEXTENTS will kick in to result
> in constantly-growing undo segments. You will end up with a single huge
> segment, and the extra size will have prevented the 1555 from occurring.
>

Interesting idea, but SET TRANSACTION seems a lot easier (an safer).

> Or you can upgrade to 9i, and switch to using automatic undo, and never
have
> to worry about the matter again!

Would that I could...<sigh>

Thanks much!!!

>
> Regards
> HJR
>
>
Received on Mon Aug 02 2004 - 14:18:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US