The CBO CPU Costing Model: Indexes vs. Full Table Scans

As previously promised, I thought I might look at how the CBO goes about costing a Full Table Scan (FTS) with system statistics and the CPU costing model, so we can understand why the CBO may have chosen one option over the other.
 
WARNING: You might need to grab a calculator to help you along 🙂

To illustrate, I’m simply going to use the original BOWIE_STUFF table and index setup I created in my earlier Introduction to the CBO. I’ll however recreate the demo here again from scratch to refresh your memory:
 
I first create a table that has 100,000 rows, with an indexed “ID” column that has 100 distinct, evenly distributed values. For those mathematically challenged, this means each distinct value will return 1000 rows.
 

SQL> CREATE TABLE bowie_stuff AS SELECT (mod(rownum,100)+1)*10 id, 'Ziggy Stardust' name FROM dual CONNECT BY LEVEL <= 100000;
 
Table created.
 
SQL> CREATE INDEX bowie_stuff_i ON bowie_stuff(id);
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BOWIE_STUFF', cascade=> true, estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 1');
 
PL/SQL procedure successfully completed.
 
SQL> select blocks from dba_tables where table_name='BOWIE_STUFF';
  
BLOCKS
------
   329

Note the table has 329 blocks. It’s a number I’ll refer to a number of times throughout.
 

SQL> SELECT index_name, blevel, leaf_blocks, clustering_factor FROM user_indexes WHERE index_name = 'BOWIE_STUFF_I';
  

INDEX_NAME    BLEVEL
------------- ------
BOWIE_STUFF_I      1

LEAF_BLOCKS CLUSTERING_FACTOR
----------- -----------------
        207             32900

   
Note also that the index has a blevel of 1, 207 leaf blocks and a rather poor Clustering Factor (CF) of 32900, not close at all to the number of blocks in the table. As we’ll see, the CF is so bad that the CBO will choose a FTS over the index.
 

SQL> show parameter db_file_multi
   
NAME                          VALUE
----------------------------- -----
db_file_multiblock_read_count    16

 
   
Note the db_file_multiblock_read_count is manually set to 16. Relevant when calculating the cost of a FTS with the I/O costing model. Less so with CPU costing in use as we’ll see.

Finally, if we look at the system statistics in place:
 

SQL> select pname, pval1 from sys.aux_stats$ where pname in ('SREADTIM', 'MREADTIM', 'MBRC', 'CPUSPEED');

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

All of these values will be relevant when calculating the cost of a FTS with the CPU costing model.
 
OK, we now have all the information we need to determine how the CBO will treat both index and FTS activities on this table.
 
Let’s start by refreshing ourselves with how the I/O based CBO model will deal with such a scenario.
 

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

 
OK, let’s just run a simple query that selects data for a specific ID. Remember, there are 100 evenly distributed distinct IDs so this query will return 1% of the data (1000 rows):

SQL> set autotrace traceonly
SQL> SELECT * FROM bowie_stuff WHERE id = 420;
 
  
-----------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost  |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |  1000 | 18000 |       |
|*  1 |  TABLE ACCESS FULL| BOWIE_STUFF |  1000 | 18000 |       |
-----------------------------------------------------------------

 
 

Note: the CBO has decided to use a FTS to select the 1% of rows as it has the lowest associated cost.
 
As previously discussed, the cost of using the index is approximately:
 
index blevel + ceil(index selectivity x leaf blocks) + ceil(table selectivity x clustering factor)
 
 = 1 + (207 x 0.01) + (32900 x 0.01) = 1 + 3 + 329 = 333        

Note: the 1 for the blevel can be dropped by the CBO bringing the cost down to 332, to be discussed another time .
 
As previously discussed, the FTS cost is approximately:
 
segment header I/O + ceil(table blocks/fudged mbrc value) 

Note: for a db_file_multiblock_read_count of 16, the adjusted, “fudged” value used by the CBO is approximately 10.4.

Therefore, for the above example, the FTS cost is calculated as:
 
= 1 + ceil(329/10.4) = 1 + 32 = 33
 
33 is significantly less than 333 so the FTS easily wins out.
 

So how do things change when using System Statistics and the CPU costing model ? How does the CBO calculate the cost of the FTS with the above system statistics in place ?
 
As I’ve previously discussed, the significant change with the CPU costing model is not so much how it impacts the cost of index accesses but that of the FTS.
 
Let’s run the same SQL, but this time using the CPU costing model:
 

SQL> alter session set "_optimizer_cost_model" = cpu;
 
Session altered.
 
SQL> SELECT * FROM bowie_stuff WHERE id = 420;
 
1000 rows selected.
 

--------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |  1000 | 18000 |     70(5)  | 00:00:01|
|*  1 |  TABLE ACCESS FULL| BOWIE_STUFF |  1000 | 18000 |     70(5)  | 00:00:01|
--------------------------------------------------------------------------------

Note: CBO is still picking the FTS as the CF is truly awful. However the cost of the FTS has increased significantly from 33 to 70, although nowhere near the approximate 333 cost of using the index.
 
So why has the FTS cost increased and how is this new cost calculated ?
 
As previously discussed , the CPU costing formula is basically:
 
(sum of all the single block I/Os x average wait time for a single block I/O +
 sum of all the multiblock I/Os x average wait time for a multiblock I/O +
 sum of all the required CPU cycles / CPU cycles per second)
/
average wait time for a single block I/O
 

If we first focus on the single block I/O portion of the formula, the only single block read considered by the CBO during a FTS is the one associated with reading the segment header. Note that the average wait time for a single block read is the SREADTIM system statistic.
 
If there’s just the one single block I/O, the single block read portion of the formula effectively equates to (1 x sreadtim) / sreadtim, which just equals 1. So 1 is basically added to the cost with regard to reading the segment header as it is with the I/O costing model.
 
OK, lets next look at the portion of the formula with regard to multiblock I/Os.
 
The sum of all the multiblock I/Os is calculated in a similar manner as it was with the I/O costing model. It’s simply the number of blocks in the table below the HWM (329 in our example) but this time divided by the MBRC system statistic. Note however the MBRC statistic isn’t some fudged, somewhat arbitrarily set figure based on the db_file_multiblock_read_count parameter, but the actual average size of multiblock I/Os in the specific database environment. Note also that the average wait time for a multiblock read is the MREADTIM system statistic.
 
So the total wait time for all multiblock reads in the above example is:
 
sum of all the multiblock I/Os x average wait time for a multiblock I/O = (BLOCKS/MBRC) x MREADTIM = ceil(329/10) x 10 = 330.
 
This value is then divided by the average wait time for a single block read (the SREADTIM system statistic) to give the overall cost of multiblock reads, but expressed in units of single block I/Os.
 
The total cost for multiblock I/Os is therefore:

 ((BLOCKS/MBRC) x MREADTIM)/ SREADTIM = 330/5 = 66.
 
So the total costs associated for all I/Os is the 1 for reading the segment header plus 66 for all the multiblock reads = 67.
 
However, the cost of the FTS is 70, not 67. Where does the additional cost of 3 come from ?
 
Well, that’s the CPU portion of the formula. The CBO has determined that the FTS operation will require ‘x’ number of CPU cycles and this value is then divided by the CPUSPEED to determine how long this CPU activity will take.
 
This CPU elapsed figure is then again divided by the average wait of a single block read (SREADTIM) to also put the CPU costs in units of single block reads. In this example, the total CPU related costs amount to 3.
 
Oracle gives us an indication of what the CPU component is in the overall cost within the execution plan via the %CPU value (which is 5 in the above execution plan). The (%CPU) value is the ceil of the overall percentage of CPU costs as calculated by the following formula:
 
%CPU = ceil(CPU related costs/overall costs)
 
So in our example, %CPU = ceil(3/70 x 100) = ceil(4.29) = 5% (as indeed displayed in the above execution plan).
 

Again, all the costs associated with a FTS with the CPU costing model can be derived and make some kinda sense. Providing all the necessary inputs are all actually correct and valid, the CBO will indeed correctly decide to use a FTS over an index when it’s the less expensive option.
 
I’ll next expand these points and why understanding how these costs are derived can be extremely useful.

You can now put your calculators away 🙂