Re: Snapshot too old error revisited

Richard Hansen wrote:
>
> Previously, someone posted a problem that they were having with the
> ORA-01555 (Snapshot too old error). I have been having the same
> problem and followed the advice of those who responded to the original
> post. I am still occasionally having the problem and would like to
> present my problem and beg for help.
>
> I am inserting about 3.5 million records from one table into another.
> I have to process each record a bit before insertion so have declared
> a cursor on the source table and fetch each record, process it, and
> insert into the destination table. Every 100,000 rows I commit and
> continue.
>
> The last time I ran this it blew after inserting 300,000 rows. Oh
> yeah, in preparation for this I created a pretty big rollback segment
> (~ 1 gig) and at the beginning of the script as well as after the
> commit I perform a "alter session use rollback segment ..." so that I
> can utilize this big rollback segment.
>
> Last week I ran this during business hours and it ran successfully. I
> ran it yesterday evening (Saturday) and it ORA-1555'ed as mentioned
> above.
>
> What am I missing? Any suggestions?
>
> Thanks in advance for any direction.

Hi Richard,

This is from oracle bulletin 103220.525:

3. 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. In    this scenerio, very often ORA-1555 can result. Let's take the following

   example to explain this.  

   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.  

4. Fetch across commits with delayed block clean out  

   To complicate things, now we see how delayed block clean outs play an    important role in getting this error.  

   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.  

   Now, take the same scenario as described in previous section. 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.    

   Summary:Fetches across commits as explained in last two cases are not supported by ANSI standard. Accoding to ANSI standard a cursor is invalidated when a commit is performed and should be closed and reopened. Oracle allows users to do fetch across commits but users should be aware that it might result in ORA-1555.

-- 
Hans-Peter Sloot   		Email: H.G.Sloot_at_PTT-Telecom.nl  
Phone: +31 50 5855436		Fax  : +31 50 5855777
DISCLAIMER: This statement is not an official statement from, 
nor does it represent official position of, PTT Telecom B.V.