CBO Cost Estimation
October 16, 2007
Oracle version 10gR2
When evaluating the performance of an Oracle SQL statement, you often encounter a cost estimate from the cost-based optimizer (CBO). For example, issuing an 'explain plan' request from TOAD will get a plan that starts something like this:
The cost column is in boldface above. What is it for, how is it used, and what does it mean?
The purpose of the cost is to direct the cost-based optimizer. The CBO always chooses the less costly of any two execution paths. The estimated costs are not a speedometer; the real performance may differ significantly from the estimate.
The way that the cost estimates are used in an 'explain plan' is that they bubble up, that is, they add to each other from bottom to up. Consequently the estimated cost of the whole SQL statement is in the top line.
The following excerpt shows how each cost reflects the cost of its own processing step and the cost of the step that feeds into it from below.
The lowest line is a costly hash join; the line above it is an inexpensive full table scan of something called
As for what the cost estimates mean, they are based on the equation
The numbers of reads and cycles come from the execution plan. The CBO calculates that for (let's say) a given table access by row ID, a certain number of single-block reads have to be executed against the relevant index and table. It calculates similarly for other access methods.
The costs derive differently, and they change during the life of an Oracle instance. On installation, they are defaulted. Industry average values for the I/O costs can be stated as follows.
In newer storage subsystems, an average single-block read shouldn't take more than 10ms (milliseconds) or 1cs (centisecond). You should expect an average wait time of 4 to 8ms (0.4 to 0.8cs) with SAN (storage area network) due to large caches.
(Shee, Deshpande, and Gopalakrishnan, Oracle Wait Interface, McGraw-Hill: 2004, page 107.) So, since the CBO algorithm estimates the cost of a query in units equivalent to single-block reads, and an average single-block read is said to consume 1 centisecond, you would expect that a SQL statement with an estimated cost of 100 will consume 1 full second in execution time.
That this is meant to be the case is shown, among other places, at the end of the standard text-based ADDM reports, where the following informational boilerplate appears.
A centisecond is 10000 microseconds, so this is the same assertion as Shee et al. made, quoted above. It is also consistent with the historical fact that Oracle's optimization and timing reports were always in units of 1 centisecond or higher until Oracle 9i began reporting in microseconds. It seems that out of the box, the CBO will estimate the cost of its execution plans in units of a centisecond, taken to be the time consumed by one single-block read. The results of the ADDM reports are based on the same numbers.
However, this still does not tell us if the CBO's current estimates have changed from the default, or what the CBO's current estimates are. For that, it is necessary to take one more thing into account. Since Oracle 9i, the CBO has improved its metrics by issuing periodic test reads to gather system statistics. Because of this, the CBO at some point after installation may have better metrics available to it than the default estimate of 1 centisecond per single-block read.
The way to verify the CBO's current metrics is to issue this query.
In the aux_stats$ view, pval1 from sreadtim gives the single-block read time in milliseconds. So, in the example, the single-block read time is much faster than the default estimate. The correct value is not sreadtim = 1 but sreadtim = .07618.
To understand optimizer costing on any system, it is necessary to go beyond the default values and to find out the current metrics. You can find more detailed discussion of the CBO's collection of statistics in an article by Kimberly Floss at oracle.com.
I hope this introduction to CBO cost estimation has been useful. If you have read this far, you might want to continue your investigation by following the references above.