jump to navigation

Store Indexes In a Larger Block Tablespace: Some Thoughts (Big Brother) March 16, 2008

Posted by Richard Foote in Index Block Size, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Myths, Performance Tuning, Richard's Musings, Tablespace Management.

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 …


1. Syed Jaffar Hussain - March 16, 2008

It is really interesting see if some one out there using this scenario and looking forward see their comments.



2. Sidhu - March 17, 2008

Interesting…looking forward to the discussions 🙂


3. Tony - March 17, 2008

Should be a great series of articles…looking forward to it.


4. Donald K. Burleson - March 17, 2008

>> As we shall see …

Just don’t forget to menion that a decision to move an index to a larger blocksize depends on “how” the index is accessed. Indexes on the “probe” side of a nested loop (index unique access by ROWID) don;t benefit, and the amount of the reduction on I/O depends on the exient of multi-block reads (index FFS has the largest measurable benefit, in my experience).

FYI, for my clients, I correlate SQL usage (from stats$sql_plan or dba_hist_sql_plan) before trying it in TEST, and I use a real-world workload to ensure valid results.

I’m hoping that you will do a reproduceable bench on this, and you know some non-anon experts who have witnessed this reduction (the 6% reduction reported by David Aldridge, Andy Kerber, etc.)

Oh, and don’t forget to mention that, like everything in Oracle, multipole blocksizes are not for everyone, and perhaps characterize those databases where it makes sense.


5. Richard Foote - March 18, 2008

Don, I’m disappointed 😦 I’ve already explained to you previously why Fast Full Index Scans are unlikely to make any difference in this OTN thread. As did Greg Rahn, what didn’t you understand ?

Don, how is it you see the “largest measurable benefit” when Oracle performs the same sized I/Os regardless of the block size? Please explain. And for these clients of yours, how do you actually “test” these benefits you see.

Don, just for you, I’ve picked multiblock reads as the first myth to discuss with regard storing indexes in a larger block size. Oh, and please run the demo across any index you wish, on any platform, on any version of Oracle to see how Oracle always performs the same sized I/Os regardless of the block size when performing a multiblock read.

So unless you dramatically reduce the overall size of your indexes, I would love to see benchmarks that shows “large measurable benefits”.

Don, do you charge for a full days work (say 8 hours) at these clients at a 1/2 hourly or an hourly rate 😉


6. Donald K. Burleson - March 18, 2008

>> what didn’t you understand ?

I understand your arguments, but you, my friend, may miss the point. There is EVIDENCE of improved performance, that’s what counts.

To cite “proofs” that it’s does not work in a contrived test case DOES NOT negate real-world evidence, it just makes your methodology appear to be invalid!

David Aldridge has shown a 6% performance improvement, which to a busy database can be huge.

Also, beware of tossing-around terms like “huge”. In my world, small marginal improvements can be amplified Consider the impact of a 5% reducntion for this system, 89,000 LIO’s per second:


>> Don, do you charge for a full days work (say 8 hours) at these clients at a 1/2 hourly or an hourly rate

There is more than enough empirical evidence that it DOES speed up some shops, and while nobody is claiming it as a panacea, you should focus on WHY ir does work under certain conditions. Nobody argues that it’s for everyone, and it;s easy to “disprove” it.

It’s more challenging to reproduce it!

Also, it would be great to get Metalink Note:46757.1 updated:

– Large blocks gives more data transfer per I/O call.

– Larger blocksizes provides less fragmentation (row chaining and row migration) of large objects (LOB, BLOB, CLOB)

– Indexes like big blocks because index height can be lower and more space exists within the index branch nodes.

– Moving indexes to a larger blocksize saves disk space. Oracle says “you will conserve about 4% of data storage (4GB on every 100GB) for every large index in your database by moving from a 2KB database block size to an 8KB database block size.”

Don;t take this wrong, by and large I agree with you, but the RWEALLY interesting part are the cases where it does help. Can you reprocuce David;s test, showing a 6% reduction?


“In terms of scattered read difference, the 16kb scan completed in 6% less time than the 8kb.”

Richatrd, anybody can easily show a case where it does not work, the real challenge is to show the true cases, pick them apart and explain WHY it works. . . .

I’ll bet that you can do it, if you keep an open mind anbout the issue . . . .

Also, don’t hesitate to contact Aldrige for details. He is a very nice fellow . . . .


7. Donald K. Burleson - March 18, 2008

>> please run the demo across any index you wish, on any platform, on any version of Oracle to see how Oracle always performs the same sized I/Os regardless of the block size when performing a multiblock read.

I believe you on this point. Unfortunately your “demo” does not explain the contradictory evidence.

Why? is your “demo” invalid because there is some other mechanism going on?

Now, that’s the real question.

Until you can explain why so many credible people see an improvement, it’s premature to call it a myth, especially since MetaLink endorses the approach. Convince MetaLink (with a reproduceable benchmark, not a “demo”), and you will have achieved your goal. . . .

In the absense of an explaination for the contrary cases, you may be spreading the myth that “blocksizes never matter to performance. and I’m sure that you don’t want to be a myth-spreader. . . .


8. Richard Foote - March 18, 2008

Hi Don

You might want to look at David’s example again.

I think David summed it up all quite well at the end when he said:

“Unless I’ve done something wrong here, I don’t see much advantage in the large block size. I could see those percentage differences being attributable just to the physical location of the segments on the disks and background noise, no? ”

Exactly !!

In fact, if he looked at the actual waits in the trace file, he would have noticed that the I/O being performed were virtually identical, the differences being the blocks were in a physically different location on the disk and the index in his case was approximately 1% smaller.

Don, what exactly is invalid about my “methodology” ? Do you not agree that multiblock I/O sizes are identical regardless of the block size?

How therefore does the “amount of the reduction on I/O depends on the exient of multi-block reads”, your quote remember ?

The simple fact is that the reduction in I/Os has nothing to do with the extent of multiblock reads …

And what about your test cases and your experiences. Rather than quoting other people, what are these “large measurable benefits” of yours, share some of your experiences. You’re the one claiming it works not me, please show us a “true” test case.

Don’t worry too much about block sizes and index height, I’ll discuss that particular myth in the not too distant future 😉


9. Richard Foote - March 19, 2008

Hey Don,

Is this David Aldridge, this “very nice fellow”, who you’ve mentioned a number of times as being someone who has “real world EVIDENCE” regarding “improved performance”, the very same David Aldridge who you later banned from your forum a few days later because he actually disagreed with your conclusions regarding the benefits of placing indexes in a separate larger block tablespace, in the very same forum thread you linked in your comments:


Is this the best example you can come up with of someone supporting your claims, is this is what you consider “evidence that counts” …

Cheers 😉


10. Donald K. Burleson - March 19, 2008

Hi Richard,

>> the very same David Aldridge who you later banned from your forum

The same. I don’t hold grudges.

>> “Unless I’ve done something wrong here, I don’t see much advantage in the large block size.

Yes, I never said that it was a hige difference, and I agree that it’s not significanbt for many shops, only a small fraction of high-volume OLTP systems.

>> And what about your test cases and your experiences.

Same as you! Virtually NONE of my clients would agree to have their details dosclosed, that’s why I cite others. I have many clients where I cannot even disclose that they use Oracle!

>> Don, what exactly is invalid about my “methodology” ?

It’s largely valid, but IMHO, you are approach it bass-ackwards. It’s easy to show a negative case. Here, I have noted credible source who CLAIM different response times with different blocksizes:


– They have no reason to lie, so the first step is to accept their observations and explore what’s up.

– I would focus on overall response time (set timing on” first, and then attempt to replicate it. Then, you are in a position to show why it’s happenning.

– I would not assume that it’s due to LIO, at this point. You can hypothesize it, but keep an open mind!

Finally, remember that in VLDB, mutiple blocksizes also make more efficient use of RAM. For example, a very large tables that grabs 80 byte rows makes better buffer use in a 2k blocksize (assuming, of course, a standard distribution of popular/unpopular rows).

Good luck!


11. Brian Tkatch - March 19, 2008


There are some cases where things just work, and it is decided to use it, even if everything says otherwise. Been there, done that, so, i can appreciate your point of view.

Here, however, Richard is not letting know what he did, what should work, and in a sense, not even what does work. He is explaining basic fundamentals in Oracle INDEXes.

My understanding of his method of explanation is to find a specific article of interest (either based on common sense or something he can challenge us with), and whittle it down to its most basic form. Then show exactly what Oracle does with it, in repeatable cases. In a sense, this is a hands-on forum. Personally, i have tested a few of the cases he provided, and gained much from it.

There is a basic flaw in this methodology, a flaw that the scientific method shares. That is, it rules out anything which is not definable or repeatable. If it cannot be defined, a test cannot be defined. If it cannot be repeated, it cannot be tested.

While this rules out a significant amount of material, it allows the scientific method to be used. I think it is quite apropos for databases, even if it misses on some real-world applications (which might benefit more from chaos theory, or the like).

Your approach, however, seems to be quite the opposite. You are more interested in what works, with a moderate amount of the why which can then be applied in a future case. No doubt, many people agree with you, and that is why you have such a popular website. Indeed, that is why people feel the urge to comment, even if they disagree with you.

Your original comment in this thread, “Just don’t forget to menion…” is asking Richard to put something in his blog, which he cannot test or repeat. No case is given (for the reasons you cited). So, even though you have referenced the cases where it does work, it, unfortunately, would go against the basic thrust of this blog to post about them.

I’d like to suggest, that if you feel Richard is not providing advice for practical cases, and you feel that they should be pointed out, do what he does. Post an article on your site, linking to this site, and use it as a starter for an informative article, to which you can add your real-world experience.

I suggest this because i feel the readership of both site would find that format to be more beneficial.


12. Donald K. Burleson - March 20, 2008

Hi Brian,

>> He is explaining basic fundamentals in Oracle INDEXes.

Oh? I thought that he was trying to explain the phenomenon of reports of performance differences between different blocksizes?

>> While this rules out a significant amount of material, it allows the scientific method to be used.

Yes. To my knowledge, I’m one of the few Oracle people who has actually conducted and published real scientific research (in academia), and parts of Richard’s approach mystify my common sense.

He correctly notes that people claim dofferent response times, that’s the assertion. If I were investigating this, I would focus on replicating the observations, not show some isolated case where it does not hold true.

Yes, and I have not yet seen any end-to-end timings. The research is about counterintuitive reports of differing response time with different block sizes, right?

>> Indeed, that is why people feel the urge to comment, even if they disagree with you.

Debate is good, so long as people treat each other with respect.


13. Richard Foote - March 20, 2008

Hi Don

My point is that David didn’t actually believe the performance improvement of storing the index in a larger block size could necessarily be attributed to the larger block. It could have been the result of any number of different things.

Also, David’s example wasn’t “real world” as you would put it. It was a little experiment, a test to play and learn. Which is fantastic.

My other point is where is your research, where are your tests and results. If you truey believe moving indexes in a larger block tablespace improves things, simply show us.

Answer this basic question. Why does an Index Fast Full Scan display the “largest measurable benefit”, what benefit are we talking about ?

And what environment has so many Index Fast Full scans that it makes moving indexes into a larger block tablespace worthwhile ?

In answer to your question on why folk might experience performance benefits after moving to a larger block tablespace, I thought the answer deserving of a Blog entry on its own 🙂


14. Richard Foote - March 20, 2008

Hi Don

>> I would focus on replicating the observations, not show some isolated case where it does not hold true.

I’m not showing folks an isolated case. I’ve clearly stated please run the demo using any index you like on any database on any platform on any version of Oracle.

How can you possibly suggest that’s an isolated case, it doesn’t get any more general and global than that ?

The size of your multiblock reads will always be the same. As Jonathan has stated, depending on what the Index Fast Full Scan is doing, you might find not only elapsed times but just as importantly, CPU times increasing as a result.


15. Richard Foote - March 20, 2008

Hi Brian

The big big big danger of simply looking at something that “works”, without some appreciation of “why” that something works, means you can be “lucky” and apply a solution that was only indirectly responsible for a solution.

Next time you might not be so lucky.

For example, you have a performance problem with an index and you rebuild it in a 16K tablespace. Performance improves significantly.

Some time later, you have another problem with an index, so remembering how you fixed things previously, you rebuild it in a 16K tablespace as well.

This time though, things run even slower than before.

Wouldn’t it be nice to know now “why” things improved the first time …

Cheers 😉


16. Brian Tkatch - March 25, 2008

Hey, Richard, i do read your blog after all. 🙂

I can just appreciate the other position as well, regardless of whether i agree with it. And, i would find it silly to have to people disagree over a point, when each’s discipline of choice proves their method to be best.


17. Richard Foote - March 26, 2008

Hi Brian

I know you do 😉

Wish the same could be said for all my “visitors” …


Leave a Reply

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: