Re: db block size, too big wasting buffer?

"sg" <s4v4g3_at_europe.com> wrote in message news:3D175E5F.6090202_at_europe.com...
> Hi
>
> I am about to create a OLTP database of 16KB in 8.1.7, I have always
> read that dont make the db block size too big or we would be wasting
> data buffer. Is this true?
> Some say that db block size is another Oracle Myth, it does not really
> matter, is this a correct statement?
>

No, it's absolutely INcorrect. The block size matters very, very much. What *is* a myth is arguing that the block size depends on the type of application you are using (the myth continues as follows: 'OLTP use small blocks to avoid contention; Data Warehouse use big blocks to cheapen full scans').

It would have helped to know your operating systen, since the truth is actually that the correct block size for a database depends entirely on the operating system it's running on. AIX and Linux - 4K. Most other Unixes - 8K.

The reason is that on these systems, assuming a file system is being used, there's a file system buffer, and the block size needs to match that exactly.

But also on these systems, you have the option of using raw devices. And raw devices don't have a file system, and therefore no file system buffer to worry about. So you can choose something that suits you. Likewise on Windows, which uses direct i/o, you can choose.

But it remains sensible to choose larger block sizes, because of the dramatic difference to index usage it can make. Therefore, I always use 16K blocks on Windows, and I'd be tempted to do the same on a raw Unix.

Does it waste space in the buffer cache? Not really, unless all your tables are just 4K or 8K in size!! If you have 100 4K buffers in use, or 50 8K buffer, you still in either case have 4M of data being cached. But, yes, cache management does need to be looked at (as do issues like contention for big buffers)... but these issues are all resolvable with flexible techniques that can change as the need arises. You pick a small block size to deal with them, and you're stuck with that decision for the life of the database.

So, to sum up: yes, size does matter, whatever you may here. And as has been said through the ages, bigger is better. But you *must* match your file system buffer size exactly, if you've got one.

Regards
HJR Received on Mon Jun 24 2002 - 14:40:29 CDT