Re: more vs. larger rollbacks

Here is a copy of an article from Oracle Tech Support on the Snapshot too old error.
http://support.us.oracle.com/cgi-bin/cr/getpart_cr.cgi?BUL-103220.525

Francis Sommers
EDS Systemhouse

Doug Cowles wrote in message <374C25B7.E2DC840C_at_bigfoot.com>...
>Ok.... You're probably right, it is probably a snapshot too old error, but
I
>have
>trouble understanding why. The code consists of 3 loops, all updating
exactly
>the
>same table, committing after every record. The error occurs on the third
loop.
>There is a completely unnecessary select for update in a cursor for each of
the
>loops.
>The update part is unnecessary. Does a select for update cause rollback
entries?
>
>Even if it did, they are not really updated, so I am confused. Code looks
>similar
>to the following, keeping in mind that page_fact is about a million rows.
>
>cursor a is select akey from atable for update;
>cursor b is select bkey from btable for update;
>cursor c is select ckey from ctable for update;
>begin
>for i in a loop
> update page_fact
> set pf_key = akey
> where x=y;
> commit;
>end loop
>
>for j in b loop
> update page_fact
> set pr_key = bkey
> where x=y;
> commit;
>end loop
>
>for k in c loop
> update page_fact
> set ps_key = c_key
> where x=y;
> commit;
>end loop
>
>Supposedly, the c loop is where the code bombs. I'm going to re-run it
over
>night to be sure -
>but I don't see why this would cause a snapshot too old error (if all the
>assumptions
>are correct) Maybe someone else would.
>
>As to the rollback sizes - you are right, they are not really 1.5 GB, they
are
>really 60MB
>optimal 70MB with unlimited extents. But they have the ability to extend
to 1.5
>GB, which
>would eliminate the possibility of them not being big enough no? Since you
said
>
>that snapshot too old is not a space error, I am sort of encouraged, but
curious
>as to why
>the above would cause it. If anyone can suggest futher reading, good
threads
>etc., on
>snapshot too old I would be much obliged....
>
>- Dc.
>
Received on Fri May 28 1999 - 07:43:07 CDT