Oracle Scratchpad

October 28, 2008

IOTs and blocksize

Filed under: Block Size,Infrastructure,IOT,Oracle,Performance,Tuning — Jonathan Lewis @ 7:17 pm BST Oct 28,2008

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.”


  1. “If someone has to ask what block size they need. The answer is always 8KB.”

    This reminds me a bit of “Any customer can have a car painted any colour that he wants so long as it is black.” (Henry Ford). :-)

    See also Richard’s excellent series about block sizes.

    Comment by Robert Klemme — October 29, 2008 @ 1:13 pm BST Oct 29,2008 | Reply

  2. not directly related, but be careful of IOT’s. They can provide a big performance boost, but one cost is that the rest of the oracle platform does not always play well with them. For example, Datapump and transportable tablespaces both have known bugs wrt IOT’s, to the point that to xfer an IOT table with DP you have to basically ctas into a heap, export, then rebuild. Considering that generally people go to IOT with big datasets this is a major impediment to their utility.

    The datapump issue is this; you are using IOT with row overflow, and export wants to export entire rows together, but as it does not use cache it ends up asking for the same block from disk again and again and again to get the overflow section of each row. If you have 100 rows/page, you end up with 101 physical reads! Turning on o/s caching helps this problem, but that is supposed to be a no-no with the whole “double cacheing” debate.

    Comment by Alonso — October 29, 2008 @ 8:57 pm BST Oct 29,2008 | Reply

  3. Alonso,
    thanks for this information. Do you also have the metalink bug numbers available?


    Comment by Wolfgang — October 29, 2008 @ 9:26 pm BST Oct 29,2008 | Reply

  4. Alonso,

    Thank you for that comment.
    I wonder if anyone else has come across the same problem and found an alternative workaround, or damage limitation mechanism.

    Is the behaviour the same for both 10g and 11g ?

    Comment by Jonathan Lewis — October 30, 2008 @ 7:59 am BST Oct 30,2008 | Reply

  5. [...] organized tables, of course. Searching back through my blog I find that I’ve only written one article about IOTs- although I’m very keen on taking advantage of them, and have made a few references to them [...]

    Pingback by IOTs « Oracle Scratchpad — November 22, 2011 @ 9:51 am BST Nov 22,2011 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at


Get every new post delivered to your Inbox.

Join 3,507 other followers