What is Cost ?

Alok asked this question today. The cost was coming as 231 in an explain plan. The best answer is given by Jonathan Lewis in his book "Cost Based Oracle". Chapter 1 of the book is called "What is Cost ?" He has defined it as:

The cost is the time spent on single-block reads, plus the time spent on multiblock reads, plus the CPU time required, all divided by the time it takes to do a single-block read. Which means the cost is the total predicted execution time for the statement, expressed in units of the single-block read time.

You can read the addenda on that chapter which has information about cost here.

Cost is time (30th Dec 2005)
Page 3, last complete paragraph. Following my claim that ‘cost’ really is ‘estimated time to run’, I explain that Oracle 9i chooses the sreadtim as the unit of time measurement for backwards compatibility so that the cost reported by 9i will generally be close to the cost reported by 8i for small, precise OLTP activity.

Another way of thinking about this is as follows:

By the definition in the 9.2 Performance Tuning Guide:

Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim
This says the cost is: “the time to estimated run in milliseconds, divided the sreadtim”. But in 8i, Oracle assumes that all read requests take the same time, and that CPU doesn’t count; in other words sreadtim = mreadtim, and CPUCycles = 0. So what happens if you put those assumptions into the 9i formula:

Cost = (
#SRds * sreadtim +
#MRds * sreadtim +
0
) / sreadtim = #SRds + #MRds
So the 9i formula gives the 8i result when you apply the 8i assumptions. In other words, Oracle 9i estimates the time to run, then divides by the sreadtim so that a query that doesn’t involved multi-block reads will show (nearly) the same cost in 8i and 9i.

As a final thought, the execution plan that you get from dbms_xplan.display() in 10g includes a time column. Here’s the execution plan for a simple query to count the rows in a table. Note that the execution plan is a full tablescan, doing multi-block reads only, but that I’ve modified the single-block read time before generating the execution plan:

/* execute dbms_stats.set_system_stats('SREADTIM',10) */
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 329 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | TABLE ACCESS FULL| T2 | 48000 | | 329 (1)| 00:00:04 |
---------------------------------------------------------------------------
And here is it again, just moments later:

/* execute dbms_stats.set_system_stats('SREADTIM',2) */
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1641 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | TABLE ACCESS FULL| T2 | 48000 | | 1641 (1)| 00:00:04 |
---------------------------------------------------------------------------
What’s the difference between these two runs that could cause such a change in the cost ? The fact that I changed the optimizer’s assumption about the time it would take for a single block read to complete. (By scaling the single block read down by a factor of five, I effectively scaled the multiblock read time up by a factor of five – so this query, which did nothing but multiblock reads increased in cost by a factor of five).

Notice that even though the cost of the query has changed dramatically the time for the query to complete has not changed. So how has the optimizer calculated the time? It’s simply taken the 9i formula and multiplied the sreadtim back in to get back to the original time. Go back to my settings for the sreadtim statistic.

In the first case (10 milliseconds): ceiling (329 * 10/1000) = 4, hence the four second time.

In the second case (2 milliseconds): ceiling(1641 * 2/1000) = 4, hence the same four second time.

Cost is Time – but the units are a bit funny.

- Jonathan Lewis