Re: PL/SQL and Snapshot too old

Dan Townsend <townsend_at_ebmud.com> wrote in article <3326FA5D.3421_at_ebmud.com>...
> 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
> 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

This is a pretty common problem with batch processes. You may have a 'fetch across commit' problem. The ansi standard says that, once a cursor is open and then you commit, you can't fetch again without reopening the cursor. Oracle let's us do that, 'fetch across commit'. The price we pay is that the database has to maintain more information to maintain the read consistency of the original cursor, which it guarantees to do. Basically, when we do those frequent commits, we take a chance on stepping on our tail. The answer is to commit *less* frequently, to generate less undo info in the rollback segment. Plus, you are doing deletes, which generate more undo than most statements.

Make sense? Hope so. For further info, see any Oracle doc on read consistency. Look in the knowledgebase for 'fetch across commit' stuff. I think Oracle has a fax doc on the subject.

Roger Received on Mon Mar 17 1997 - 00:00:00 CST