Oracle 12 Performance Issues

[Terry Becker]

I am in the middle up upgrading my CA PPM from 14.3 to 15.3.  Part of my migration plan is to move from Oracle 11 to Oracle 12.  This has become problematic due to poor performance.  The big issue that I discovered is that the attributes page for the Project object will not open.  I click the tab, and then the application never returns.  I have isolated the query in OEM and it continues to run even after the application gives up.   It just ran 2 hours before ending.

 

I am also experiencing poor performance on a few of our custom jobs

 

I have 2 environments in different stages. Both fail.  It appears that the oracle version is causing the problems.

 

Dev:  Oracle 12 & PPM 15.3

QA: Oracle 12 & PPM 14.3 

 

I have matched my db parameters to the CA recommended settings. 

 

I'm hoping this sounds familiar to someone who has solve it in their environment.

 

Thanks,

Terry

[Damon Logiudice]

Sorry to hear of these temporary frustrations with the upgrade. I've been advised to offer the following suggestions:

1. Consult the Change, Impact, and Upgrade Notes and the Upgrade documentation on docops.ca.com/ppmop. Did you install/upgrade the PMO accelerator, data warehouse, and run the load data warehouse job (all required for 15.3)?
2. As an Oracle DBA, try the Automatic Workload Repository (AWR) report for time details on slow queries.
3. Let CA Support or the PPM Community know if you are on Oracle 12.2.0.1 or 12.1.0.2.
4. For Oracle 12c R2 (12.2.0.1), you can also try enabling the following parameter and set the optimizer to 12.1.0.2 as shown on this page: Change Impact and Upgrade (On Premise) - CA PPM - 15.3 - CA Technologies Documentation 
   To improve performance, database administrators can run the following commands:
     sqlplus / as sysdba
     ALTER SYSTEM SET "_fix_control" = '17800514:0';
     ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE= '12.1.0.2' SCOPE=BOTH;
    Exit;
   This issue and workaround are subject to change pending further third-party developments.

[Terry Becker]

Thanks for your reply Damon, 

I have two environments, both behaving the same way. 

 

My Dev is on PPM 15.3 with Oracle 12.1.0.2.

My QA is on PPM 14.3 also with Oracle 12.1.0.2.

 

Both have the same problem with the Project Object's Attributes page.   It appears to be the version of Oracle.

 

My  _fix_Control = 17376322:OFF and OPTIMIZER_FEATURES_ENABLE =  11.2.0.3

 

My DBA and I were able to get the attributes page to open by setting the Optimizer back to this 11.2.0.3 setting.  I will review these suggestions with my DBA.

 

Thanks again for you suggestions.

[Suman Pramanik]

Along with what Damon said, request the DBA to run the SQL profiler to see if database is picking the optimized plan. 

[Legacy User]

Hi,

We have encountered the same problem with the Project Object and also set the optimizer back.  Have you found any other possible problems since you set the optimizer back?

[Terry Becker]

Hi,  I haven't found any other problems since setting the optimizer back to 11.  I am currently requesting more CPU/RAM for the database server and will try to move it forward as Damon had suggested.  We are not using any of the 15.3 User Experience functionality yet, so I cannot tell you if that would be impacted.

 

I'll let you know if I see any other effects of our modification.

[Legacy User]

Thanks for your quick response.  By any chance did you open a CA ticket?  When we opened one they made it sound like we were the only ones experiencing this.

[Terry Becker]

Hi,  I did open a case with CA Support, but it was under a larger issue I was having with Oracle 12 performance in general. That might explain why they didn't catch the similarities.  Anyway,  they recommended that I try things like increase the RAM on the server and increase SGA/PGA parameters on the server.  I'm in the process of requesting those changes now.  I'm hopeful that will help.  Just curious, does your Project object have many attributes?  I realized that we are probably pushing the limit on the number of attributes on that object. Most of the other attributes pages did open. 

 

Another thing I'm looking into is if the optimizer has chosen a new execution plan.  We had an issue where the optimizer picked a worse plan for time slicing.  It killed our response times.   Just a thought.