Incremental Load DWH job very slow after upgrade due to an slow update query on DWH_INV_ASSIGNMENT or DWH_TME_ENTRY

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Incremental Load DWH job very slow after upgrade due to an slow update query 

STEPS TO REPRODUCE:

1. Post upgrade to 16.3.1, run the full load once a day
2. Make some task level changes
3. Run the Incremental Load DWH job

Expected Results: The job to finish successfully at a reasonable time
Actual Results: The job is slow at running either of the updates below:

UPDATE DWH_INV_ASSIGNMENT SET PRTASKID_VALUE = (SELECT PRTASKID_VALUE FROM DWH_LKP_PRTASKID lkp INNER JOIN (SELECT ASSIGNMENT_KEY AS dim_pk, TASK_KEY AS dim_key, PRTASKID_VALUE AS dim_string, 'en' AS language_code FROM DWH_INV_ASSIGNMENT dim) iv ON (iv.dim_key = lkp.PRTASKID_KEY) WHERE DWH_INV_ASSIGNMENT.ASSIGNMENT_KEY = iv.dim_pk AND lkp.dw_updated_date >= TO_DATE('26-jun-2025 01:55:29', 'dd-mon-yyyy hh24:mi:ss')) WHERE EXISTS (SELECT PRTASKID_VALUE FROM DWH_LKP_PRTASKID lkp INNER JOIN (SELECT ASSIGNMENT_KEY AS dim_pk, TASK_KEY AS dim_key, PRTASKID_VALUE AS dim_string, 'en' AS language_code FROM DWH_INV_ASSIGNMENT dim) iv ON (iv.dim_key = lkp.PRTASKID_KEY) WHERE DWH_INV_ASSIGNMENT.ASSIGNMENT_KEY = iv.dim_pk AND lkp.dw_updated_date >= TO_DATE('26-jun-2025 01:55:29', 'dd-mon-yyyy hh24:mi:ss'))

UPDATE    DWH_TME_ENTRY SET    TASK_ID =    (SELECT   TASK_ID FROM   DWH_INV_TASK lkp    INNER JOIN(   SELECTTIMEENTRY_KEY AS dim_pk,TASK_KEY AS dim_key,TASK_ID AS dim_string,:"SYS_B_0" AS language_code    FROMDWH_TME_ENTRY dim)iv ON (TO_CHAR(iv.dim_key) = TO_CHAR(lkp.TASK_KEY)) WHERE   DWH_TME_ENTRY.TIMEENTRY_KEY = iv.dim_pk    AND lkp.dw_updated_date >= TO_DATE(:"SYS_B_1", :"SYS_B_2")   )WHERE   EXISTS    (SELECT   TASK_ID FROM   DWH_INV_TASK lkp    INNER JOIN(   SELECTTIMEENTRY_KEY AS dim_pk,TASK_KEY AS dim_key,TASK_ID AS dim_string,:"SYS_B_3" AS language_code    FROMDWH_TME_ENTRY dim)iv ON (TO_CHAR(iv.dim_key) = TO_CHAR(lkp.TASK_KEY)) WHERE   DWH_TME_ENTRY.TIMEENTRY_KEY = iv.dim_pk    AND lkp.dw_updated_date >= TO_DATE(:"SYS_B_4", :"SYS_B_5")   )

Environment

Clarity 16.3.1, 16.3.2, 16.3.3 with Oracle

Cause

DE171769/DE172491

 dw_updated_date is updated for all records and so it is trying to update all records getting stuck. It should be using clarity_updated_date instead

Resolution

• Fixed in Clarity 16.4.0
• Fixed in Clarity 16.3.3 Patch 1 (16.3.3.1) 

Workaround: Run the Full Load DWH job

Contact Support if additional workaround is needed