Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Cost as used in 'EXPLAIN PLAN'

Re: Cost as used in 'EXPLAIN PLAN'

From: <raghuram_govind_at_my-deja.com>
Date: Tue, 06 Jul 1999 20:19:27 GMT
Message-ID: <7ltoc3$o60$1@nnrp1.deja.com>


Hi Guys:

I have been a oracle developer for some time and am just getting into optimization. The explain plan for a SQL statement is giving lots of info from which I am finding it difficult to understand what each one of those statements mean (physical reads, disk access, recursive calls, consistent gets ...). Can some one suggest a web site / document where I can find this info.

In article <376a0732.6835168_at_newshost.us.oracle.com>,   tkyte_at_us.oracle.com wrote:
> 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
> --
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jul 06 1999 - 15:19:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US