Re: optimal size for rollback

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:J1lg9.31098$g9.89398_at_newsfeeds.bigpond.com...
> Hi Daud,
>
> Must be looking forward to moving onto 9i when you won't have to worry
 about
> all this :)
>

Uh huh. Instead he has to worry about whether his undo tablespace is big enough and whether undo rentention is set appropriately. Automatic undo does not mean irresponsibility.

> The "problem" with optimal is that by deallocating a bunch of extents, it
> greatly increases the likelihood of ORA-1555s, snapshot too old.
>

That's just the start of it. Having to deallocate extents just as I'm in the middle of my transaction is also something to worry about.

> In the case of a large transaction, I would look at assigning it to a
> pre-created big mamma of a RBS with the SET TRANSACTION USE ROLLBACK
 SEGMENT
> big_mamma command.

Oh dear.

>That way, you don't have the overheads of dynamically
> allocating (and later de-allocating) additional extents and queries won't
> freak out as they can access the undo they're after.
>
> I would also just increase the number of your extents

Oh dear, oh dear.

>a tad to perhaps
> reduce the likelihood of an extent containing an active transaction at
 wrap
> time.

6 extents is just fine. The issue of the wrap encountering an active extent is a matter of extent size, not the number of extents. Or it ought to be, at any rate.

HJR
>
> Cheers
>
> Richard
> "Daud" <daud11_at_hotmail.com> wrote in message
> news:f0bf3cc3.0209130205.2cd2db2_at_posting.google.com...
> > Hi
> >
> > I have been reading quite a bit about rollback segments and I kinda
> > agree that setting optimal size is not quite a good idea. That shows
> > that a dba has not done his job to find out what the correct size of
> > the rollback segment should be.
> > This is what I am thinking of doing and let me know if it does not
> > make sense.
> >
> > initial 1M
> > next 1M
> > minextents 6
> > optimal 6M
> >
> > The reason I want to set optimal is because occasionally I have some
> > big jobs that cause a rollback segment to grow. However, I do not want
> > to have to manually go in and re-set its size once the jobs are done.
> > So, I thought setting optimal will take care of it. What do you
> > experts think?
> >
> > rgds
> > Daud
>
>
Received on Fri Sep 13 2002 - 08:37:49 CDT