Re: Cost based Optimizer

Murali Kazhipurath (murali_at_jps.net) wrote:
: I am looking for suggestions and tips from Oracle experts who have used the
: COST based optimizer. We are facing some problems with it and have found
: that the RULE based optimizer is much more predictable and efficient for
: our environment. However, I heard that Oracle might discontinue RULE based
: optimizer. So at this point, I am looking for input from Oracle experts. I
: have analyzed all the tables and built the required indexes. The COST based
: optimizer doesn't seem to be smart enough to pick the correct indexes.
: Also, the execution time is much lower when I alter the session to use the
: RULE based optimizer. The execution plan clearly shows that the RULE based
: optimizer is using indexes and the COST based optimizer is doing a full
: table scan. Any ideas???

First, I think Oracle has backed off of earlier statements regarding the demise of the Rule-based optimizer.

Going to the Cost-based optimizer is no trivial task. We investigated it and saw horrendous performance degradation (due to the fact that we had originally tuned around the rule-based optimizer). Cost in particular seems to like to full-table-scan small tables even if they are joined to repeatedly. Apparently, cost-based is attempting to do the fewest I/O operations, and since full table scans are relatively efficient they show up (too) often.

You should not see much if any difference if you analyze the tables and then set your session (or instance) to rule.

Going to cost-based, especially if your app has been written to take advantage of assumptions about the rule based optimizer, will not be a quick or simple process.

Joe

-- 
                                   
=-------------------------------------------------------------------------=
Joe Nardone <nardone_at_patriot.net>
  "There are many dying children out there whose last wish is to meet me."
                                                  - David Hasselhoff
  "The most beautiful thing we can experience is the mysterious.  It is
  the source of all true art and all science.  He to whom this emotion
  is a stranger, who can no longer pause to wonder and stand rapt in awe,
  is as good as dead: his eyes are closed."       - Einstein