Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Which plan is better - what COST really means ?

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

From: Joze Senegacnik <>
Date: Thu, 13 Jan 2005 13:26:10 +0100
Message-ID: <>

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: =
[]On Behalf Of Giovanni Cuccu Sent: Thursday, January 13, 2005 12:12 PM To:; Subject: Re: Which plan is better - what COST really means ?


   Tom Kyte says 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. 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 environment.

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

Received on Thu Jan 13 2005 - 06:19:46 CST

Original text of this message