Store Indexes In a Larger Block Tablespace: Some Thoughts (Big Brother)

A suggestion that seems to pop up on a routine basis on various forums and discussion boards is that we should be storing our Indexes in a larger block-size tablespace. For example, if our database block size is set to 8K, we should be creating separate (say) 16K block tablespaces specifically for our indexes. Doing so will improve performance as the index will have a flatter, more efficient structure. Multiblock reads will also be more efficient (or so the theory goes) as we would be reading fewer index blocks during such scans.

Oracle introduced the concept of having different tablespaces in a database with different block sizes back in 9i Release 1 in order to make transportable tablespaces between databases with differing block sizes possible. However, there’s nothing preventing one creating a new tablespace with a non-default block size and assigning objects to these tablespaces.

In principle, storing indexes (in particular) in a larger block size sounds like a really good idea doesn’t it ?

I’ll be discussing the pros and cons of this approach in future postings but just some initial thoughts to get everyone thinking about it:

• All tablespaces with a non-default block size requires a separate, non-default block size buffer cache to be manually configured
• Non-Default buffer caches are not automatically sized as part of Oracle’s automatic memory management and must be manually tuned and sized, potentially increasing administrative overheads
• Non-Default buffer caches do not have an associated KEEP or RECYCLED pool and so all objects with the same non-default block size must reside in the same buffer cache
• The possibility of unnecessarily caching blocks from an infrequently accessed object and wasting memory is therefore likely to increase
• The possibility of unnecessarily aging out blocks from a more frequently accessed object is also likely to increase, thus increasing I/O related overheads
• Although the height of an index may reduce if stored in a larger block size, in many cases it may not actually change at all
• In those cases when the height of an index is actually reduced, the actual performance benefit of such a height reduction is often overstated
• The reduction of index leaf blocks (a much more telling possible advantage) is only beneficial to very specific types of queries
• Larger blocks often have the disadvantage of greater contention, which can lead to performance related issues
• Indexes with larger block sizes have a significantly greater I/O and memory related footprint in relation to most OLTP related index scans
• Index related multiblock reads on larger block sized segments actually have no real benefit when compared to multiblock reads on smaller block sized segments 
• Most databases out there in the “real world” only use default block size tablespaces so the risks associated with finding bugs, CBO anomalies, etc. increase once non-default block sizes are introduced

Although in specific scenarios with specific applications, there may be some potential benefits of using non-default blocks sizes, in general, the disadvantages of using non-default block sizes usually out weigh these potential benefits.

As we shall see …