Re: Can sombody list disadvantages of using Optimal setting for Rollback segs.

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:3f90f8cf$0$24515$afc38c87_at_news.optusnet.com.au...

<Snip>

> >
> > Without using optimal and not able to specify a large rollback segment,
> > wouldn't I need to set all 15 rollback segments to over 2 Gig?
>
> So?
>
> >
> > So, my tablespace would go from 6Gig to something like 35-40Gig?
>
> So??
>
> Listen: in 9i, you won't have the option. Well, OK... you can opt to do
this
> botch job in 9i exactly as you have in 8i. But what about 10G, or 11Y???
Do
> you think rollback segments will still be around then? Nah.. it will be
all
> automatic. And at that point, what will Oracle tend to do? It will tend to
> need 35-45Gig of undo tablespace. Add in undo_retention to avoid your
> 1555s, and you might be looking at 200Gig. But who cares??
>
> If you want kiddie-sized databases, I can recommend Access.
>
> You might as well bite the bullet now. It's only hard disk space, after
all.
>

Hi Howard,

I know we've had this discussion before ("optimal size of rollback" thread back in Sept 2002) but having huge rollback segments to cope with abnormal or rare undo requirements (eg. the largest possible transaction) can be *bad* for performance. All these blocks need to be cached at some point and by having 200G of undo, you have zip chance of caching such data effectively. Therefore you will probably induce extra I/O and extra I/O is bad and something you generally want to avoid. Size them to be cached effectively and maybe the load on dbwr(s) can be reduced. The difference between your reads (rare) and your writes (far more common) should be minimized as much as possible. This can only be achieved by tuning your rollback segments and checkpointing appropriately and that does *not* mean making them all as big as the largest transaction. *Both* are important.

Auto undo management is a far from a perfect mechanism as it doesn't necessarily perform the above as best as it could but one thing it does attempt to do is reduce undo wastage by nicking unused extents from other undo segments under certain conditions. If the amount of undo were no issue, then why bother ...

Then of course you have to consider mirroring 200G rather than 6G, backing up 200G rather than 6G, recovering 200G rather than 6G (remember all PIT recoveries require the undo ts to be restored), etc,

As we are discovering, who ever said disk is cheap must have shares with a disk manufacturer ;)

So on the grounds of performance, administration overheads and pure cost, I still can't agree with you :( I would recommend setting and tuning an appropriate optimal mark than the alternative you suggest.

And for those that say, "hold on, you'll just introduce snapshot too old errors", my reply is you haven't set a very appropriate optimal ....

Cheers

Richard Received on Mon Oct 20 2003 - 08:46:57 CDT