Re: formulas for correct sizing of various parameters

"David Wilburn" <dwilburn_at_mitre.org> wrote in message news:3B8D37BC.5B72B7FB_at_mitre.org...
> I've got plenty of books and documents that all say that specifying
> correct values for initial/next/max extents, db_block_size, etc., are
> all critically important. However, I've yet to find any formulas
> specifying how to come up with the correct values.
>
> I've read that (at least of solaris) the block size should be some
> multiple of the output of the command 'pagesize' (in this case, 8KB),
> but I'm not sure whether it should be 8KB, 16KB, etc. Which should it
> be?

On Solaris, you want 8K blocks. See www.ixora.com.au, and search for an article called 'Why a Large Database Block Size' for the reason why.

>
> How do I calculate appropriate values for initial/next/max extents?
>

You don't really! Well, OK... here goes. You don't need to worry about it too much *if* you are using locally managed tablespace. Have a variety of such tablespaces, with uniform extent sizes of, say, 8K, 16K, 64K, 128K, 512K, 1M, and 10M. House each segment in whatever you think is the appropriate tablespace, based on what you anticipate will be the eventual size of the segment, and aiming to keep the number of extents down to a sensible figure (say, 500 or so).

If you get your estimates wrong, use the 'alter table blah move tablespace X' command to transport the segment into a more appropriate tablespace.

Of course, all the above assumes you are using 8i. If you are not, or if you are using dictionary managed tablespaces, then it gets rather trickier, because a huge number of extents can be a problem. In that case, you still want a set of tablespaces wherein everything comes in the same extent size, and you'd be aiming to keep the number of extents any one segment acquires to below 504 (for an 8K block size). Because the data dictionary is used to record extent allocations and de-allocations, you'd be better aiming for around half a dozen extents per segment, and 504 should be considered a hard upper limit. In practice, the number of extents is unlimited, but exceeding 504 would mean the extent map would be in two blocks, not one -and that can induce performance penalties.

Regards
HJR
> We're getting a new system in shortly, and I'll have the opportunity to
> rebuild our DB, so I'd like to get things right the first time.
>
> -Dave Wilburn
Received on Wed Aug 29 2001 - 15:00:59 CDT