The CBO and Indexes: An Introduction (Absolute Beginners) June 9, 2009

One of the more common questions I get asked and one of the most common questions asked in the various Oracle related forums is the general question of why doesn’t the CBO choose to use a particular index. There are various reasons why an index might be ignored but generally and rather simplistically (for now), the answer is often simply because the CBO considers the cost of using the index to be more expensive than other alternatives.

As one would expect with the CBO, the cost is a rather important consideration and yet many don’t understand what the costs associated with an execution plan actually means and represents. Some people think the costs associated with an execution plan are basically meaningless and are only used by the CBO in “mysterious ways”. Some people even go so far as to suggest that the CBO sucks and recommend all sorts of inappropriate ways to force Oracle to use an index.

It all comes back to simply not understanding CBO fundamentals and how Oracle costs the basic use of an index.

In reality of course, the CBO doesn’t “suck” with 10g, in fact it’s actually extremely clever and resourceful when it comes to determining appropriate, efficient execution plans. If the CBO is armed with “accurate enough” statistics, it will generally do a remarkably good job. The costs and the associated cardinality of the various steps within an execution plan provide valuable information on why the CBO has made its decisions.

So I thought I might discuss the CBO a little and start with a very basic introduction into how the CBO costs an index to get this message across.

Note there are various slight variations and interpretations of this formula from database version to database version and for differing specific scenarios, which can vary the resultant costs marginally. All the following examples are from a windows based 10.2.0.3 database.

So from an index perspective, the index blevel, the number of leaf blocks and the clustering factor are all statistics that directly influence the cost of using the index. However, just as important are the associated selectivities of accessing both the index and the table. These statistics are often based on the associated column statistics (but not always) and if Oracle gets these selectivities wrong, then the CBO will generate wrong costings and all bets are off regarding the appropriateness of the resultant execution plan.

OK, let’s create a nice, simple little scenario which will be the basis of future demos.

We’ll use the BOWIE_STUFF table in future demos, but for now I’m after a relatively large index with an excellent clustering factor. So I’ll create a second table, ordered on the ID column and double it’s size …

The indexed ID column has 100 distinct values, which are perfectly evenly distributed. The density of the ID is an “accurate” 0.01 with a selectivity of 1%, so a selection of one distinct value will return 200,000 x 0.01 = 2,000 rows. Nice easy numbers …

The index has a blevel of 2, it has 605 leaf blocks and a rather good clustering factor of just 854 (note the rows in the table were basically inserted in ID order).

OK, we now have all the information we need.

Note to start with, I’ll only use the “older” IO costing model by setting the following parameter:

SQL> alter session set "_optimizer_cost_model" = io;
Session altered.

As we’ll see in future posts, the use of the CPU costing model actually has minimal effect on many index related access costings but we’ll use the IO costing model as a starting point.

This first equality SQL demo shows the costings in relation to a simple, single value equality predicate (I’ve linked to a PDF to preserve formatting).

OK, 2 important pieces of information to note. Firstly, the CBO has got the expected cardinality (2000 rows) spot on.

As the stats are 100% accurate and the values are perfectly evenly distributed, this is to be expected. If Oracle gets the cardinality correct, we can be reasonably confident that the CBO to have made a reasonable decision here.

As previously mentioned, there are 100 distinct values of which we want one of those those values. Oracle has calculated the selectivity of the query to be 1/100 = 1% or a value of 0.01 (as defined in the density column statistic). The expected number of rows is therefore 200,000 x 0.01 = 2000. Like I said, spot on.

Secondly, the CBO has calculated the cost of the index range scan to be 9 and the overall cost of the query to be 18. How has the CBO derived these costings ? Well as it’s a simple, single column index and associated query predicate, we can plug in the 0.01 selectivity into the above formula as follows:

In this second IN list demo, we’re just going to expand on things a touch by selecting 3 distinct values in a IN list. However, the actual principles and cost calculations are basically the same.

This time we’re after 3 possible values as listed in the IN clause. So that’s 3 out of the 100 possible values, 3/100 = 3% or 0.03. The required cardinality is therefore 200,000 rows x 0.03 = 6000 which again Oracle has got spot on. Usually a good sign that the CBO has made a reasonable decision.

This time the costs have come to 23 for the index range scan part of the execution plan and 49 overall. Again using our basic formula:

So the cost associated with the index is indeed 23 and the overall cost of the query is indeed 49. Again, all these CBO costings make sense. These costings aren’t meaningless, internal numbers but values that give us a useful insight into how and why the CBO has come to a specific cost and so to a specific execution plan.

Note BTW, one of those subtle differences in how the formula is implemented with 10g (from say 9i) is the fact the selectivity is calculated for the index by performing the CEIL function for one value first and then multiplied by the overall number of expected values in the IN list:

2 + 3 x ceil(0.01 x 605) = 23

rather than determining and using the total index selectivity within the CEIL function:

2 + ceil(0.03 x 605) = 21

The net result being such costs are going to be just that little bit higher in 10g than they would have been in 9i.

So indeed, these things can all change a little from release to release but the basic principle is still the same. Feed the CBO acurate enough statistics and it’ll likely do the right thing. If it doesn’t, understanding the CBO and how it costs various execution plans will help to determine what the issues might be. Huge clues are provided by the costings and cardinality estimates in the associated query execution plans.

1) This is a little table I use as a basis for number of different basic demos and it helps to show the effects of bounded and unbounded range scans if the data doesn’t contain all possible integers between 2 values. Depends what I finally discuss here whether or not it comes into play.

2), 3). Yes indeed, fixed thanks.

4). It’s a great book, useful for beginners and more experienced folk alike. I learnt lots of useful bits of information.

I think it is really good that you are going back and covering the basics again – not that I don’t like the advanced stuff a hell of a lot too, but covering the basics (a) helps people who don’t know it yet (b) reminds those of use who thought we knew it about it but might have fogotten {i’m getting old} (c) looking at the basics you can realise that something you thought you knew for years… you didn’t. I recently did a simple presentation on explain plan and realised I did not really know what a db block get was….I worked it now, it’s on my own blog.

I have one question. I would have expected the “in” example to result in 3 seperate index interogations which fed back to the table scan, as each value in the “in” would need a new starting point in the index to be found and thus 3 walks down the index depth and a scan for each. As such I would have expected to need 3*(B level +ceil(index selectivty*leaf blocks)) + ceil(table selectivty*clustering factor). But your numbers add up and mine would not (though index selectivity*leaf blocks is 6.02, veeery close to 6… OK, maths does not work like that) .
How does Oracle avoid working down the depth of the index 3 times?

That’s a good question which I’ll expand upon later. The blevel is actually a bit of a variable in the formula and how it’s interpreted between releases and between differing queries and between differing index sizes.

The thing to note is that if the index is accessed a lot, there’s a good chance the non-leaf blocks are going to be cached as they only take up a small proportion of the overall index size. The CBO doesn’t like to include what it thinks might be cached blocks as the cost is meant to determine the overall I/Os required for the execution plan.

If an execution plan has just worked down the index structure, the CBO assumes if it does so again within the same execution plan, there’s a very very good chance those blocks will already be cached and so doesn’t need to include the non-leaf blocks again in its costings.

It’s an automatic use of the optimizer_index_caching parameter if you like, but only for non-leaf blocks. This parameter is used to make a similar assumption for the index leaf blocks as well.

I always believed that cost is some meaningless, internal number. Now you are going to break my dreams 🙂
In my practice I had optimized some queries, so query’s cost was increased but execution time decreased from minutes to seconds.
It means statistics or multiblock_read_count or some else parameters were wrong in that database?

Remember, the CBO doesn’t deal strickly with response times, it deals with “costs”, either I/O or I/O and CPU related. Lower costs should ideally mean lower resources and so low response times but not always, especially if the fundamental cost calculations are incorrect.

Generally in the scenario you describe, one either has incorrect cost calculations due (usually) to incorrect cardinality estimates due (usually) to imprecise enough statistics or the CBO is making incorrect assumptions regarding the relative costs associated with the various types of I/Os.

Again, understanding how this number is derived can help in determining why some of these things may be occuring with your execution plans. If a higher cost plan is “better”, you want to know why so that the CBO can ideally make the necessary corrections.

[…] back to basics, Richard Foote explains Oracle’s cost-based optimizer in CBO and Indexes, an Introduction for Absolute Beginners. Speaking of optimizations, Valcora has Another Way To Do Performance Tuning — make sure you […]

It shows 8192 as the value. Also, my Clustering factor for the BOWIE_STUFF2_I index is 857, not the 854 shown in your article. That’s much closer than the block count, but I’m left wondering why the differences.

Our Oracle installation is on a Linux system, if that makes a difference, but I am not a DBA so I don’t have the exact operating system and version of Oracle available to quote..

The differences in blocks allocated can be attributed to different tablespace definitions. The demo used what was more current then, that being uniform extent size with manual segment space management, whereas you likely used auto extent sizes and auto segment space management, which results in more allocated blocks below segment high water mark.

If you want to test this, create a tablespace with say “extent management local uniform size 1m segment space management manual” and see what object sizes you then get.