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
Release : 15.8.1
Component : CLARITY DATA WAREHOUSE
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.
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.
See also KB: Data Warehouse (DWH) known issues