jump to navigation

The CBO and Indexes: OPTIMIZER_INDEX_COST_ADJ Part III August 20, 2009

Posted by Richard Foote in Index Access Path, OPTIMIZER_INDEX_COST_ADJ, Oracle Indexes.

After a bit of a layoff to organise a few upcoming overseas trips, while watching plenty of Ashes Cricket and the brilliantly funny “Flight Of The Conchords” DVDs, it’s about time I got back to my humble little blog.

In Part II, we looked at a really bad way to set the optimizer_index_cost_adj parameter, by just setting it a really low value and allow indexes to blindly reign supreme in the database.

Remember, the purpose of the optimizer_index_cost_adj parameter is to accurately reflect differences and discrepancies in costs associated with single block I/Os when compared with corresponding multi-block I/Os so that the CBO considers and incorporates these discrepancies in its costings. 

A second method of setting the optimizer_index_cost_adj parameter is to set it to a value that attempts to accurately reflect these comparative costs. So if a single block I/O is typically only half as expensive and/or only takes half the time to complete when compared to a multi-block I/O, then a reasonable setting for the optimizer_index_cost_adj parameter would be 50.
So how to set the optimizer_index_cost_adj parameter “intelligently” ?
Well, Oracle has excellent instrumentation and the comparative wait times for each of these types of I/Os are automatically measured and captured by Oracle. A single block I/O as performed typically by an index range scan is measured via the “db file sequential read” wait event while the multi-block I/O as typically performed during a FTS is measured via the “db file scattered read” wait event. 

By determining the average wait times for each of these events and comparing the differences, one can determine how much longer it takes on average for one type of I/O to complete versus the other. This will then provide us with a reasonable starting point with which to set the optimizer_index_cost_adj parameter.
One can simply look at these average wait events for the database since startup by querying v$system_event:
SQL> select event, average_wait from v$system_event where event like ‘db file s%read’;

EVENT                   AVERAGE_WAIT
----------------------- ------------
db file sequential read          .59
db file scattered read           .78

In order to determine these wait events during a specific time period to perhaps better reflect typical loads during these times, one could also simply run a statspack or an AWR report and look at the wait event section of the report.

So in the above example, a “sequential” read only takes approximately 75% of the time when compared to a “scattered” read. As such, a value of 75 would be an appropriate starting value with which to set the optimizer_index_cost_adj parameter.
With the I/O costing model, the CBO is basing it’s costs on the number of I/Os performed by each possible access path. If an index is only going to take 75% of the time to perform it’s associated I/Os when compared to the time it takes to typical perform I/Os during a FTS, it’s reasonable to adjust the associated costs of an index access down to 75% of its overall costs.
This will hopefully have the desired effect of making it a “level playing field” between an index based access path and a FTS when determining how long all the I/Os associated with each possible execution path might take.

If we plug a value of 75 into the optimizer_index_cost_adj parameter and re-run the demo in Part Iwhere the CBO initially choose the more expensive FTS which had a cost of 65:

SQL> alter session set optimizer_index_cost_adj=75;
Session altered.
SQL> select * from bowie_stuff2 where id in (20, 30, 40, 50, 60);
10000 rows selected.

Execution Plan
Plan hash value: 2964430066
| Id  | Operation                    | Name           | Rows  | Bytes | Cost  |
|   0 | SELECT STATEMENT             |                | 10000 |   175K|    60 |
|   1 |  INLIST ITERATOR             |                |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2   | 10000 |   175K|    60 |
|*  3 |    INDEX RANGE SCAN       | BOWIE_STUFF2_I | 10000 |       |    27 |

We note the CBO is now choosing to use the index, which is the more appropriate plan as it provides a somewhat faster response that the previous FTS.
However, if we also re-run the demo from Part IIwith the optimizer_index_cost_adj also set to 75, where previously Oracle initially choose to use a FTS quite correctly:

SQL> alter session set optimizer_index_cost_adj=75;
Session altered.
SQL> select * from bowie where id between 1 and 1000;
1000873 rows selected.

Execution Plan
Plan hash value: 1845943507
| Id  | Operation         | Name  | Rows  | Bytes | Cost  |
|   0 | SELECT STATEMENT  |       |  1000K|    69M| 16499 |
|*  1 |  TABLE ACCESS FULL| BOWIE |  1000K|    69M| 16499 |

We note that the FTS is still selected as the change in the CBO index related costs were not significant enough to change the execution plan. A really low value of 2 for the optimizer_index_cost_adj parameter really stuffed things up previously, but a more appropriate value of 75 in this database has ensured that the FTS is still chosen when appropriate.
So in both scenarios, the CBO is now choosing an appropriate execution plan. By setting the optimizer_index_cost_adj parameter in a logical manner, consistent with the relative wait time differences between single and mutli-block I/Os, the CBO is more likely to choose appropriate execution plans. 

Of course, there are always likely to be some discrepancies when dealing with such “averages”. We only have the one parameter after all which impacts the costs of all index range scan access paths, so we can only deal with averages. Perhaps there are some specific indexes which take significantly more (or less) time to complete than the average, as their associated I/Os are impacted by where the blocks might physically sit on the disk arrays, or on contention issues due to other concurrent activity, or on index caching characteristics (Note: I’ll discuss the optimizing_index_caching parameter at another time), etc. etc.
Same for some specific FTS which have multi-block I/Os that take significantly less (or more) time to complete than the average, as it’s associated I/Os might be also be impacted by similar factors. Perhaps some of these I/O characteristics and timings might change depending on the load on the system at different times of the day or week or month.

But that’s what an “average” value means right, some objects will have a higher (or slower) value while some have a lower (or faster) value.
So setting the optimizer_index_cost_adj parameter is not a precise science although of course the CBO in general is not a precise science either and close enough is usually good enough for the vast majority of cases. The name of the game is ensuring that the parameter is set to a value that’s in the “ballpark” and using the associated wait events to determine comparative wait times for single and multi-block I/Os is a reasonable way to do this.

However, despite being able to set the optimizer_index_cost_adj parameter in a reasonably “intelligent” manner, my preferred method of setting this parameter is still method number 3. That is to simply not set the optimizer_index_cost_adj parameter at all and leave it at the default value of 100 and use system statistics and the CBO CPU costing model instead.
By generating and maintaining accurate system statistics, you can effectively get the desired “level playing field” benefits of a well tuned optimizer_index_cost_adj parameter in a somewhat easier manner but with a few other added benefits as well. I would therefore strongly recommend the use and implementation of system statistics and leave the optimizer_index_cost_adj parameter well alone. IMHO, the optimizer_index_cost_adj parameter is there now only for backward compatibility reasons since the introduction of the CBO CPU costing model.
However, these discussions have not all been in vain because the optimizer_index_cost_adj parameter still has an impact even with system statistics in place. It’s just that the use of the optimizer_index_cost_adj parameter in conjunction with system statistics typically has the effect of screwing up the “level playing field” environment system statistics is meant to create.
Also, the costing formulas for indexes as previously discussed are still very much relevant as the CPU costing model often has little impact on the actual costs associated with using indexes. As I’ll discuss later, system statistics actually achieves a very similar outcome to the optimizer_index_cost_adj parameter. It’s just that it does so in a somewhat different manner by generally increasing the associated FTS costings to a more appropriate comparative value, rather than simply decreasing the index related costs, while taking both I/O and CPU overheads into consideration.


1. Mark Brady - August 22, 2009

So the answer to the average_wait query on one system I tested was:

event average_wait
db file sequential read 0.05
db file scattered read 0.11

Since scattered is more than double, it would seem that some adjustment would be appropriate?

Richard Foote - August 22, 2009

Hi Mark

Potentially yes.

However, such low read times suggests that caching is being performed at the disk level which might mean other factors need to be looked at as well (eg. memory management, are blocks being flushed from the database buffer cache prematurely, etc.).

If one sets this parameter, it’s but a step of the whole tuning process, one best set once most else has been tuned first.

2. Entradas de Oracle semana 34 « Gruñidos sobre Oracle y SAP - August 24, 2009

[…] Richard Foote nos trae la tercera entrega sobre el significado del parámetro OPTIMIZER_INDEX_COST_ADJ. En SAP hay una recomendaciones fijas de las que no es conveniente salirse, de todas formas es […]

3. Brian Tkatch - August 26, 2009

Thanx Richard.

4. Blogroll Report 14/08/2009 – 21/08/2009 « Coskan’s Approach to Oracle - September 8, 2009

[…] 15-How to adjust optimizer_index_cost_adj if you really want to use it ? Richard Foote – The CBO and Indexes: OPTIMIZER_INDEX_COST_ADJ Part III […]

5. Rahul - October 20, 2009


After gathering system statistics I got some mysterious behaviour of Oracle with Disk IO and CPU usages.

6. Richard Foote - October 25, 2009

Hi Rahul

Care to elaborate ?

7. Faulty Quotes 1 – OPTIMIZER_INDEX_COST_ADJ « Charles Hooper's Oracle Notes - December 6, 2009
8. Andy - April 13, 2010

Found a while paper, first published in 2006, updated in 2009 in Metalink that targets Siebel CRM on 10g ( ID 743170.1). Somewhere in the middle of the paper, it reads:

For Siebel Product to work effectively and efficiently, the following two parameters need to be set to values ….

optimizer_index_cost_adj = 1

Hmm… those developers at Siebel must have loved indexes very much ;)

Richard Foote - April 14, 2010

Hi Andy

And people wonder why these large Apps often run so poorly and are so difficult to address as one must do what the vendor says one must do.

Makes you wonder how their large batch jobs run ?

Andy - April 14, 2010

I can hear those indexes screaming “Pick me! Pick me!” :))

9. Fuzzypig - July 16, 2010

Given that Siebel schemas have no referential contraints between the tables, I think Siebel admins have bigger things to worry about! LOL!

Brilliant trio of articles, many thanks indeed.

Richard Foote - July 20, 2010

Hi Fuzzypig

I think I might just have to keep avoiding Siebel applications if I can :)

Glad you liked the articles.

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 )

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


Get every new post delivered to your Inbox.

Join 2,158 other followers

%d bloggers like this: