Re: snapshot too old question

mr_potato_head_at_my-deja.com wrote in message <8ttkpd$jm1$1_at_nnrp1.deja.com>...
>Hi,
> I'm running 8.1.6 on solaris 7 and I'm getting a "snapshot too old"
>on a table that is not being updated. I thought I could only get this
>error if the table was being updated and the rollback came around on the
>rollback segment and over wrote my files I needed for read consistency.
> Why would I get this error on a table that is just being "select"ed not
>being update? Thanks in advance...

This could be caused by the effect of "delayed block cleanout" and occurs as follows:

The table you are reading has been updated and committed some time in the past, possibly before your query started. However, when Oracle commits a transaction, it does not mark all the affected data blocks as committed, as this would involve a lot of work, especially if a lot of blocks had been updated by the transaction.

Instead it simply marks the transaction entry as committed in the rollback segment header. The data block itself is only marked as committed (cleaned-out) when some other query (yours, for example) tries to access the block. It will see that the block is apparently uncommitted and so visits the rollback segment to confirm or deny this. If all is well, the transaction entry in the rollback segment header shows that the update has, in fact, been committed, so Oracle updates the data block to reflect this (avoiding the need to go through this processing again in future) and returns the current data to your query.

However, if there has been much update activity since the transaction was committed (perhaps on other tables), the transaction entry in the rollback segment header will have been overwritten and so Oracle can no longer determine the true state of the data block. "Snapshot too old" is returned.

The usual fixes can be applied to this problem: ie. increase the number and size of your rollback segments. However another solution to this particular problem is to force block cleanout to occur by running a query which does a full tablescan of the affected table before you run your query. Of course, this may suffer from "snapshot too old" itself, but as you'll be expecting it, you can manage the situation. Also, if you run the FTS soon after the updating transaction the problem will be avoided as the rollback segments won't have been overwritten. Note that if your query uses indexes, it may be a delayed cleanout of an index block which is causing the "snapshot too old", in which case you will need to execute a query which performs an index scan to force the cleanout of the updated index blocks.

Refer to my website for more common causes of "snapshot too old"

     http://home.clara.net/dwotton/dba/snapshot.htm

HTH, Dave.

--
If you reply to this newsgroup posting by email, remove the "nospam"
from my email address first.