jump to navigation

The CBO and Indexes: OPTIMIZER_INDEX_COST_ADJ Part I July 8, 2009

Posted by Richard Foote in CBO, Index Access Path, OPTIMIZER_INDEX_COST_ADJ, Oracle Cost Based Optimizer, Oracle Indexes.
trackback

In the previous entry regarding The CBO and Indexes, we saw how the CBO with a query that selected 5 distinct values in an IN list, representing 5% of the data, decided to use a FTS because the costs of doing so were less than that of using a corresponding index. These costs (using the I/O costing model) represented the number of expected I/Os and the FTS was basically going to perform fewer I/Os than the index. Less I/Os, less cost and so the FTS was selected as the preferred access path.
 
However, by default, the CBO when determining these costs via the I/O costing model makes two very important assumptions which may not necessarily be true.
 
Assumption one is that all I/Os are likely to be “physical I/Os” which all need to be costed and taken into account.
 
Assumption two is that all I/Os are costed equally, even though the size of a multiblock I/O performed typically during a FTS is larger and so potentially more costly than a single block I/O usually associated with an index access.
 
Today, I’m only going to focus on this second assumption. 

Now, when performing and processing data from a multiblock I/O as performed during a FTS operation, it’s typical for such operations to be more resource intensive than that of a single block I/O as performed during an index range scan, as the associated overheads are likely be greater such as having to read more actual data off the disk, having to transfer more data into the SGA, having to process more data in each associated block, etc.
 
Therefore, not all I/Os are equal. However, by default the CBO ignores all these possible differences and costs all I/Os associated with a FTS (multiblock) and an index (single block) as being equivalent or the same.
 
Now, this hardly seems fair or indeed accurate and desirable when determining the true cost differences between an index and a FTS. Shouldn’t the fact that a single block I/O is likely to be less resource intensive and take less elapsed time to process be taken into consideration when determining these relative costs ? 

Enter the optimizer_index_cost_adj parameter.
 
The purpose of this parameter is simply to “adjust” the corresponding costs associated with an index to (hopefully) more accurately reflect the relative I/O costs between using an index and a FTS. If for example a single block I/O only takes 1/2 the time and resources to perform compared to a multiblock I/O, shouldn’t these associated I/O cost differences be reflected when determining whether or not to use an index and perhaps reduce the index related costs by 1/2 as a result ?
 
This parameter has a very simple impact on how the CBO costs the use of an index based access path. It takes the value of the optimizer_index_cost_adj as a percentage and adjusts the cost of an index related range scan access path to only be the corresponding percentage of the total index cost. By default, it has a value of 100 meaning that a single block I/O is 100% when compared to that of a multiblock I/O which in turn means that the index related I/O costs are treated the same as that of a multiblock FTS I/O. A default value of 100 therefore has no effect on the overall cost of using an index related access path.
 
However, if the optimizer_index_cost_adj only has a value of (say) 25, it means that all single block I/O are only 25% as costly as that of a multiblock I/O and so index related range scan costs are adjusted to be only 25% of that of the total index access path cost.
 
Going back to the previous demo where the FTS was selected, I calculated the cost of using the index when retrieving the 5% of data to be:

index blevel + ceil(index selectivity x leaf blocks) + ceil(table selectivity x clustering factor)

2 + 5 x ceil(0.01 x 602) + ceil(0.05 x 854) = 2 + 5 x 7 + 43 = 37 + 43 = 80.
 
The cost of using a FTS was calculated as being only 65. A cost of 65 for the FTS is less than a cost of 80 for the index and so the FTS was selected.

This time, the linked demo sets the optimizer_index_cost_adj = 25 before running the exact same query again.

We notice of couple of key differences. The first obvious difference is that the plan has changed and that the CBO has now decided to use the index. The second difference is the associated cost relating to the use of the index. Previously, it was calculated as being 80 but now it only has a cost of 20. The maths is pretty simple as with an optimizer_index_cost_adj = 25, we need only mutliply the previous total with 0.25:

(2 + 5 x ceil(0.01 x 602) + ceil(0.05 x 854)) x 0.25 = (2 + 5 x 7 + 43) x 0.25 = 80 x 0.25 = 20.

Note also that just the index range scan cost component was previously 2 + 5 x ceil(0.01 x 602) = 37, but is now also adjusted to 37 x 0.25 which rounds to 9.

Basically by setting the optimizer_index_cost_adj = 25, we have effectively reduced the overall cost of using the index based execution path down from 80 to just 20, to just 25% of the previous total index cost.
 
The cost of the FTS remains unchanged at 65. The index access path at just 20 is now less than the FTS alternative and so the index is now chosen by the CBO.

Yes, all these numbers and costs make sense when one understands how the CBO performs its calculations and the effect of setting the optimizer_index_cost_adj parameter to a non-default value.

The  optimizer_index_cost_adj parameter can therefore obviously have a very significant impact in the behaviour and subsequent performance of the database as the CBO will reduce (or maybe increase) the actual costs of index related access paths by the percentage denoted in the optimizer_index_cost_adj parameter. It can potentially dramatically increase (or decrease) the likelihood of an index access path being chosen over a FTS.
 
There are typically 3 very different ways in which this parameter is set, which I’ll list in increasing order of preference.
 
1) Set it arbitrarily to a very low figure such that indexes reign supreme as their associated costs get adjusted to such a low figure by the CBO that a FTS access path has little chance of being chosen (for example, here’s a suggestion to set it to a magical value of 12). Generally a very bad thing to do in any database …
 
2) Set it to a value that the DBA determines is an approximate percentage of the costs associated with a single block I/O when compared to a multiblock I/O. An improvement of option 1), but I still prefer the next option 3) …
 
3) Leave it at the default value of 100 such that it has no impact and the CBO does not use it to adjust the cost of an index access path

 

I’ll explain in Part II a sensible approach in setting the optimizer_index_cost_adj parameter and why option 3 is the preferred option with any currently supported version of Oracle.

About these ads

Comments»

1. Narendra - July 8, 2009

Let me guess. Is the option 3 most preferred because of (not mentioned) system statistics collection?…:)

Richard Foote - July 9, 2009

Hi Narendra

Indeed. All examples thus far have deliberately used the IO costing model. Chat about system stats and the CPU costing model to come. Note though that the impact in index costs is generally minimal although the same might not be the case for the FTS …

2. Brian Tkatch - July 8, 2009

Hmm, why doesn’t Oracle do a simple test to see the difference in speed. Disregarding override settings, isn’t this discernible via a simple test?

Richard Foote - July 9, 2009

Hi Brian

That’s what system stats are designed, in part, to do.

However, the test isn’t necessarily so simple. Remember you potentially have many many tables and many many indexes, living on all different tablespaces and parts of your file systems, with all sorts of different loads at different times of the day or week.

Therefore, in a “simple” test, how do you ensure that the cost of a particular single and multiblock I/O is really indicative of your system ? What if that particular index or table lives on a slower disk or on a slower part of a disk. What if during the test, the disk was particularly hot but not so during most of the day. What if a particular table is flushed frequently and remains in storage cache but not so other I/Os. Etc.Etc.

Therefore, you need to average these things out to get a “typical”, “average”, “general” value of the relative costs of these sorts of I/Os.

And that’s the problem with a “one value for the entire system”, generic value for these sorts of things. Hopefully, it’ll be close enough for most scenarios so any test or measurement needs to be global and general enough to not be unduly impacted by any specific issue.

Brian Tkatch - July 22, 2009

I see. So in order to be accurate, it would need to be an ongoing test, averaging over a period of time. Similar to line conditioning performed by some DSL/Cable modems to find appropriate window size and whatnot.

3. Steve Winter - July 9, 2009

Since you prefer option 3 over option 1, doesn’t that make the list ordered in increasing order of preference?

Richard Foote - July 9, 2009

Hi Steve

I’ve possibly had too many beers but I had to read it a number of times until I came to the final conclusion that you’re probably right.

So I’ve changed it and blame you if it no longer reads right ;)

4. Blogroll Report 03/07/2009 – 10/07/2006 « Coskan’s Approach to Oracle - July 11, 2009

[...] Richard Foote – The CBO and Indexes optimizer_index_cost_adj Part I [...]

5. Matt - July 18, 2009

Hello Richard,

Thanks for sharing all the information on this blog, I only discovered it recently and have been making my way through your previous entries – very interesting reading.

When’s part II coming of this one – I’m interested in it already…

Thanks,
Matt

Richard Foote - July 20, 2009

Hi Matt

No worries.

Part II is coming, it’s just the DKB news articles are sooo funny that I just had to write about them.

Also, school holidays, the Ashes cricket and the Tour De France means my evenings/nights are not a free as they might ordinarily be.

But Part II is coming I promise :)

6. Richard Foote - July 23, 2009

Hi Brian

That’s right. It’s not going to be a perfect scenario, it can’t be when you have just one set of global values but the greater the sample, the more accurate the “average” , indicative result.

However of course, when you take these measurements are important as well as I/O characteristics may change as the database load and processes change. So systems stats taken at night (during batch processing loads) might not be as indicative as when they’re taken during the day (during more OLTP processing loads).

So you might need different system stats for different database processing loads.

7. Hans-Peter Sloot - August 28, 2009

Hi Richard,

I am still a bit surprised to see that the formula wit the optimizer_index_cost_adj is
(2 + 5 x ceil(0.01 x 602) + ceil(0.05 x 854)) x 0.25
and not
(2 + 5 x ceil(0.01 x 602)* x 0.25 + ceil(0.05 x 854))
making only the index part cheaper.

regards Hans-Peter

Hans-Peter Sloot - August 28, 2009

Hi Richard,

I would like to show that this is in fact a wrong way to calculate the cost.

CREATE TABLE table1 AS SELECT ‘Y’ flag, object_name FROM dba_objects WHERE rownum ‘DBA_DOE’
,TabName => ‘TABLE1′
,Estimate_Percent => NULL
,Method_Opt => ‘FOR ALL INDEXED COLUMNS SIZE AUTO ‘
,Degree => NULL
,Cascade => TRUE
,No_Invalidate => FALSE);
END;
/
PL/SQL procedure successfully completed.

explain plan for
SELect *
FRom table1 t
where t.flag = ‘Y’
;

Explained

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 963482612

—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————-
| 0 | SELECT STATEMENT | | 50000 | 1318K| 63 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TABLE1 | 50000 | 1318K| 63 (2)| 00:00:01 |
—————————————————————————-

Predicate Information (identified by operation id):
—————————————————

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – filter(“T”.”FLAG”=’Y’)

alter session set optimizer_index_cost_adj = 10;

Session altered

explain plan for
SELect *
FRom table1 t
where t.flag = ‘Y’
;

Explained.

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
————————————————————————————————————————
Plan hash value: 2359840706

——————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————-
| 0 | SELECT STATEMENT | | 50000 | 1318K| 31 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TABLE1 | 50000 | 1318K| 31 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TABLE1_IDXA | 50000 | | 9 (0)| 00:00:01 |
——————————————————————————————-

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
————————————————————————————————————————
—————————————————

2 – access(“T”.”FLAG”=’Y’)

Because not only the index part was made cheaper the optimizer now chooses and index range scan which is infact silly because all the rows are retrieved and so all the table blocks are accessed anyway.

Autotrace proves that

For optimizer_index_cost_adj = 10
Statistics
———————————————————-
0 recursive calls
0 db block gets
6964 consistent gets
0 physical reads
0 redo size
1393400 bytes sent via SQL*Net to client
37083 bytes received via SQL*Net from client
3335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50000 rows processed

For optimizer_index_cost_adj 100

Statistics
———————————————————-
0 recursive calls
0 db block gets
3542 consistent gets
0 physical reads
0 redo size
1393400 bytes sent via SQL*Net to client
37083 bytes received via SQL*Net from client
3335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50000 rows processed

Regards Hans-Peter

Richard Foote - August 30, 2009

Hi Hans-Peter

I disagree. I think it’s quite a good way to calculate the cost but you have given a good example of what feeding the CBO bad information can do.

A few key points to your example.

Firstly, the index obviously has a really good clustering factor as the total cost of your second plan with the index is just 31 which would have been around 310 with the full costings in place. A cost of 310 to read a 50,000 row table means the CF is excellent and the index would likely only have to read each block 1 or 2 times at most.

Next point is that the effective db_file_multiblock_read_count must be quite low. The FTS has a cost of 63 which means it’s only reading about 800 rows or so per I/O which in turns means it’s not reading too many blocks per multiblock I/O, I estimate about 8-9 blocks.

Thirdly, you have set a really really low optimizer_index_cost_adj to just 10.

The problem here is not that the way the CBO is costing things is wrong, but that when you have an index with a really good CF AND a db_file_multiblock_read_count that is relatively low AND a likely inaccurate optimizer_index_cost_adj that is way under-estimating the relative costs of a single block I/O vs. these smallish multiblock I/Os, the CBO is going to make the wrong decision.

If by reading the table with a FTS the CBO is going to use 63 I/Os that are 10 times more expensive than the 310 I/Os used by the index plan, then the CBO is going to think it quicker to use the index.

It’s that simple, the CBO doesn’t have the smarts to say but wait, we’re reading the whole table with methods anyways.

The fix is to set perhaps a larger and more effective db_file_multiblock_read_count AND to set the optimizer_index_cost_adj parameter to a more suitable value (or leave both values alone and let system stats and the defaults look after themselves).

Then the CBO will calculate the more appropriate costs with the formulas.

Richard Foote - August 29, 2009

Hi Hans-Peter

The second formula is basically the formula for the optimizer_index_caching parameter, which I’ll discuss in my next blog post. It truly only impacts I/Os associated with just the index part of the execution path as it only considers the caching of index related blocks.

However the I/Os associated with accessing the table blocks when using an index path are all also single block I/Os, that’s why all the associated I/Os costs are adjusted for the optimizer_index_cost_adj parameter. It therefore adjusts the costs of the index access path, not just the I/Os associated with index blocks.

The parameter could possibly be called the optimizer_singleblock_io_cost_adj and it would perhaps make more sense :)

Hans-Peter Sloot - August 31, 2009

Yes optimizer_singleblock_io_cost_adj would be more appropriate.

Regards Hans-Peter

8. Faulty Quotes 1 – OPTIMIZER_INDEX_COST_ADJ « Charles Hooper's Oracle Notes - December 6, 2009

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

Follow

Get every new post delivered to your Inbox.

Join 1,853 other followers

%d bloggers like this: