Re: optimal size for rollback

Hi Daud,

Must be looking forward to moving onto 9i when you won't have to worry about all this :)

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

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. 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 a tad to perhaps reduce the likelihood of an extent containing an active transaction at wrap time.

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 - 07:51:16 CDT