| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Giovanni Cuccu
Sent: Thursday, January 13, 2005 12:12 PM
To: premj_at_rediffmail.com; oracle-l_at_freelists.org
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
>=20
>=20
>=20
>=20
>=20
>=20
--=20
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 13 2005 - 06:19:46 CST
 |
 |