3 Replies Latest reply: Mar 26, 2013 6:19 AM by KR10822864 RSS

    Cost-Based Optimizer

    user3266490
      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
          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
            >
            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
              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