Re: Oracle rollback segments

In article <1994Jul26.173248.23306_at_tellab5.tellabs.com>, John Vasicek <jsv_at_tellabs.com> wrote:
>
>Looking for some help. We are doing an evaluation of Oracle 7 on for
>SunOS 4.1.3. We have a ProC program which performs the following
>function.
>
>
> define cursor 1 select from A,B,C where A.a = B.a and B.b = C.b;
> open cursor 1;
>
> fetch record;
> while records remain
> depending on record type loop n times (where n <= 50)
> insert into table D;
> COMMIT;
> fetch next record
> endwhile
>
>When we run a single process, all is well. When we attempt to run five
>processes simulateously we get Oracle error -1555 "Snapshot is too old".
>
>We have tried just about everything which we can think of including
>assigning this transaction to a rollback segment with an itial size of
>35M!
>
>We are terribly confused as to exact what information Oracle is attempting
>to take a snapshot of. Also, since we do a commit after each insert we
>would not expect an excessive accumulation of rollback information.
>

This may point you in the right direction but I don't see the exact problem...

The snapshot too old message is due to the way oracle implements read consistency. Read consistency ensures that the data retrieved by a query will be a snapshot of the data at the time the query was started, no matter how long the query runs or how many changes are made to the data. Oracle maintains this by assigning a system change number (SCN) to any row that was changed and storing that SCN as part of the transaction in the rollback segment. When your query is running, Oracle returns either a row from the table or from the rollback segment depending on the time information in the SCN. If, for any reason, oracle cannot retrieve the appropriate row from the rollback segment, you'll get a snapshot too old message.

In your case, a "snapshot" is being maintained for all three tables (A,B,C) for each proccess that you run. I don't know what the phrase "no matter how long the query runs" actually means with respect to read consistancy, but it might mean until the cursor is closed. I don't see any close cursor statements in your psuedo-code so you might try that. Hopefully, understanding what Oracle is trying to do will give you some insight on solving the problem.

Mike Marolda
Bristol Database Resources Inc. Received on Thu Jul 28 1994 - 15:14:37 CEST