This discussion is archived
3 Replies Latest reply: Mar 26, 2013 4:19 AM by KR10822864 RSS

Cost-Based Optimizer

user3266490 Newbie
Currently Being Moderated
Hi,

Db : 10g and above.

I read below link which says that " Cost-Based Optimizer (CBO) - The CBO uses database statistics to *generate several execution* plans, picking the one with the lowest cost, where cost relates to system resources required to complete the operation."

http://www.oracle-base.com/articles/misc/cost-based-optimizer-and-database-statistics.php

Is it possible to see all the execution plan for a particular query?
My intention is to know why CBO rejects other plans.

Thanks & Regards,
VN
  • 1. Re: Cost-Based Optimizer
    sb92075 Guru
    Currently Being Moderated
    user3266490 wrote:
    Hi,

    Db : 10g and above.

    I read below link which says that " Cost-Based Optimizer (CBO) - The CBO uses database statistics to *generate several execution* plans, picking the one with the lowest cost, where cost relates to system resources required to complete the operation."

    http://www.oracle-base.com/articles/misc/cost-based-optimizer-and-database-statistics.php

    Is it possible to see all the execution plan for a particular query?
    My intention is to know why CBO rejects other plans.

    Thanks & Regards,
    VN
    http://www.lmgtfy.com/?q=oracle+10053

    It is documented; but you MUST be willing & able to just Read The Fine Manuals


    Handle:     user3266490
    Status Level:     Newbie (25)
    Registered:     Nov 26, 2008
    Total Posts:     1,423
    Total Questions:     829 (669 unresolved)
  • 2. Re: Cost-Based Optimizer
    rp0428 Guru
    Currently Being Moderated
    >
    Is it possible to see all the execution plan for a particular query?
    My intention is to know why CBO rejects other plans.
    >
    A 10053 trace will include a section for all of the plans that were considered and has information for those that were rejected.

    See this AskTom blog for one example.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:63445044804318

    This more recent Oracle Scratchpad article by noted author and Oracle ACE Jonathan Lewis includes a link to a 10053 viewer.
    http://jonathanlewis.wordpress.com/2010/04/30/10053-viewer/
  • 3. Re: Cost-Based Optimizer
    KR10822864 Pro
    Currently Being Moderated
    please check below link.
    http://docs.oracle.com/cd/B10500_01/server.920/a96533/optimops.htm#48061
    http://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15tech-tuning-090612.html

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points