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

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.

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.

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 ;)

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

– 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?

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

“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 ;)

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:

– 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).

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.

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.

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 :)

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

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 …

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.