Re: impact of changing optimizer_features_enable

  • From: "Greg Rahn" <greg@xxxxxxxxxxxxxxxxxx>
  • To: ujang.jaenudin@xxxxxxxxx
  • Date: Sat, 23 Jun 2007 21:33:37 -0700

If you are running into plan regressions when OFE
(optimizer_features_enable) is increased from version to version, as a
first method of triage, you can set OFE to the previous version.  This
however does not solve the root cause, it merely makes the symptom go
away temporarily. I personally have never heard any complaints about
any of the queries whose plans are now better/faster because of new
optimizer features, people only mention the bad ones...but that's
expected.  Just consider the fact that if you are globally setting OFE
to a previous version, you might very well be loosing out on on some
of the new/better things.

As a first step in troubleshooting plan regression, make sure the
stats are accurate.  Also, consider things like histograms are
gathered by default in 10g and were not in previous versions. I would
also encourge using defaults for optimizer init.ora parameters.  If
you have validated that the stats are representative (for example the
NDV and low/high values) and used default optimizer parameters, and
the plan regression still exists, then create an SR and give your test
case to Oracle Support.  Chances are, if it is a bug, it will get
fixed faster if you file an SR vs. just setting back OFE and posting
it to this list.

On 6/22/07, Ujang Jaenudin <ujang.jaenudin@xxxxxxxxx> wrote:
when tried *.optimizer_features_enable from 10.2 until 9.2 , the
report was not much different (so slow). but when change to
*.optimizer_features_enable=8.1.7, it amazingly so so so fast....

so, does anyone have experiences about changing the above parameter
and the impact to the applications?


Greg Rahn

Other related posts: