VIEWS: 15 PAGES: 1 POSTED ON: 8/29/2009
There may two Cause for SNAPSHOT-TOO-OLD error:(1) Since space managemnt in undo segment follo LRU algorihm.So in space allocated for to undo segment will so small to data may frequently swaped out from undo segement and may cause SNAPSHOT-TOO-OLD error. (2) It also depend on the undo retention policy set by the DBA.If the undo retention time is set to very low the the data will sweped out too early and may cause SNAPSHOT-TOO-OLD error. When any DML operation is taking long time which may lead reuse of datablocks of undo segment by another DML, in this case oracle will throw snapshot too old error. For any DML oracle stores the undo image in rollback segment, suppose transaction A is in progress which cause datablock from undo segment been used by this transaction. And now if another transaction B is started which need datablocks from undo segment to keep undo copy, but if rollback segment is not large enough to provide the datablocks for second transaction B, then transaction B will use the datablock which are already in use to retain the undo image for transaction A, and this will lead to snap shot too old. Simple solution change the commit point by chaning checkpoint frequency. And if it's possible then increase undo segment size by adding datafile to undo tablespace. Anyways, ORA-1555 snapshot too old error is thrown by a "query" when it fails to get a read consistent image of data its querying. What is read consistency? When a long running query(running into hours) is issued, its possible that before the query finishes and gives u result some other DML "transaction" may change the data that the query was reading.This will cause the query to return inconsistent result(which is not allowed as per the ACID properties). To avoid this Oracle has something called as Undo segments which will store the old value of changed data blocks.So now if a query hits a data block which was changed after it started, it will go to the UNDO and pick up the old value(the one which was prevalent when the query has started its execution.this is also the READ CONSISTENT copy). Ok, now when will ORA-1555 occur? If the query is unable to get the old(read consistent) copy of the data in the UNDO-because it may have be overwritten-then...yes my friend you are right..thats the time the query throws the ORA-1555 error.
Pages to are hidden for
"Snapshot_too_old"Please download to view full document