Re: Problem with rollback segment (ORA-01555)

Your maxextents setting is irrelevant. That just *permits* the segment to extend up to 1000 extents (a ludicrously high figure, incidentally), but it doesn't mean it *will* acquire that number of extents, unless it has to.

And it won't feel it has to if your application constantly commits, and you only have 2 extents.

As your latest transactions reach the end of extent 2, they see whether they can move back into extent 1, or whether they must acquire extent 3. If extent 1's transactions have all been committed, then it can move into extent 1. If only one tiny transaction is still live in extent 1, then it must extend the segment, and write into the new extent 3.

Assuming it moves back into extent 1, your latest transactions will now merrily over-write the blocks that were used by earlier transactions -because, by definitiion, those transactions are dead, and the blocks are not needed, for transaction rollback. Except that the information those blocks contain are *still* required for read-consistent image preparation. The second your new transaction over-writes information still needed for read-consistency purposes, you get the infamous 1555 error.

The answer is dead simple: do what the error message tells you to do. Extra extents will do it, or have bigger extents. Either way, you end up taking much longer to loop back round the segment and get into the over-writing business that causes all the trouble.

Other more subtle cures (though not recommended necessarily): don't commit. Well, don't commit until the very end of your updates, anyway. If all your transactions are still live, they cannot be over-written.

And rather more recommended, check out Steve Adams' www.ixora.com.au website, where he has scripts guaranteed to prevent 1555's. He pulls this trick off by the simple expedient of raising a dummy transaction in every rollback segment, which only gets rolled back when it's safe to do so.

At the end of the day, though, Steve's scripts simply force the rollback segment to acquire extra extents and thus get bigger dynamically, as would the business of never committing. It would be much better to have the segments the right size before you begin, dynamic growth of anything not being good for performance.

Regards
HJR <c_joh_at_my-deja.com> wrote in message news:93pvgj$m2g$1_at_nnrp1.deja.com...
> I have a problem with rollback segments that I don't understand. I'm
> running Oracle EE Ver. 8.0.5.1.0 and I have written a client
> application that will insert/update some tables in the database. It all
> works very well except that I now and then get the error message
> "ORA-01555: snapshot too old (rollback segment XX too small)". I have
> currently 5 rollback segments online. They all are quite big, more than
> should be needed for the transactions that occur from the client
> program. The database is in a test environment where there is no other
> users connected.
>
> When I query V$ROLLSTAT I can see that none of the rollbacksegments
> uses more then 2 extents and as the maxextent is set to 1000 there
> should be plenty of room to allocate more extents. The datafile is set
> to autoextend = ON.
>
> If anyone could give me a hint on what to look for I would be most
> grateful.
>
> -Carl
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Sat Jan 13 2001 - 13:29:51 CST