Re: Meaning of cost in a plan?

A copy of this was sent to Jan Nowitzky <nowitzky_at_informatik.uni-jena.de> (if that email address didn't require changing) On Sat, 23 Oct 1999 14:15:57 +0200, you wrote:

>Hi,
>
>I have following situation.
>The plan of my query (Q1) says 'Cost=1237'. Then I have changed the query
>(Q2) and the new plan says 'Cost=780'. But the response time of Q2 is larger.
>
>Query | Cost (estimated) | Response Time
> Q1 | 1237 | 123 sec.
> Q2 | 780 | 301 sec.
>
>What is the relevance of the cost in the query plan?
>What means this cost number in the query plan (which unit)?
>My statistics are up to date.
>

the costs for queries CANNOT be compared across query plans. You cannot compare the 1237 from query 1 to 780 for query 2. When the optimizer gets a query, it makes a couple of plans and assigns costs to them and compares those costs -- the costs for a given query with different plans can be compared (but you never get to see the other plans that were developed and discarded).

The costs for 2 different queries are *not* comparable -- the costs for the SAME query with 2 different optimizer/session settings are not comparable.

For example, in many cases, you influence the cost by HINTING to the optimizer to use a specific index. That made the optimizer assign a *really* low cost to the access plan that used that index to help it become the plan of choice -- by using the hint with the index name -- YOU lowered the cost. The runtime of the query with the lower cost (as influenced by you) might be much longer (or shorter) then the default plan picked without the hint.

>Thanx, Jan
>
>--------------------------------------------------------------
>Jan Nowitzky
>Friedrich-Schiller-Universität o Institut für Informatik
>E-Mail nowitzky_at_informatik.uni-jena.de
>--------------------------------------------------------------

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Oct 23 1999 - 08:35:27 CDT