Introduction:
We have recently upgraded a database from 11gR2(11.2.0.4) database to 12cR1(12.1.0.2) on EBS environment. Although the whole upgrade process went well, but on post upgrade – we have encountered performance slowness on database queries while accessing few database objects. After a thorough analysis from AWR/ADDM and TFA diagnosis reports, there found to be a bug hit on 12.1.0.2 which caused the degrade of those query performances in the database and overall execution time have increased.
Although, we had a latest patch set (PSU-July 2020) applied and ETCC been run against 12c database home. But unfortunately, this bug is not covered under PSU as confirmed by Oracle support and it must be applied as a one-off.
In this blog, let’s see how to fix this type of performance issue if encountered on post database upgrade to 12.1.0.2 on an EBS environment.
Description:
To begin with, the database upgrade process completed successfully with reference to the Doc ID :1926201.1 and all the mandate database initialization parameters were set as part of the upgrade process.
To identify if you have similar kind of issue , connect to database as
sqlplus / as sysdba
Select count(*) from sys.all_synonyms; à Query elapsed time : 7min whereas before the upgrade its only 1.8 min
If you are using sql developer tool (latest 20.2.0), similar kind of delay can be observed using the option “pop-up describe”, here is a sample for reference.
Solution:
Try the workaround provided at session level,
alter session set “_connect_by_use_union_all” = ‘old_plan_mode’;
Once it found working, we can apply the change permanently through the patch p22113854_12102160119_Generic.zip or latest release of it on 12c(12.1.0.2) database, since it’s exclusive to this issue matched with a known bug in 12c slow query on ALL_SYNONYMS ( Doc ID 22113854.8 ) and confirmed by Oracle support.
Bottom line:
Hope this blog saves you hours of time in troubleshooting such performance bug!!
