Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: db block size, too big wasting buffer?

Re: db block size, too big wasting buffer?

From: Jonathan Lewis <>
Date: Mon, 1 Jul 2002 10:18:18 +0100
Message-ID: <>

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.


Jonathan Lewis

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November

Don Burleson wrote in message

>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.
Received on Mon Jul 01 2002 - 04:18:18 CDT

Original text of this message