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,
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.
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.
Giovanni
> Friends,
> when i have got two plans , how do i decide=20
> which one is the best ?
>=20
> just read in asktom , that COST of a plan ,=20
> does not have any correspondence with response
> time of a query.
>=20
> i.e.,a query with higher COST than another
> can be faster.
>=20
> if so , what in the execution plan does help=20
> me to decide about a good (faster) plan ?=20
>=20
> unless i execute it , can't i know it ?=20
> is that the only way !!!
>=20
> Can someone throw light on this ?
> The environment is oracle 9.2.0.4/AIX5.2L.
>=20
> Thanks in advance.
>=20
> Regards,
> Prem.
> --
> //www.freelists.org/webpage/oracle-l
>=20
>=20
--=20
----------------------------------------
Giovanni Cuccu
Sw Engineer@xxxxxxxxxxx
Dianoema S.p.A.
Via de' Carracci 93 40131 Bologna
Tel: 051-7098211 051-4193911
e-mail:gcuccu@xxxxxxxxxxx
----------------------------------------
No man does it all by himself,
I said young man,
put your pride on the shelf
----------------------------------------
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
