Database Block Size – does it really matter?

What block size should you use? For what purpose? How about tablespaces in different block sizes? Any opinions?

When support for multiple block sizes was introduced, I was working for Oracle Uni and did have some (very restricted) access to Product Development. It seemed to me that an obvious use case for this was tuning. I was thinking of things like putting LOB segments and IOT overflow segments in large blocks while keeping the base table in small blocks. Product Development was most emphatic: “Don’t try to do that”. They wouldn’t give any reason (they never do) but there was a hint that the buffer cache management algorithms for non-default block size pools are not optimized for normal work; I have no idea if that is, or was, true. They pretty much said that the only reason for multiple block size support was to allow tablespace transport between DBs with different block sizes. Of course there was nothing said that can be quoted, and I have no idea if the situation has changed since.

So if one accepts that all tablespaces should use the db_block_size, what size should this be? I have never seen any justification for the advice about “small blocks for OLTP, large blocks for DW” that has been in the docs for decades. It sounds right instinctively, but that is all. Virtually all the DBs I see use 8KB or 16KB, and I have no opinion on whether one performs better than the other for any purpose. Some people produce algorithms based on block size and db_file_multiblock_read_count, trying to relate the IO size to the RAID stripe or the ASM Allocation Unit, but again I have never seen any proof of this having any effect.

For a long time, I thought that 16KB blocks were more convenient than 8KB because it meant that I could have datafiles up to 64GB. But now that I always use bigfile tablespaces, that reason no longer holds.

With regard to the buffer cache, I now follow the principle that it is best to have one big default buffer pool: do not try to segment it with different block sizes or keep and recycle pools. The only interference a DBA should consider doing is setting the db_big_table_cache_percent_target, which I think can really help when you have a mixed workload. Otherwise, let Uncle Oracle get on with it: he can manage the cache better than me.

So my conclusion is that in the twentyfirst century, all DBs should use the 8KB default block size, and the cache should be one default pool. However, I would love to see some science behind this, or behind any other opnions.