RE: Which plan is better - what COST really means ?

  • From: "Karen Morton" <karen.morton@xxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 14 Jan 2005 14:43:53 -0800

So, if a query has a cost of 2, the optimizer is estimating a 2
second/centisecond/microsecond response time when the statement is =
executed. =20

In what time measurement is the cost?  Seconds, centiseconds, =
microseconds? =20

I can never seem to equate a value for cost that reliably matches any =
one of those.  Even
when the optimizer is right on target as far as cardinality estimates go =
and the query
executes as quickly as I might desire, it seems to rarely be that the =
cost matches any
elapsed time value I can measure or match any statistics I can collect =
from v$ views,
actual response time consumption from extended SQL trace data and the =

Just seems curious to me.

Karen Morton
Hotsos Enterprises, Ltd.
Upcoming events at

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Jonathan Lewis
Sent: Friday, January 14, 2005 2:13 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Which plan is better - what COST really means ?

One day someone, somewhere, will agree
with me that the cost of a query IS and always
has been the optimizer's estimate of the actual
run time of a query - with the slightly idiosyncratic
"assumed time for a single block read" as the unit
of measure.

The fact that it is easy to find examples where=20
there is no apparent relations between the cost
of two queries, or the cost for two paths for
the same query, and the actual run time is the
consequence of the deficiencies in the models

The fact that (as Joze points out) you can find
execution plans where the predicated cardinality
is nowhere near the actual cardinality is the most=20
obvious example of how the models fail - and helps=20
to explain why cost and time seem to be unrelated.

The fact the v7 didn't allow for time spent on the CPU,
the difference in time required for single and multiple block reads, and =
benefits of the
buffer cache also helps to explain the apparent lack of correlation =
between cost and time.

I don't often disagree with Tom, but on this I do.=20
How different do two plans have to be before you
cannot compare their cost ?  What if one query has
two different plans because Oracle has transformed
the query in two different ways - can you still compare
the costs of the two plans or not ?  Can I compare
the cost of a manually unnested subquery against=20
the cost a filtered subquery - or is it only the optimizer
that is allowed to do that when it does the unnesting automatically ?


Jonathan Lewis
The Co-operative Oracle Users' FAQ
Public Appearances - schedule updated Dec 23rd 2004


Other related posts: