DWH Performance with Incremental on custom TSV

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

With a custom TSV, noticing DWH Performance issue with Incremental, it is taking over 3 hours.

 

STEPS TO REPRODUCE:

1. Run DWH – Incremental job

Expected Results: DWH to run in timely manner

Actual Results: The Load DWH – Incremental took 3 hours, 2 of which on the below statement:

DELETE
FROM
   DWH_Z_CAP_TIME_E00_FACTS TABLE1
WHERE
   EXISTS
   (
      SELECT
         1
      FROM
         DWH_FCT_Z_CAP_TIME_ENTRIES_V@PPMDBLINK SRC
      WHERE
         (
            TABLE1.Z_CAP_TIME_ENTRIES_KEY = SRC.Z_CAP_TIME_ENTRIES_KEY
            AND TABLE1.PERIOD_KEY = SRC.PERIOD_KEY
            AND SRC.CLARITY_UPDATED_DATE >= TO_DATE('2025/11/19 02:00:02', 'yyyy/MM/dd HH24:mi:ss')
         )
   )
   OR NOT EXISTS
   (
      SELECT
         1
      FROM
         DWH_FCT_Z_CAP_TIME_ENTRIES_V@PPMDBLINK SRC2
      WHERE
         TABLE1.Z_CAP_TIME_ENTRIES_KEY = SRC2.Z_CAP_TIME_ENTRIES_KEY
         AND TABLE1.PERIOD_KEY = SRC2.PERIOD_KEY
   )
   AND ROWNUM <= 50000

Environment

Clarity 16.3.2, 16.3.3, 16.4.0

Cause

DE179499

Resolution

Fixed in Clarity 16.4.1