PROC_AFTER_LOADING_PKG_SCD Scenario, HistRollupActivity Step Taking Long Time To Complete Causing Extreme Performance Degradation With Data Warehouse ETL For P6 Source
Last updated on FEBRUARY 20, 2018
Applies to:Primavera Analytics Cloud Service - Version 22.214.171.124 and later
Primavera Analytics - Version 126.96.36.199 and later
Primavera Data Warehouse - Version 188.8.131.52 and later
Information in this document applies to any platform.
While running an ETL for a P6 datasource, the ETL execution takes a long time to complete.
- This bulk of the delay is stemming from the "After loading package SCD" step noted within the ETL log file.
- This step executes the the PROC_AFTER_LOADING_PKG_SCD Scenario which has 8 tasks. However, the entire delay is stemming from only one of the tasks - HistRollupActivity.
The HistRollupActivity task runs the following SQL:
Within the package procedure, the following update statement is being run:
UPDATE w_activity_history_f wahf
FROM w_activity_spread_hf wasf
WHERE wasf.activity_wid = wahf.activity_wid AND wasf.datasource_id = wahf.datasource_id AND wasf.datasource_id = vsrc_id
WHERE wahf.day_wid = dayRowWid AND wahf.datasource_id = vsrc_id AND wahf.project_object_id = proj.project_object_id;
Above Statement repeats for:
- Every proj.project_object_id; within the following cursor:
SELECT project_object_id, NVL(history_interval, histInterval) history_interval FROM w_project_d WHERE datasource_id = vsrc_id and UPPER(NVL(history_level, histLevel)) = 'HL_TASK';
- Associated W_DAY_D.row_wid (from SELECT getdayrow(status_get_start_date(1), 1, 'HL_TASK') FROM DUAL) for the ETL run.
Identify tuning to the SQL which is executed within the hist_rollup_pkg.rollup_to_activity_history package procedure for improving the overall duration of a P6 datasource ETL (and specifically, the HistRollupActivity task from PROC_AFTER_LOADING_PKG_SCD Scenario)
The issue can be reproduced at will with the following steps:
1. Login to the Analytics Administration application
2. Note the amount of time it takes for a P6 ETL to complete.
This issue is occurring within production.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms