Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Location
    India
    Posts
    4

    Unanswered: Explain Plan and Cost...

    hi all,

    We use EXPLAIN PLAN to find out the cost of executing SQL statements.. and we try to minimize the COST.
    Suppose for executing a particular SQL statement, if i get cost is 120, wht does that 120 actually represent?

    Thanks in advance

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    The Oracle documentation is unclear on this, it contradicts itself by stating both of the following...

    1: cost has no real world measure, it is is purely an arbitrary value to help compare plans
    2: That it relates to some multiplier of disk blocks physicaly read

    Tom Kyte (a veritable Oracle guru) also argues, quite strongly that cost has no useful purpose whatsoever. His arguments are convincing but I still disagree with him :-)

    I think Tom's argument is based, in part, on the fact that many people do not understand that cost (in explain plan) is an expected cost, not an actual and that a high cost query can turn out much faster than a low cost one.

    There are fairly simple ways to trip up the optimiser on cost. I have written an introduction to SQL tuning document which covers this, and at the end points out that while explain plan is a valuable tool, you should supplement this with post query analysis (tkprof et al).

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Feb 2004
    Posts
    45

    Re: Explain Plan and Cost...

    I have to agree with billm that the cost reported by explain plan is not easy to relate to something tangible. It's also true that the tkprof approach He recommens is more usefull.

    Are you evaluating all of your repaeated SQL statements for tuning ? If so you may want to do some regular monitoring of your instance(s) to see how overall resource utilization is doing. That may be a better approach to overall tuning.

    You can improve things overall by making sure db blocksize is right for you situation, balancing load across disk volumes, making sure you have enough memory (ant that it is allocated well), watching latch contention, and other things that are not statement specific. These things will also help you forecast load and anticipate needs rather than cleaning up after troublesome statements.

    HTH
    Cliff

    Originally posted by kunchesm
    hi all,

    We use EXPLAIN PLAN to find out the cost of executing SQL statements.. and we try to minimize the COST.
    Suppose for executing a particular SQL statement, if i get cost is 120, wht does that 120 actually represent?

    Thanks in advance
    It was working just 5 minutes ago - I promise !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •