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?
-- Regards, Greg Rahn http://structureddata.org -- //www.freelists.org/webpage/oracle-l