performance degrade after upgrade fron 8.1.7 to 9.2.0

[hannah00]

Hi all,

One of my client's database performance degraded after upgrading from 8.1.7 to 9.2.0. I am aksing them to send me the STATSPACK report so I am still waiting. I did some research and it may be very well having something to do with optimizer_features_enable parameter. B/c of this parameter, the impact could relate to many other parameter in 9.2.0 since it's so different between the two version.

One of the suggestion is that revert this parameter back to 8.1.7. My question is what can we do if we want to leave this parameter in 9.2.0.


thanks,

[dbasan]

Hi all,
One of the suggestion is that revert this parameter back to 8.1.7. My question is what can we do if we want to leave this parameter in 9.2.0.

1. You can wait for the statspack report, then troubleshoot.
2. reverting back the parameter to 8.1.7, will get back the optimizer settings(oracle recommends to have the same old settings then after careful analysis set the paramenter to the new version).
3. Check the Oracle docs on the enhancements to 9.2.0, with the statspack report do the required tuning to get the expected performance.

[BV1963]

I had the same problem and what we did was we had to identify every single query which permormance was degraded and tune it.
Another thing you try is to change the way you analyze your statistics.
Bottom line is that default behavior of the optimizer changed in 9I and it is causing a problem. I had to use /=* rule */ hint in order to make some queries work