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

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 = like.

Just seems curious to me.

Karen Morton
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events at http://www.hotsos.com/education/schedule.html

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Friday, January 14, 2005 2:13 PM
To: oracle-l_at_freelists.org
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 used.

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 ?

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Dec 23rd 2004

--
http://www.freelists.org/webpage/oracle-l