about "snapshot too old" problem

[稀里哗啦]

I don't know why the below ways can resolve "snapshot too old" problem.
1. higher MINEXTENTS value
2. large extent size

[Brian Crabtree]

By allocating more space, it will force the rollback segments to not do small extents. This should not be the only path that you take.

Other things to look at are "fetch across cursors", where a cursor is called from a loop based on another cursor, and the data from the initial cursor is being modified. This can cause a snapshot too old.

Also, committing to frequently can cause this issue, as it will constantly pull new extents from the rollback segment tablespace, forcing it to go through all of the extents available in the rollback segment, and eventually wrapping around to the beginning.

If you want to post the information from v$rollstat and dba_rollback_segs, we might be able to take a look and offer advice.

Brian

[Volker Borowski]

Hello,

actually, these values, in addition with a corresponding OPTIMAL value, do NOT "resolve" this problem, but they give you a better chance that this error MIGHT PROBABLY not show up.

Basicly the only way to surely (!) avoid a snapshot too old, is to use a SINGLE rollbacksegment, disable OPTIMAL from the storage, so that you would have to issue a shrink manually.
In addition, it would be good to have a dummy table available where you can insert a value into.

Armed with this setup, you go by

create session (1)
alter rollback segment rs_name shrink;
insert into dummy_table ... ;
< leave this session as it is, open and waiting for input >

create session (2)
Perform, whatever might get a snapshot too old
COMMIT

COMMIT or ROLLBACK session (1)

This works for sure!

Unfortunately due to multi-user requirements, this normaly is a useless approach for productive systems. So the only other option is to maximize the chance that the rollback information, you need for your transaction is overwritten as late a possible.

This can be done by:
- doing no or later shrinks -> higher OPTIMAL value.
- using more rollback segments
- (and best) avoid concurrent transactions

Hope this helps
Volker

[Dave Chamberlin]

There are many potential causes of this problem - and many ways to reduce its occurrence. Each transaction needs a rollback segment, so the number of segments must be higher for more transactions to coexist. Larger extent sizes will allow more data to be placed into the segment, so if you have large transactions, this will help resolve the "snapshot" problem. If you have large and small transactions both you increase the likelyhood of failure. Batch inserts etc should not be performed when there is a lot of small transaction activity. Large inserts etc can be broken up into smaller pieces (with frequent commit statements).

[Jean-Luc Oudart]

The extent size depends on the type of transaction you're running in the system. The number of concurrent transactions may be important and you would require a rollback seg for 4 to 10 users depending on the type of transaction and/or activity (read vs update).
If this is happenning for s specific transaction such as a (big) batch program you may require a big rollback segment and assign this particular rollback segment at the beginning of the transaction :
set transaction use rollback segment BIGRLBG

JL

[Aashish Raj]

if you using cursors to do the DML, you can put an order by clause in the cursor which will minimize the risk of this error.

Thanks
AR

[Raynald Boucher]

First, you must ensure that you have sufficient total space available otherwise to cover your largest possible update transaction, no matter how many segents you have, your updates will fail.

Second, your want to remember that space deallocation is only performed when a new transaction is assigned that specific rollback segment. For example: there are 10 RBS, xactn1 gets assigned RBS1, RBS1 grows massively because it is a large update, meanwhile, xactn 2, 3 and 4 get assigned RBS 2, 3 and 4 respectively and so on.
Only when xactn11 gets assigned RBS1 will RBS1 be examined for any "freeable" extents, if none are found none are released until xactn21, 31 and so on.

Higher minextents reduces the risk of snapshot too old because the rollback segment always has some extents reserved for it many of which are unused. In this way, other segments cannot capture all of the available space.

Larger extents permits larger updates and/or a higher volume of small concurrent updates within those minextents.

Hope this helps
Take care.