Re: Why ORA-1555 snapshot too old.

On Fri, 18 Oct 2002 22:50:54 GMT, Richard Kuhler <noone_at_nowhere.com> wrote:

>I must admit I don't understand your answer. The poster stated that "I
>know the RBS is not wrapping back on itself in that short of a time and
>it is not shrinking either." However, you seem to be implying that
>committing records individually makes this a mute point (i.e. no matter
>how big the rollback was you couldn't stop ora-1555 in this
>situation?). Can you please expound on your answer a little more?
>
>Richard Kuhler

The issue here is read-consistency. The select will use a read-consistent image to get results. Whatever is currently being changed is going to be retrieved from the rollback segment. The select will try to reconstruct a read-consistent image to the point in time *the select started*.
In the mean time, a different process has committed a transaction, which started before the select fired. A commit means that process releases the rollback data, and the rollback data can be overwritten by any other transaction. However the select you are firing still needs it to reconstruct that read-consistent image. Once it discovers it can't find that record(s) anymore because the other transaction has been committed it will result in an ora-1555. There are two solutions to this problem: 1 stop committing every individual record. Most people committing every individual record do this because they want to avoid the rollback segment grow. Now this is silly as they are splitting up their *logical* transaction in smaller *physical* transactions, at the same time increasing the redolog volume (because every transaction has overhead)
2 Increase the rollback segments in size, or stop using optimal, in order to make sure committed data ages out of the rollback segment in a lower tempo.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Oct 19 2002 - 00:54:48 CDT