"Snapshot too old" issue

[Chris Fung]

Hi all,

We are running a data wareshouse with Oracle8i. We always encounter snapshot too old issue when loading data to the database. We already have 20 rollback segments (other than the system rollback segment) and each with 10M initial and 10M next extents. Since the sql is embeded into the application and we are unable to tuning the application to make use of a single large rollback segment. Is it safe to ignore the snapshot too old messge?? Any suggestion to this issue??

Please help,

Many thanks,

Chris,

[Praveen Bezawada]

Hmmm.
"Snapshot too old" Is it safe ? Safe until you need to rollback ;)
As you said the problem is when data is being loaded which i guess would have at the beginning. Try using larger initial rollback segment size and also do not use the 'Optimal' clause for the rollback segments.
Atleast the segment size won't shrink.

...BPK...

[Kawah Cheung]

Hi,

You can create a large rollback segment, and before you do the load, offline all the other rollback segments. Hence, you will force the load to use the large rollback segment.

Rgds,
Kawah

[Andreas D. Skjervold]

Hi

It is save in terms of recover scenarios, as the "snapshot too old" message has to do with the read consitency function of the rollback segment. The Recover is managerd by the redologs.

What happens is that while you are performing a transaction against a table in your database, that existing rows of data that wil be changed are copied out into the rollback segments so that other user may read these data while your transaction runs.
When getting "snapshot too old" the rollback segment have been overwritten by another transaction.

What this means is that Oracle can't present a read consistent view of the data.

If this happen during a large load its should be safe to ignore by the terms of the load, but should be resolved to minimize users problems.

Increase the rolbacksegments MINEXTENTS, set larger extent sizes and a higher OPTIMAL value.
Increaseing MAXEXTENTS will not resolve the case.

Andreas

[Brian Crabtree · Accepted solution]

Also, see how often a commit is performed in your transaction. You should be commiting after 10,000 records or so. I have found that a snapshot to old error will occur when the rollback segment initial and next extent values are large and are being commited too often. This forces the rollback segment to wrap around itself, overwriting data that is required to keep the transaction consistant.

If you do not have any control over the commit statement, you might want to try reducing the size of your rollback segments. A good place to see if they are too small/large is the "AVEACTIVE" column of the V$ROLLSTAT view. You can more effectivly tune your rollback segment, rather than increasing it to a high number that will cause more problems. Otherwise, creating a single large rollback segment, and altering the rollback segments offline and online to fit the current need is also possible, although should not be used in a 24/7 enviroment.

(P.S. While a Snapshot too old error will only occur on a long running transaction, remember that most applications use cursors, and a cursor is considered to be a transaction until it is closed. Something to remember)

Thanks,

Brian

[T G Manikandan]

Hello,

As the data blocks are read on behalf of the query, only blocks with lower
SCN than the query SCN will be read. If a block has uncommitted changes of
other transactions or changed data with more recent SCN, then the data is
reconstructed using the saved snapshot from the rollback segments. In some
rare situations, if RDBMS is not able to reconstruct the snapshot for a long
running query, the query results in ORA-1555 error.

I just went through two more causes for the snapshot old error.
Apart from the size of the rollback segments or the corrupted rollback segment,

Fetch across commit

This is the situation when a query opens a cursor, then loops through
fetching, changing, and committing the records on the same table
very often ORA-1555 can result. For ex.

A cursor was opened at SCN=10. The execution SCN of the query is then
marked as SCN=10. Every fetch by that cursor now need to get the
read-consistent data from SCN=10. The user program is now fetching
x numbers of records, changing them, and committing them. Let's say they
were committed with SCN=20. If a later fetch happens to retrieve a record
which is in one of the previously committed blocks, then the fetch will
see that the SCN there as 20. Since the fetch has to get the snapshot
from SCN=10 it will try to find it in the rollback segments. If it could
rollback sufficiently backwards as previously explained, then it could
reconstruct the snapshot from SCN=10. If not, then it will result in
ORA-1555 error.

Committing less often which will result in larger rollback segments will
REDUCE the probability of getting 'snapshot too old' error.

Fetch across commits with delayed block clean out

When a data or index block is modified in the database and the transaction
committed, oracle does a fast commit by marking the transaction as
committed in the rollback segment header but does not clean the datablocks
that were modified. The next transaction which does a select on the modified
blocks will do the actual cleanout of the block. This is known as a
delayed block cleanout.

But instead of assuming one table, lets assume that there are two tables in question.
i.e: the cursor is opened and then in a loop, it fetches from one table and
changes records in another, and commits.
Even though the records are getting committed in another table it could
still cause ORA-1555 because cleanout has not been done on the table
from which the records are being fetched.

For this case, a full table scan before opening and fetching through the
cursor will help.



Thanks

[Simeon Fox]

To reiterate - the message means oracle cannot rollback the load transaction, as some of the rollback info has already been overwritten by other transactions. So if the load falls over you could end up with a corrupted database. You would have to manually reverse the load in SQL and then try to repeat.

Do you know how the data is being loaded - by sql-loader or a database package? And how large are the rollback segments? (select rssize from v$rollstat). If you are unable to modify the underlying SQL, you need to drop the existing segments (not the system one) and create a series of much larger ones, disk space permitting. The load transaction will then allocated to a larger rollback segement, hopefully avoiding the ORA-01555.

You must also retain multiple rollback segments to reduce the risk of multiple transactions being allocated to the same segment.

Hope this helps!

[Brian Crabtree]

Simeon,

No, no, no. Under no circumstances will this corrupt the database, or leave the database in an inconsistant state. All it means is that the transaction has to rollback due to the fact that the transaction cannot find data required to complete the transaction in the rollback segment.

Brian

[Yogeeraj_1]

Hello Chris,

Maybe due to your application design, etc. Your rollback segments are too small. You will need to use larger rollback segments.

Explanation:
There are numerous reasons why this can occur but basically for the operation that failed the information it needs to construct a read consistent view of the data is no longer available.

Diagnosis:
Is the user FETCHING across a COMMIT ?? If so this breaks the ANSI standards and is not guaranteed by Oracle. Make sure they know this then proceed onto describing the options below which may help.

The main way to avoid this is to use larger rollback segments across the system. This gives you a longer period of grace before 'old' rollback infomation gets overwritten. It is typically no use just enlarging a single rollback segment.

For long running reports, try to run these when the database is lightly loaded so that the chance of a rollback segment wrapping is reduced (as there are fewer transactions in progress and hence less rollback information being generated).

Try using an ORDER BY clause that forces a sort in the query - this should produce the row-source up front so undo is only needed while this ordered row-source is produced.

If this is on a SNAPSHOT update check whether the SNAPSHOT is performing a full or fast refresh. A full refresh could be occuring.

[information from Oracle Metalink]

Hope this helps
Best Regards
Yogeeraj

[Simeon Fox]

Yes of course oracle will rollback when it hits error, sorry for that!

From Oracle:

"There are a few common cases where the ORA-01555 may appear:

1. Few number of rollback segments with potentially small sizes in a very active database (e.g., multiple users doing on-line transaction processing -- OLTP).

2. Corrupted rollback segment prevents a consistent read requested by a query from being able to read the block in the rollback segment.

3. Fetch across commits within an open cursor (cursors retain the "snapshot" of the query at cursor open time).

4. Fetch across commits with delayed block cleanout (the data block is not updated with the last committed image until the next reader accesses the data block, which requires verification from the transaction table of
the rollback segment)"

So certainly drop and re-create all your segments in case you have corruption.

If error is due to multiple transactions, increasing size and number of rollback segments should help. Also try to run the load at a time when little or no other activity is occuring.

However since it is the load itself which is generating the error it could perhaps be (3) or (4) above. Difficult to know without access to the code. For (4) Oracle recommend running full table scans (select count(*) in rule mode or with a "full" hint) of all involved tables before the start of the transaction.