The CPU Costing Model: A Few Thoughts Part I

In the coming days, I’ll post a series of little entries highlighting a specific point in relation to the use of system statistics and the CPU cost model. In my previous post, we looked at how the cost of a FTS is calculated using the CPU costing model and how this generally results in an increase in the associated FTS cost over the I/O costing model.

The table in my demo though had an index with an appalling clustering factor and even though the cost of the FTS increased substantially from 33 to 70, this cost was still significantly less than the large cost associated with using such an inefficient index. So in that specific example, the change of FTS costs as introduced with the CPU costing model made no difference to the final execution plan.
 
The key point I want to emphasise with this post,  is that by increasing FTS costs as is common with the CPU costing model over the I/O costing model, this can of course potentially result in entirely different execution plans, especially if a candidate index has a reasonable clustering factor. Substantially increasing the associated costs of a FTS can be very significant, especially where the difference in costs between a FTS and an index can be much narrower for well clustered indexes.
 
In this previous I/O Costing Model example using the BOWIE_STUFF2 table, the index had an excellent clustering factor. However the query resulted in a FTS as the cost of 65 was just a little less than using an associated index:

SQL> select * from bowie_stuff2 where id in (20, 30, 40, 50, 60);
10000 rows selected.

 
Execution Plan
———————————————————-
Plan hash value: 573616353
——————————————————————
| Id | Operation | Name | Rows | Bytes | Cost |
——————————————————————
| 0 | SELECT STATEMENT | | 10000 | 175K| 65 |
|* 1 | TABLE ACCESS FULL| BOWIE_STUFF2 | 10000 | 175K| 65 |
——————————————————————

Remember, this was “addressed” and the CBO started using the index, by manually adjusting the optimizer_index_cost_adj parameter from its default value to a value of 75 as explained in this previous post on the effects of the optimizer_index_cost_adj parameter.

However, with system stats and the use of the CPU costing model, the extra FTS cost can have a direct impact on the resultant execution plan. Running the same query again, but this time without changing any optimizer parameters and using the same system stats as in my last post on the CPU Costing Model:

PNAME    PVAL1
-------- -----
SREADTIM     5
MREADTIM    10
CPUSPEED  1745
MBRC        10

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 (%CPU)| Time     |
———————————————————————————————–
|   0 | SELECT STATEMENT             |                | 10000 |   175K|       69(2)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2   | 10000 |   175K|       69(2)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | BOWIE_STUFF2_I | 10000 |       |       25(0)| 00:00:01 |
———————————————————————————————–
 

We notice that the CBO has now chosen the index automatically, without having to make any changes to the optimizer_index_cost_adj parameter at all.
 
Previously, the FTS costs were 65. However, the current costs for a FTS are at least:
 
(ceil(blocks/mbrc) x mreadtime) / sreadtime = (ceil(659/10) x 10) / 5 = 132.
 
132 is already way greater than the 69 cost associated with using the above index and the 132 cost doesn’t even take into consideration any additional costs related to CPU usage.
 
So in general, using the CPU costing model will likely increase the associated costs of FTS, making indexes automatically more “attractive” to the CBO as a result. This change alone in how the FTS in particular is costed using the CPU costing model can have a major impact in execution plans chosen by the CBO. This is but one of the key reasons why things can change so dramatically when moving from 9i to 10g where the CPU costing model is the default.