Load DWH job stuck on table DWH_FIN_TRANSACTION

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Load Data Warehouse (DWH) Incremental job is hung in processing status intermittently. Incompatible jobs will then show in waiting mode and will not run.

The job is stuck on the DWH_FIN_TRANSACTION table

Environment

Release : 15.8.1

Component : CLARITY DATA WAREHOUSE

Cause

This issue is due to a slow processing foreign data wrapper.

The issue is with SP DWH_TMP_RECORD_KEYS_LOAD which queries PPA_WIP records and inserts them in a temp table in Clarity

This works (not stuck) but is extremely slow due to the foreign data wrapper (db link). It's fetching 100 records at a time for the insert.

Resolution

Fixed in 15.9. To address the issue we did a change to the fetch size on the database server and changed to 50000. We noticed the change when Incremental is about 10k of transactions per job. Once this was applied, the performance dramatically improved. 

Additional Information

See also KB: Data Warehouse (DWH) known issues