Post WIP Actuals to Financial Plans job is slow due to missing index on INV_INVESTMENTS.PARENT_ID

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Post WIP Actuals to Financial Plans (UPDATE_FIN_ACTUALS) takes up to 6 hours where the prior job version ran under 40 minutes, because its per-plan actuals query full-scans PPA_WIP instead of probing it by investment id.

STEPS TO REPRODUCE:

1. On a large Oracle dataset with many thousands of WIP records to post
2. Run Post WIP Actuals to Financial Plans UPDATE_FIN_ACTUALS job with default options
3. While the job runs, get the SQL_ID for revmgr.getCostPlanDetailActuals from V$SQL and run:
   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sql_id, NULL, 'ALLSTATS LAST'));
   Observe TABLE ACCESS FULL on PPA_WIP with direct path reads, driven by a hash join against the CONNECT BY subquery on INV_INVESTMENTS, once per plan.

Expected Results: The job to complete within reasonable time
Actual Results: The Post WIP Actuals to Financials job takes 6 hours.

Environment

Clarity 16.4.1, 16.4.2, 16.4.3

Cause

DE210472

Resolution

In review by Engineering

Workaround: CREATE INDEX CLARITY.Z_INV_CONNECT_BY ON CLARITY.INV_INVESTMENTS (PARENT_ID) ONLINE

Note: The index must be dropped before upgrade and then recreated if defect is not addressed in the version you are upgrading to