Re: Snapshot Too Old Reported - But We Doubt It

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

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.

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

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.

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

Regards
HJR Received on Mon Aug 02 2004 - 14:04:54 CDT