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

  • From: "Joze Senegacnik" <Joze.Senegacnik@xxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 13 Jan 2005 13:26:10 +0100

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,

-----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 ?

   Tom Kyte says
that you cannot compare costs of two different query but you can compare =

costs of different plan of the same query.
I think it's safe compare different plan costs of the same sql with the=20
same Oracle session/database settings (i.e. I dont' know if the same=20
query plan costs with different PGA_AGGREGATE_TARGET are comparable).
If you have different query returning the same results and you had to=20
decide what is best I suppose you have to try the query in your test=20

> Friends,
> when i have got two plans , how do i decide=20
> which one is the best ?
> just read in asktom , that COST of a plan ,=20
> does not have any correspondence with response
> time of a query.
> i.e.,a query with higher COST than another
>  can be faster.
> if so , what in the execution plan does help=20
> me to decide about a good (faster) plan ?=20
> unless i execute it , can't i know it ?=20
> is that the only way !!!
> Can someone throw light on this ?
> The environment is oracle
> Thanks in advance.
> Regards,
> Prem.
> --
> //


Giovanni Cuccu
Sw Engineer@xxxxxxxxxxx
Dianoema S.p.A.
Via de' Carracci 93 40131 Bologna
Tel: 051-7098211   051-4193911
No man does it all by himself,
I said young man,
put your pride on the shelf


Other related posts: