Performance problems after upgrading from 10g to 11GR2
We have a third party application that had been running on 10.2.0.4 for a few years without any performance issues. The third party vendor has now certified the software to run on 11GR2. We upgraded the database to 11GR2 in test environment and noticed significant degradation in the performance. I turned on tracing a found many SQL statements that were causing the problems. These SQL would execute in seconds on 10G, and the same SQL took a few minutes to execute in 11GR2. I set the optimizer_features_enable=10.2.0.4 and restarted the database and everything went back to normal. Unfortunately, we cannot optimize the SQL code since this is vendor software.
Question: If I keep the optimizer_features_enable=10.2.0.4 permanantly, does this mean that all the SQL will use the 10G optimizer in the 11G instance and not the 11G optimizer ? .
It means Ora11gR2 optimizer will behave as if it was Ora10gR2 - http://docs.oracle.com/cd/B28359_01/...tparams158.htm
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
In 11R2 there are new SQL fearures, If I use some of these 11G features, will it still behave as Ora10gR2 optimizer or 11G ?
You can always create sql profiles to "fix" vendor software.
Originally Posted by pascal01
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Click Here to Expand Forum to Full Width