RE: Strange snapshot too old during a select statement

From: Justin Cave (DDBC) <jcave_at_ddbcinc.com>
Date: Thu, 11 Mar 2004 01:59:17 -0700
Message-ID: <87E9F113CEF1D211A4C3009027301874195D32@ddbcinc.ddbc.local>

I'll wager that what is happening is the following:

Your long SELECT starts and the system notes that the system change number (SCN) is x
Another transaction comes along and updates a row. The current version of the row has a SCN of x+1. The old version of the row, the one from SCN x, is stored in the rollback segments (or UNDO)
At some point, your SELECT needs to read this block as of SCN x, so it has to visit the rollback segment to get the old version of the data.
When Oracle visits the rollback segment, it finds that the data has been overwritten. Since Oracle cannot reconstruct a picture of the data at SCN x, it throws the ORA-01555 error.

If you were using UNDO, this would be a bit easier to deal with-you would set the undo retention time to the length of your query and ensure that you throw enough disk in to handle the UNDO generation for that amount of time. When you're dealing with rollback segments, though, you have to figure out why the data is no longer available when the query wants it. You may have too few rollback segments, you may have rollback segments whose OPTIMAL values are too small, your rollback segment size may be too small.

Justin Cave

Distributed Database Consulting, Inc.

http://www.ddbcinc.com/askDDBC

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of NGUYEN Philippe (Cetelem) Sent: Thursday, March 11, 2004 1:46 AM
To: 'oracle-l_at_freelists.org'
Subject: Strange snapshot too old during a select statement Importance: High

Hi list!
we encounter a very strange "ORA-01555 : snapshot too old :rollback segment number 3 with name "_SYSSMU3$" too small" This is a very long query
With a query that only make a select statement. We have open a TAR with Oracle Support but they say that it's not possible since this only a "select" statement ?!! Any idea ?

TIA
Philippe

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Thu Mar 11 2004 - 02:53:49 CST