Re: impact of changing optimizer_features_enable

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_at_gmail.com> 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?

-- 
Regards,

Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l