Re: optimal size for rollback

Howard J. Rogers wrote:
>
> "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
> >
> >

And what's more (imho), automatic undo is basically an OPTIMAL clause just under the covers...Its like "Oops, I'm an extent thats 3 seconds over the retention limit...bang, I'm outta here"

Cheers
Connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."