Re: Database Block Size Question

How do you determine the best Oracle block size for an environment?

I can not give a concrete answer, but a 4k block has half the fixed overhead of a 2k block, and an 8k block reduces the fixed overhead in a larger database to 1/4 the size used by 2k blocks. You do need to consider adjusting the inittrans and maxtrans parameters when using these larger sizes.

The main cost of 4k, 8k, and larger block sizes is that the redo logs and the rollback segments are affected. Increasing the redo log sizes is easy and can be done while the system is available to users. Providing usable space to rollback segments depends on fragmentation of the tablespace and how much spare space you have available, but using 4k blocks instead of 2k block does not require doubling the rollback area.

Some people will argue that larger block sizes reduce the number of random data blocks that you can hold in the buffer pool. This is true, but each block holds more data reducing the need to read another block in many cases. Sequential I/O benefits greatly from having larger blocks usually exceeding the cost of having less random blocks.

If you have a 2k database, rather than worry about the exact right size, just move up one notch to 4k. We are planning on using 8k on our future databases. We have 2k and 4k databases and we have seen improvements in the applications on all databases that we have increased in block size.   

Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Sun Aug 03 1997 - 00:00:00 CDT