Re: How to calculate the optimal size for undo segments

On Thu, 06 Mar 2003 09:36:39 +0000, Yvonne wrote:

> es anybody know how to calculate the optimal size for undo segments
> ( oracle9 i)

Er, the whole point of 9i's "undo" management is that it's automated, and you don't concern yourself with the size of undo segments. Oracle does it for you. Your only job is to decide on a size for the undo tablespace in which these segments will be automatically placed.

Basically, there is a cost for going beserk over the undo tablespace size: Oracle's transaction algorithm is: "New transaction? New undo segment!". Until a mysterious point is reached where Oracle decides it has enough undo segments, and starts sharing them between multiple transactions. Each undo segment means an undo segment header, which needs to be cached in the buffer cache. The bigger the undo tablespace, the further you push the decision to start sharing undo segments into the future: if there is an infinite amount of undo tablespace, Oracle simply doesn't start sharing at all. Meaning that if you opt for an enormous undo tablespace, you end up with a bazillion undo segments, and their segment header blocks start flooding your buffer cache.

So there's a balance to be struck between oodles of undo space and a working buffer cache.

In general, what you do is: find out how much undo per second you are generating (see v$undostat, and the undoblk column. Each line in that view is a ten minute window, except for the first. You want the line with worst possible (ie, biggest possible) number). If it says on one line you generated 1000 blocks of undo in 10 minutes, and assuming an 8K block size, then you've generated 8M of undo in 600 seconds, or 13K per second (my maths might be wonky!). Now: how long do you want to retain this undo for. To avoid ORA-1555s, you want to retain ndo for at least as long as your longest transaction. So look in v$undostat and find the MAXQUERYLEN column. If the worst casr there is 1200 seconds, then you need (worst case) 1200 x 13K = 16000K of undo tablespace. Add on a little for overhead, and make it 20MB.

Of course: you can't measure these things until you've already got an undo tablespace, so what you are supposed to do is to create a massive tablespace that you reckon will contain the worst case you can possible imagine, and let your users do their thing.... that populates v$undostat, and you take it from there, sizing the tablespace down or up as the case may be.

Regards
HJR   Received on Fri Mar 07 2003 - 06:16:25 CST