CA PPM: SaaS Refresh Data Warehouse OData Model Slow Performance

Document ID : KB000118445
Last Modified Date : 07/12/2018
Show Technical Document Details
Issue:

Why is the SaaS job called the Refresh Data Warehouse OData Model, not completing or taking a long time to complete?

Cause:

The problem can be a combination of a database performance issue and HDP (Hybrid Data Pipeline) server encountering an issue.
 

Resolution:

1. If the job never completes, the SQL execution is not optimal on the SaaS environment.

After profiling the SQL, the job runtime was brought down from hours to within 15 minutes completion time.

The SaaS database solution of ensuring the best SQL execution plan for this job can be applied to any environment for subsequent runs.

The database profiler selects a non-optimal plan based on the ALL_SYNONYMS table, therefore it is best to use USER_SYNONYMS table instead. 

The following SQL is to be profiled:
SELECT SYNONYM_NAME FROM ALL_SYNONYMS WHERE TABLE_OWNER = :v0 and TABLE_NAME = :v1 and OWNER = :v2

An improvement will result as a result of using the SQL Tuning Advisor.

2. If the job eventually fails, the HDP (Hybrid Data Pipeline) server needs to be restarted in addition to applying the best execution plan.