Re: Which plan is better - what COST really means ?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 14 Jan 2005 22:12:54 -0000
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
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
obvious example of how the models fail - and helps
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.
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
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
----- Original Message -----
From: "Joze Senegacnik" <Joze.Senegacnik@xxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, January 13, 2005 12:26 PM
Subject: RE: Which plan is better - what COST really means ?
Below is slightly changed excerpt from my presentation=20
"How To Forecast Tuning Results" on Hotsos Symposium 2004:
"A lot of developers and DBAs use cost as the most important measure in=20
the SQL statement tuning process. The misunderstanding of the cost=20
concept leads them to erroneous conclusion that lower cost means=20
faster execution and vice versa. When they accidentally use the right=20
hint and run the statement but the response time is much better, they=20
ask themselves how this is possible because the cost is higher but the
execution is faster. Another very common misconception is to compare=20
the cost and the performance of two completely different SQL=20
statements and make some conclusions from that. The cost can only be=20
treated as the CBO's internal measure that is used in the process of=20
selecting the optimal plan. "Cost" is the result of the "price" of the=20
access method and the estimated cardinality of the row source. When we=20
recall that the cardinality of a row source (i.e. table, result of=20
previous operations) is calculated from the base cardinality of the=20
row source and the estimated selectivity of predicates, we suddenly=20
discover the origin for sub-optimal execution plans. Thus both factors=20
that are used in a cardinality computation can contribute to the plan=20
becoming sub-optimal. Incorrectly estimated selectivity and an=20
inaccurate base cardinality of the table have same effect. How can one=20
see the possible danger that is hiding in the execution plan? The=20
estimated cardinality as well as cost is reported for each step in the=20
execution plan. People usually do not pay too much attention to the=20
cardinality but rather (and that's the major problem) to the estimated=20
cost. The theoretical execution plan, produced by the explain plan=20
command, also contains the estimated cardinality of the final result.=20
As cost is always a result of cardinality we should focus only on the=20
estimated cardinality. A developer, when writing the text of a SQL=20
statement, knows the purpose of the statement and also knows what he=20
wants to get as the result. Also the cardinality of the result set is=20
known - or at least he/she has an idea about that. Thus comparing the=20
estimated cardinality with the expected cardinality would be good=20
practice for timely elimination of performance problems. When those=20
two cardinalities differ by orders of magnitude performance problems=20
are almost inevitable. If the CBO were to correctly estimate the=20
cardinality of final or intermediate result sets, the cost would be very =
different and some other plan with a lower cost would be selected.=20
Most likely this plan would be the optimal one."
Kind regards,
Joze
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Giovanni Cuccu
Sent: Thursday, January 13, 2005 12:12 PM
To: premj@xxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Which plan is better - what COST really means ?
Hi,
Tom Kyte says
http://asktom.oracle.com/pls/ask/f?p=3D4950:8:7678906812144089472::NO::F4=
950_P8_DISPLAYID,F4950_P8_CRITERIA:313416745628
that you cannot compare costs of two different query but you can compare =
costs of different plan of the same query.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
