Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Cost as used in 'EXPLAIN PLAN'
A copy of this was sent to Greg Akins <insomnia_at_a1usa.net>
(if that email address didn't require changing)
On Thu, 17 Jun 1999 05:59:55 -0800, you wrote:
>Hi,
>
>I understand that cost is a relative measure of query
>efficiency. Can somebody give me a rough idea of scale.
>
>For example, I do an explain plan and get some processes
>returned as Cost=1 or Cost=1 2. Then some get returned
>with Cost>5000.
>
>Is Cost>5000 an unreasonably large figure. Those costs are
>related to a sort that appears to stem from a 'select
>distinct' What can I do to minimize the cost and still
>effect the 'select distinct'
>
>-greg
>
>
>
>**** Posted from RemarQ - http://www.remarq.com - Discussions Start Here (tm) ****
the costs within a query plan are useless to you. they mean nothing. ignore them.
the optimizer assigns costs to each phase of MANY different plans. It then takes each plan and performs a function on the costs of each step. It then picks the plan with the lowest cost.
a query plan with the cost of 5,000,000 might very well run faster then a plan with a cost of 1. You cannot compare the costs of 2 different plans ever -- it doesn't make sense. only the optimizer can compare costs and then only for a set of plans developed for the same query.
the costs of certain steps are affected by many many things -- statistics (or lack thereof), heuristics, HINTS (thats how hints work -- by artifically reducing the cost of certain steps in the hopes that makes the optimizer pick the plan with that step in it), and so on...
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA--