After upgrading to PPM 15.4.1, the FULL load Data Warehouse job runs for over 500+ minutes but never completes.
CA PPM 15.4.1
The following steps may help to resolve the performance issues for the DWH Load Jobs:
1. Change "optimizer_mode" to "all_rows" in the Oracle configuration
2. Change the Oracle Optimizer to "12.1" To do this run the following:
sqlplus / as sysdba
ALTER SYSTEM SET "_fix_control" = '17800514:0';
ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE= '184.108.40.206' SCOPE=BOTH;
3. Follow all post-upgrade steps as appropriate for your environment as documented here: https://docops.ca.com/ca-ppm/15-4/en/release-information/change-impact-and-upgrade-on-premise
4. There does not appear to be any PGA value set explicitly - you need to size accordingly. This is not something CA can assist with or provide to you as its all dependent on the size of your actual database - you will need to work with your DBA on this.
5. Gather the statistics on the PPM schema (the transnational NIKU db) as there may be some high reads on it. If so, this will cause the read to be slow from the PPM transaction database, thus it delays the write to DWH causing long delays when running the DWH jobs.
6. Check the fragmentation on the tables - review the fragmentation results on the AWR report and address the fragmentation.