Results 1 to 5 of 5

Thread: Explain plan

  1. #1
    Join Date
    Aug 2003

    Unanswered: Explain plan

    I need your help to understand the output of the Explain plan
    I am attaching explain plan out put of one of my query, can some one help me to understand this output'


    Execution Plan
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=67 Card=46 Bytes=446

    1 0 SORT (UNIQUE) (Cost=64 Card=46 Bytes=4462)
    2 1 NESTED LOOPS (Cost=62 Card=46 Bytes=4462)
    3 2 MERGE JOIN (Cost=16 Card=46 Bytes=4002)
    4 3 SORT (JOIN) (Cost=12 Card=47 Bytes=3196)
    5 4 MERGE JOIN (Cost=11 Card=47 Bytes=3196)
    6 5 SORT (JOIN) (Cost=7 Card=47 Bytes=2209)
    7 6 MERGE JOIN (Cost=6 Card=47 Bytes=2209)

  2. #2
    Join Date
    Oct 2003

    Red face

    There's no substitute for the documentation but also remember this... The only part of the output that really matters is the list of steps that Oracle plans to execute. "What does Oracle propose to do in response to this query-input?"

    The magic-numbers are .. well .. interesting, but really not very useful. "When you see the computer proposing to travel from Phoenix to San Francisco by way of Tokyo, the essential problem is 'Tokyo.'" Armed with this information you can change the query, put on indexes, break the problem down into smaller steps, or what have you. And you can present each new plan to the Oracle to see what it comes up with this time. So you don't wind up looking at Mount Fuji.

    The plan is useful when comparing two queries, to compare the two routes that Oracle proposes to travel in order to arrive at the destination.
    Last edited by sundialsvcs; 12-29-03 at 11:21.
    ChimneySweep(R): fast, automatic
    table repair at a click of the

  3. #3
    Join Date
    Apr 2003
    Greenville, SC (USA)
    It is hard to help with the information given ... Definitions ...
    The NESTED LOOPS=Join two tables where at least one index is used

    The SORT JOIN=Sort a set of records for a MERGE JOIN operation

    The MERGE JOIN=Join tables by merging sorted lists of records from each table

    The SORT UNIQUE=Sort a result set and eliminate duplicates


  4. #4
    Join Date
    Aug 2003
    I am sorry, Actualy what I want to know is what is COST,CARD and BYTES, what should be the Ideal value for those parameter for good performance and I understand that the lower value of COST will improve the performance is it true always

    which are all the other parameter we need to look into for the beter performance.


  5. #5
    Join Date
    Jul 2003
    Hah, sorry but that is a funny question.

    I suggest you run a sql-trace on the query, tkprof the trace file with explain, and see what Oracle is REALLY doing with the query.

    What you have right now is only what Oracle THINKS it will probably do with the query and not what it will ACTUALLY do.

    To answer your last question is impossible. The only rule is that LOWER is better. Each 'Ideal Value' is specific to each and every sql statement. There is no magic number since every statement gathers a different amount of data.

    If you want to know the meaning of everything then just go here and look up Explain Plan: is also a very good resource.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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