Re: db block size, too big wasting buffer?

I think you have to be careful about statements like the one below - especially since it comes complete with a justification that seems to be quite reasonable.

Consider the point in more detail, though, and it may turn out to be appropriate only to particular scenarios.

32K blocksize -
Assume a (fairly generous) key size of 40 bytes, add in the row overheads of a few bytes; allow 25% of free space for a world-weary and well-used index; and you still get about 550 leaf entries to a block.

If you NEED a range scan of 550 entries, you probably then need to do 550 block visits to table blocks.

Either you have a special case (in which event the choice of block size may a minor worry); or any performance problems you have are unrelated to choice of block size.

The weight of the possible arguments changes with Oracle features, of course - e.g. big blocks for IOTs sounds as if it could be good, a useful number of rows without an overflow section. (I'll get around to thinking that one through some time).

Big blocks for indexes sounds like a redundant, or even bad, idea for pre-9.0 as big index blocks could result in significant latch contention on a few critical leaf blocks. (Not to mention increasing the probability of buffer busy waits).

I won't say that your idea is wrong. But I don't think it's a good idea to announce it without some detailed justification, or a reference to some public document that describes the (explicit and implicit) assumptions made about the working environment.

Regards

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

Don Burleson wrote in message
<998d28f7.0206301512.411700f9_at_posting.google.com>...


>

>1 - The number of index accesses - Oracle index range scans love large

>blocksizes, and on my 9i systems, I place indexes in a 32k tablespace.

>