Re: PL/SQL and Snapshot too old

Dan Townsend wrote:
>
> I thought I understood 'Snapshot too old' from my earlier incarnation as
> a DBA, but this one seems odd. We have a nightly process that first
> deletes about 100,000 rows from a (much larger) table, then inserts rows
> to replace them (refreshing an external data source). To avoid blowing
> out the rollback segments, our developer performs the deletes in batches
> that are committed (no problems), then does the inserts in a PL/SQL loop
> that commits every 'n' records.
>
> The 'n' records had been set to 20,000. We started hitting the limits of
> the rollback tablespace free space, so cut it back to 10,000. It ran for
> a while, then complained again. We continued cutting it back. Now we
> have it cut down to 1,000 and instead of running out of rollback space,
> it has started complaining about Snapshot too old (ORA-01555). This job
> runs with very little contention late at night. Monitoring the rollback
> segments indicates that they are not extending into the free space. No
> other process is reading the data being inserted.
>
> We are in the process of migrating from Oracle 7.0.15 to 7.3.2 and
> thought the new Server version would solve the problem, but it has
> turned up there also.
>
> Can anyone explain this or help us resolve it? Thanks.
> +-----------------------------------------------------
> | Dan Townsend, Supervising Database Architect
> | EBMUD Enterprise Object Designer
> | mailto:townsend_at_ebmud.com
> +-----------------------------------------------------

Dan -

Let me briefly explain two of the many reasons for ORA-1555

1. Fetches across commits . This occurs when you have opened a cursor which loops thru' fetching,changing and committing records on the same table, in such cases you could very well end up with ORA-1555 as given below.

Lets say that ur cursor was opened to read from a table at SCN=1001. Every fetch by the cursor will now have to return data from blocks with SCN=less than 1001. Now ur user program is fetching data,changing and committing data. Lets say that they were committed with SCN=1020. Now if subsequent fetch has to retrieve a record from one of committed blocks which has SCN=1020, it cannot as it has to get read-consistent data with SCN=1001. Thus it will go to the rollback segment to see if it can rollback sufficiently to SCN=1001,if it cannot 'cause of wrap arounds and overwrite,the query will return ORA-1555. Thus committing less often will result in more rollback info and REDUCE probability of ORA-1555!! Irony indeed !!

2. Fetches across commits with delayed block clean out.

Oracle will do a fast commit when a data block is changed by making txns as committed in the rollback segment header but still may not have as yet cleaned the datablocks that were modified.

Let us in this case take two tables and assume the same situation as in step 1, ie., a cursor is opened on table T1, in a loop again you are fetching from T1 and changing records in table T2 and committing. Though you are committing on T2 you could still get ORA-1555 because cleanout has not been done on T1 from which records are being fetched.

In this case a full table scan before opening and fetching thru cursor will help.

Hope this helps.
Manoj Appully Received on Mon Mar 17 1997 - 00:00:00 CST