Re: more vs. larger rollbacks

To paraphrase Thomas Kyte: The most common cause of the 1555 error is committing inside a cursor loop that is updating the same table that is being read.

The commits free the rollback segment extents to be over-written even though the cursor still needs the before image. Oracle isn't smart enough to retain a rollback segment extent just because some query still needs it.

If the rollback segment is soooo big that it cannot wrap around for the duration of the cursor, then the error cannot occur. But this has to be the actual size of the rollback segment -- actual extents already allocated.

If you didn't commit inside the loop, Oracle would automatically extend the rollback segment -- up to the limit of the tablespace. But due to the commits, no extending is necessary.

One other thing: why open all three cursors before the three loops? It would seem more natural to open 1, loop thru it, then the next etc.

Doug Cowles wrote:

> 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.
>
> John Higgins wrote:
>
> > You say that you have created 3 1.5GB rollback segments but the HWM never
> > get above 300M.
> >
> > I think you have NOT created the rollback segments at 1.5GB.
> >
> > If, for example, you specify Initial as 100MB, next ad 100MB and min extents
> > as 2, then you have created a 200MB rollback segment. It may be inside a
> > 1.5GB tablespace, but only big transactions cause a rollback segment to
> > extend. Otherwise, Oracle just wraps around and around the current size.
> >
> > Also, what about OPTIMAL? If it is set to a size less than 1.5GB, Oracle
> > will shrink the rollback segment back to that size.
> >
> > I suspect that you are getting the ORA-01555 snapshot too old; rollback too
> > small type error. This is not an out-of space error. This means a query
> > needs to find old data in the rollback (for read consistency) but the
> > updates have wrapped around and overwritten the old data. Curiously, if the
> > updates had not committed as often, you might avoid this error! (But then,
> > the updates might extend the rollback segments past the size of the
> > tablespace!)
> >
> > This
> >
> > Doug Cowles wrote:
> >
> > > I know the rule of thumb is, lots of connections and users, OLTP, lots
> > > of little and
> > > medium sized rollback segments, big batch jobs, fewer, and larger
> > > rollback segments.
> > >
> > > But..
> > >
> > > We have someone doing some preliminary datawarehouse loads, and have
> > > provided
> > > 3 1.5G rollback segments, combined equalling 4.5G which is bigger than
> > > the database
> > > itself (I know that's ridiculous) , but our user is getting rollback too
> > > small, and the high
> > > water marks on these three 1.5G rollback segments never get above 300M.
> > > Still
> > > PLENTY of room, at far as I know.
> > >
> > > So..
> > > I'm guessing that there are certain situations in Oracle, even with one
> > > user and one session,
> > > where Oracle will ask for another rollback segment, rather than extend
> > > one where there
> > > is plenty of room. Can anyone either tell me I'm wrong, or tell me
> > > what kind of circumstances would cause this?
> > >
> > > As to the errors, I have not seen specifically what it is. User says
> > > it's rollback too small,
> > > but for all I know it could be need more rollbacks or snapshot too old.
> > > Regardless..any
> > > feedback would be appreciated.
> > >
> > > - Dc.
Received on Wed May 26 1999 - 14:00:36 CDT