jump to navigation

Larger Block Index Tablespace and Small Index Scans – Performance Improvement ? (Let Down) March 31, 2008

Posted by Richard Foote in Index Block Size, Oracle General, Oracle Indexes, Oracle Myths, Performance Tuning, Tablespace Management.

Thought it might be worth looking at the impact on the performance of Unique and small index range scans as typical in OLTP environments, when an index is rebuilt in a larger block tablespace.

Warning, this discussion will again primarily be an exercise in simple mathematics. However, a few little details to start the ball rolling.

I’ve already discussed how in many scenarios, by increasing the index block size, the height of the index can remain unchanged. One can’t simply assume a larger block index results in an index with a lesser height. However, as we shall see, even when indexes do reduce in height, the so-called performance benefits can be somewhat “exaggerated”.

Note also in OLTP environments, the vast majority of index related access paths are either unique scans or small index scans. We’re only interested in a specific customer, in a specific order, in the bank balances of specific accounts, with specific customers buying specific products with specific credit cards, etc. Remember also that say a block size of 8K can generally store many hundreds of index entries per index leaf block and that even a humble little 2K leaf block can often store a three figure number of index entries …

So let’s say in our first example we currently have an 8K index with a height of 3 which we rebuild in a 16K tablespace and the index height remains the same. What are the comparative costs of performing a simple index look-up.

In the 8K index, we need to read 3 x 8K (root, branch and leaf block) to read the index entry of interest for a total of 24K. However, in the new 16K index, we now need to read 3 x 16K (again, a root, branch and index leaf block) to read the index entry of interest, for a total of 48K.

That’s double the potential physical I/O if the blocks are not currently cached, that’s double the memory that needs to be used in the buffer cache to store the index blocks and that’s potentially more CPU we need to use in order to pin and process the larger index blocks.

Hummm, these larger blocks aren’t too impressive so far …

But what if we actually reduce the height after moving an index to a larger block tablespace ?

In the 8K index, we still have our 3 x 8K = 24K. If we rebuild in an 16K block tablespace and reduce it’s height by 1, we now only need to read 2 x 16K = 32K. That’s 3 consistent reads at 24K vs. 2 consistent reads at 32K. Yes, we reduce the number of consistent reads which is a good thing but we still have a larger index footprint which again means possibly more data off disk, more memory and more CPU resources to process the index blocks.

Let’s hope our 2K block index with a height of 2 reduces its height somewhat if we decide to rebuild it in 32K blocks because that’s 2 x 2K = 4K vs 2 x 32K = 64K otherwise. But even if we did, although we reduce the consistent reads, which is a good thing, that’s still 2 x 2K = 4K vs 1 x 32K = 32K.

But we can potentially reduce the height of a 2K index if rebuild in a much larger block size (in specific cases) by more than 1 level, right ?

Yes, a 4 level 2K index, 4 x 2K = 8K, might very well now only require 2 levels in a 32K index, 2 x 32K = 64K. But that’s still 64K of index data we need to access vs. 8K in the smaller block. Consistent reads reduce but our footprint for small index range scans is still larger, sometimes by a considerably amount depending on the change of block size.

You begin to see the issue …

It’s a bit like someone saying they’re going to improve the skyline somewhat and only build appartment buildings that have fewer numbers of floors than previously. But if the floors they build are 2 times or 4 times or even 16 times higher than the previous floors and they only reduce the number of floors by a moderate amount, is the building really lower ?

Have we really reduced the height of the building ?

Yes, the lift only has to stop at fewer floors which can be more “efficient” but it takes longer each time as it goes from one floor to the next. So is it really that much quicker to get to the top, or more specifically in our discussion, from the top to the bottom of the building ?

Surely though, things must run faster, performance must actually improve, things must be more efficient by having larger sized index blocks else why bother ? Why indeed …

This demo on the Performance Impact Of Small Index Scans In A Larger Index Block Size shows how performance may actually worsen, not improve if we move indexes into a larger block size. It creates an index on a well clustered column (so the index is at it’s most efficient and potentially impacts performance the most), first with an 8K block size and then with a 16K block size. The size of the index was carefully chosen so that the larger index block size did indeed reduce the height of the index (although the range of values when this was possible was actually quite limited with these block sizes). A simple PL/SQL procedure then performs a massive number of single row look-ups and the CPU and elapsed times are monitored. The results show when the associated blocks are either cached or not cached, the larger block index incurs larger CPU related costs and results in overall slower response times, despite the fact it actually has a lower height than the smaller block index.

It’s not precisely the same as a large scale environment as PL/SQL has subtle little differences and efficiencies when compared to multiple, separate transactions. Also, it’s not the specific results that are important here but the overall general approach. You can pick any index you want to  investigate, you can pick whatever block sizes you may be interested in, you can monitor and benchmark by checking out specific session details or by tracing the specific sessions, you can determine what the comparative costs and response times may be and you can determine what may cause any performance differences by digging deeper into the session statistics or trace files.

No matter what index or block size you select, you will likely come to the same conclusion. The overall performance benefits of your OLTP transactions when you move indexes to a larger block tablespace will likely be “disappointing”.

If a real estate developer comes knocking on your door with a promise that the empty block around the corner will have an apartment building with only a few floors and it shouldn’t ruin the view too much, you may just want to ask them quietly exactly how many floors they’re planning to build and exactly how high each floor will be.

Else the resulting view may turn out more disappointing than you’re led to believe 😉


1. Christian Antognini - March 31, 2008

Hi Richard

A remark about the demo…

On page 2 the difference of physical reads should be 4440 (21903-17463) and not 400.



2. Richard Foote - March 31, 2008

Hi Christian

Fixed. Many thanks !!


3. Brian Tkatch - April 2, 2008

Richard, thanx for the demo. It really drove the point home.

…even if it was a PDF file…


4. Richard Foote - April 3, 2008

Hi Briam

Unfortunately, wordpress no longer allows me to upload text files 😦


5. Chris - April 10, 2008

Hi Richard,

I was just on my way to convert some 8K index tablespaces to 16K, but luckily found your article and have now decided against it!

Well written, and thanks for avoiding ‘disappointment’!


6. Richard Foote - April 10, 2008

Hi Chris

Spend your energies instead on tuning a few queries here or there 🙂


7. Marcin Przepiorowski - May 12, 2008

I made some test on my test database using production data and results was as expected – different database block give me worst response time. I’ve publish it on my blog. Now I’m thinking about this same scenario but using different default block size, ex. default 16 kB, default 32 kB and then compare a results. Did you ever try this ?


8. Richard Foote - May 12, 2008

Hi Marcin

Good on ya !! I’ll study your results tomorrow when I have some time.

The question of what block size to set the database is entirely different to whether or not to use multiblock tablespaces. Yes, I’ve tested and played with different database block sizes but it’s not just a question of simple performance for a specific activity, one needs to consider issues such as concurrency and how the OS handles and deals with different I/O sizes.

It’s something on my (somewhat long) list of things to discuss one day.


9. Chris Godfrey - June 30, 2009

Just stumbled upon this excellent guide, very helpful!


Richard Foote - June 30, 2009

Thanks Chris.


10. rainer stenzel - June 18, 2014

Still an excellent blog, raising this question for me:
Is Oracle using some kind of binary search to look up the (first) wanted key inside one index block ? Otherwise a simple linear search would most often probably take more (CPU) time for larger index block sizes.


11. Jonathan Lewis - June 25, 2014


Many years ago I had a conversation with an Oracle employee who mentioned that the code did a binary chop through the index leaf block (indirectly through the row directory, of course) until there were no more than 3 consecutive entries that had not been checked. I doubt if the mechanism has changed.


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: