CA PPM: Performance of Load DWH job on Oracle 12.2

book

Article ID: 104122

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

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.

Environment

Release:
Component: ODDWH

Resolution

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