A question came up on the Oracle database forum a few months ago asking:
What are the benefits and the downside of using IOTs on 16k blocks? Would you recommend it?
I think the best response to the generic question about block sizing came from Greg Rahn in another thread on the forum:
If someone has to ask what block size they need. The answer is always 8KB.***
For someone running their database through a filesystem cache, I would probably modify that from “8KB” to “the same as the filesystem block size” – but the principle is the same. Before you ask someone what your block size should be, you ought to be able to give a concrete reason why you think there might be a benefit in using anything other than the default.
If you don’t know why your specific application might benefit from a non-standard block size then you don’t know enough about your application to make it efficient anyway – and choosing the “perfect” block size is a long way down the list when it comes to methods for making an application efficient.
But when you ask about sizing blocks for IOTs (Index Organized Tables) you’re probably one step ahead in the thinking. If you’ve decided that your application would benefit from an IOT then you may have some extra information that makes it sensible to consider setting up a special cache with a different block size for that IOT.
The special feature of IOTs, of course, is that they are structured as unique B-tree indexes that “carry” their table data, so the index entries are likely to be relatively large – and this can exaggerate the effects of block splitting and space wastage that can appear with ordinary indexes.
Since IOT entries are likely to be larger than typical B-tree entries, you could argue that a larger block size might help to reduce the risk of ‘premature’ or ‘unlucky’ block splitting – which means less undo, and redo and a possible reduction in logical I/O when you do index range scans. (If you have secondary indexes, a reduction in block splits may also help to keep the number of correct “block guesses” at a higher level than it would otherwise be).
On the other hand, if you use a larger block size for the IOT, each block that splits will generate a larger amount of undo and redo – each block split generates redo equivalent to a little more than two data blocks. The total volume generated over any given period of time probably won’t change much, but the timing – pattern and duration – hence potential for contention, may change enough to cause problems.
Of course, if you’re thinking about IOTs you may also have some ideas about popular columns and overflow segments – so the index entries may not hold all the columns from the table (only the popular ones) and may not be all that large after all. This, of course, means you may treat the IOT just as you would any other unique index.
On top of everything else there’s always the thought that moving from a heap table to an IOT may be the feature that gives you such an I/O benefit that any tweaking that you then do to optimise the IOT is (a) relatively insignificant and (b) very hard to predict.
So, as a general guideline, my approach would be: if an IOT is a good idea (which means it offers a significant performance benefit) then assume you want to stick with the default block size unless:
- It’s extremely obvious that there’s further benefit in using a special block size or
- the IOT is going to be such a threat to the rest of the system that you want to get it into its own cache – in which case the recycle cache may be better than a different block size.
Footnote: the original thread that prompted this note includes a demonstration script that makes a case for the performance benefit for the larger block size – but there are flaws in the script which I highlighted in a response to the posting.
It is important to note that despite the flaws in his argument, the author of the test case deserves a commendation for doing the work to produce and publish the test. Publishing a flawed test case that can be criticized and corrected is as beneficial as publishing a perfect test case – in fact it may be even more helpful as it is often easier to learn by seeing errors than it is to learn by seeing nothing but perfection.
*** It used to be said of Rolls-Royce cars: “If you have to ask how much they cost you can’t afford to own one.”