CA PPM: Performance of Load DWH job on Oracle 12.2

Document ID : KB000104122
Last Modified Date : 03/07/2018
Show Technical Document Details
Issue:

The Load DWH job runtime can increase when no data change was made.

The other stock PPM jobs do not increase but have the same duration.

 

 

Cause:

Database configuration/maintenance is needed when a database is migrated from a lower version of Oracle to the current Oracle 12.2 (Release R2).

If this is not the case, the following query can be ran on the DWH database to check for progression:

SELECT *
FROM DWH_GATHER_METRICS_V
order by dw_load_start_date desc

The first record returned is the latest table being processed.

The final table to be processed is DWH_INV_SUMMARY_FACTS.

The DW_UPDATED_DATE shows the last time the job ran. All the records will have this date if the job is successful. If this column shows a date of 01-JAN-1910, then has not been processed.

 

Resolution:
The database team will need to open a ticket with the Oracle Support team to review/mitigate the performance issue.