Re: PL/SQL limitation while updating table A with a cursor on Table A

Chrysalis wrote:
>
> Shivakumar Gopalakrishnan wrote:
> >
> > I am doing a financial data warehouse currently. We have the Fact Table
> > Balances by Period and the Account(which is a composit of 9 fields). I have
> > a kind of period opening process. What this process does is read the
> > previous month records and create the current month records with the ending
> > balance of privious month as opening balance of the current month.
> >
> > While running this procedure, I always get a ORA-01555, which says snapshot
> > is too old in rollback segment. We have increased the extent size of the
> > rollback segment, decreased the no of records after which I commit, Doubled
> > the tablespace of the rollback segment. Still this error is persisting.
> >
> > Currently we have decided to write the balance to a flat file and load it
> > using sql/loader. Does anyone know of a better way.
> >
> > The problem we found is that, Oracle tries to maintain a copy of the
> > database as it was before, or as of the open of the cursor. SQL/Loader by
> > passes this somehow. Does anyone know of a way to turn this switch off, as
> > is done by the sql/loader.
> >
> > Any help or suggestion is appreciated. Pls mail them to shiva_at_one.net

Another option is to tune the the procedure that updates the table(s) so that it runs more efficiently (thus more quickly). Updating a table with
another process while the procedure is running does not inherently cause the error. Having the procedure take too long, allowing *too many* updates to the table to occur will cause the error to arise.

You might try these ideas:

1. Do not use any *implicit* cursors in your procedure. An implicit cursor is where you 'select into' a variable, or execute a 'cursor for loop'. Because of compliance with ANSI SQL 92 specifications, these constructs incur greater overhead than a 'declare cursor; open cursor; fetch cursor into' construct.
2. Replace joins between large tables with individual cursors for each table you reference. This gives you greater control over the execution plan of your statements, and allows you to select the table that drives the query (which should be the table with the fewest number of rows).
3. Commit less frequently in your procedure. If possible, only commit after the procedure completes. If not, do so every <x> thousand updates.

By speeding up the execution of your procedure and reducing commits you avoid the 'snapshot too old' syndrome. I believe this 'syndrome' is caused by too many records which are referenced in your procedure having
been updated by another process, so many that it exceeds the limits of the
rollback segments and update queues. Or so I believe. Received on Sun Jun 22 1997 - 00:00:00 CDT