Thursday 6 June 2019

Performance degradation after upgrading Oracle database from 11g to 12c for documentum applications

I faced major performance issues when I upgrade database environment for one of the system from 11g to12c (12.1.0.2.0). Front end application is Document D2.

Here is my analysis -

In Oracle 12.1 the adaptive optimizer features are controlled by the OPTIMIZER_ADAPTIVE_FEATURES parameter, which was set to TRUE by default.

This is introduced to better estimate statistics and optimize plans. In some system,  this lead to the performance issues.


SOLUTION -

I found the below tuning works for my environment

Use the following system setting to disable Adaptive Query Optimization:
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_FEATURES = FALSE SCOPE=BOTH; 


Also there are some tables which still contains statistics, so have to flush them out as well to get the better result. Contact your DBA team with these details.








No comments:

Post a Comment

Make life easier — Git automation with single command file

Make life easier — Git automation with single command file Posted on medium #makelifeeasier series - Automation of git related activity...