Snapshot too old...

[rsuri]

While executing a procedure, I am getting the 'snapshot too old...' error --

SQL> exec sms.computeloadterminaltm
begin sms.computeloadterminaltm; end;

*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 7 with name "RBS4" too small
ORA-06512: at "SMS.COMPUTELOADTERMINALTM", line 24




I don't think though that increasing the rollback size will help because there is plenty of space in the tablespace for the rollback to grow but the high-water mark shows that it's not extending beyond initial 2 extents (mening it does not need more space for rollback)

The procedure involves a long group-by query that sends results into a table (insert into...)

Any idea why this "snapshot..." error when there is room for rollback to grow ?

Thanks,
- Rajeev

[hnagia]

Make a new set of rollback segments with a big OPTIMAL value

[pando]

you better set initial extents to at least 20. The size of tablespace is not the problem here, the problem is the extent in RBS4 which a transaction is using has been overwritten by another transaction while you were querying that extent.

When a rollback segment grows, it does this circularly, if the next extent which it wants to extend to is occupied it must *spawn* another extent, if then the occupied extents is freed it will be eligible again to be overwritten by another transactions, now if a long query started just before the extent was freed and reaches the freed extent it will find an inconsistency because that extent would be overwritten by other transactions and causing SNAPSHOT TOO OLD error. To avoid this you need high number of initial extents and bigger size extents.

[rsuri]

Thanks Pando and hnagia.
For a DSS system which of the following Rollback segment configuration do you think is better and why ?

inital : 200M
next : 10M
minimum : 1
optimal 300M (or should it be 200M)

Or

inital : 10MB
next : 10MB
minimum : 20
optimal 300M

Thanks,
- Rajeev

[marist89]

For a great explanation, check out [url]http://osi.oracle.com/wa/ask/owa/ask_tom_pkg.display?p_dispid=275215756923[/url]

[rsuri]

Thanks marist,
that site was really helpful.

- Rajeev