I'm designing a new Oracle 11g database to run on Solaris 10. While researching the database configuration on Oracle's web site I came across the following statement with regard to database block size:
A larger data block size provides greater efficiency in disk and memory I/O (access and storage of data). Therefore, consider specifying a block size larger than your operating system block size if the following conditions exist:
Oracle Database is on a large computer system with a large amount of memory and fast disk drives. For example, databases controlled by mainframe computers with vast hardware resources typically use a data block size of 4K or greater.
The operating system that runs Oracle Database uses a small operating system block size. For example, if the operating system block size is 1K and the default data block size matches this, the database may be performing an excessive amount of disk I/O during normal operation. For best performance in this case, a database block should consist of multiple operating system blocks.
In the past I've usually stuck with an 8K block size (which I think is the default), but after reading the Oracle documentation, noting that my Solaris OS also has an 8K block size, and the server I'm installing on is pretty decent (512Gb of RAM, 4CPU * 12 cores @ 2.4GHz, fast disk arrays, NAS, and SAN) I'm wondering whether I should depart from tradition and go for a 16K block size instead. The only thing that makes me think otherwise is the average row size for the schema tables (which are pretty much LOB free) does come in well under 8K, leaving room for block headers and the like.
So if there is likely to be limited row chaining, is a 16K block size going to provide an efficiency dividend, or is it just going to waste a lot of space?
Any thoughts on the subject would be appreciated.
Don't waste your time worrying about block size. Oracle documentation is usually high quality but that section looks like it hasn't been updated in a few decades.
There seems to be a consensus among Oracle experts that changing the block size is not helpful. I've never seen a specific test case showing performance gains by changing the block size. But I have seen errors caused by using non-default block sizes.
Unless you have a very specific reason, and a lot of time to test it, your time is better spent worrying about something else.