Re: more vs. larger rollbacks

From: Doug Cowles <dcowles_at_bigfoot.com>
Date: Wed, 26 May 1999 12:47:52 -0400
Message-ID: <374C25B7.E2DC840C@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....

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 - 11:47:52 CDT