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

Richard Foote wrote:

> 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.

If his large transaction was rare and predictable, I'd agree with you, which is why I mentioned to him having the large segment in a tablespace of its own, and usually offline.

But he (apparently) doesn't have things as pat as that, or (I forget which) doesn't want to/isn't able to manipulate tablespaces like that.

So what do you do at that point? You have a routinely mixed load, some small, some large, and you have little or no control over where things end up. At that point, you have no choice, it seems to me, but to plan for the worst-case.

> 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 ...

I never said it was "perfect", or ideal, or brilliant, or that it didn't give too hoots about the amount of undo segment space. Merely that it is the way of the future, and you'd better get used to it. You've been saying the same about ASSM to me for a while now ;-)

You mention extent stealing, and that's a lovely example of an optimisation Oracle brought in with automatic undo that is clearly designed to minimise the waste of space. It's not a great surprise to me that 9i handles the matter rather more cleverly than 8i could do. Great.

Now, back to the question: what do you do in 8i, when you don't have automatic undo, and you don't have control over where your mixed workload's undo ends up?

>
> 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.

You strike your balance where you can find it. If backup is an issue, then fine, strike it on the optimal side of the equation, and be prepared for the issues that arise when you wrap.

Me, I'd still prefer to recognise that the entire situation is a compromise, and that the regularity of the big jobs combined with the small ones means you'd be better off sizing for the big ones. You also have manual re-sizing options when you don't use optimal, and for me that definitely tips the balance over away from using optimal. This isn't a question of never being able to shrink things to save on space (and hence on your I/Os), but merely a question of when the shrink should happen, and whether you want it happening automatically, with all the penalties that brings with it.

> 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 ....
>

Again, all I'd say is that the original poster isn't doing rare or unusual big loads, but has a thoroughly mixed workload where some are big and some are small, and he has no control over the mix or how they're handled. Yes, there's a backup penalty. Yes, there's also a possible I/O penalty. But there's no 1555 penalty, and there's no continual wrap-and-wait penalty. And he is still able to take control over size issues if those I/O issues are getting out of control.

Pick the side of the see-saw you want to sit on.

Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------