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

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_at_snt.si> To: <oracle-l_at_freelists.org> 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_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.

--
http://www.freelists.org/webpage/oracle-l