Re: more vs. larger rollbacks

Ok..need some clarification and I've got a theory. First, she has some select for updates - 1) Does this automatically build a rollback image?
If it did, then 2) even though it is not really being altered, just referenced, row by row,
could the commits by row free up a rollback entry, and then 3) When the same image is requested again, the SCN's are different, yielding snaphshot too old? Side question - if you have time -
Is any kind of rollback information generated by indexes? Another side question -
What triggers a shrink to optimal?
and fourthly - need some clarification below - see comment

John Higgins wrote:

> 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.
>

Does this mean basically, that even if a commit has freed a rollback block for re-usethat it bascially isn't likely to be written over again?

> 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 Thu May 27 1999 - 09:35:19 CDT