DE59155 (GCP Only) - Load Data Warehouse is slow on DWH_INV_TASK with a RowExclusiveLock on DWH_TME_ENTRY

book

Article ID: 205581

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

STEPS TO REPRODUCE

  1. Run Load Data Warehouse - Incremental on a schedule

Expected Results: The job to run well and consistently

 

Actual Results: Intermittently, the DWH_INV_TASK tables takes longer than expected (can be about 30-40 min)

When checking with DBA we can see a RowExclusiveLock on DWH_TME_ENTRY update

Slow Query:

DECLARE\r +| | | | | | V_SQL_TEXT text;\r+| | | | | | | (1 row)

DO $$\r DECLARE\r V_SQL_TEXT text;\r V_EXCEPTION varchar(4000);\r V_SQLERRM varchar(4000);\r V_SQLSTATE varchar(5);\r BEGIN\r update DWH_TME_ENTRY set TASK_ID = ( select TASK_ID from DWH_INV_TASK lkp inner join ( select TIMEENTRY_KEY as dim_pk, TASK_KEY as dim_key, TASK_ID as dim_string, 'en' as language_code from DWH_TME_ENTRY dim ) iv on (iv.dim_key = lkp.TASK_KEY ) where DWH_TME_ENTRY.TIMEENTRY_KEY= iv.dim_pk and lkp.dw_u pdated_date >= to_timestamp('2020-12-22 08:33:33.000000000', 'yyyy/mm/dd HH24:MI:SS') ) where exists ( select TASK_ID from DWH_INV_TASK lkp inner join ( s elect TIMEENTRY_KEY as dim_pk, TASK_KEY as dim_key, TASK_ID as dim_string, 'en' as language_code from DWH_TME_ENTRY dim ) iv on (iv.dim_key = lkp.TASK_KEY ) where DWH_TME_ENTRY.TIMEENTRY_KEY= iv.dim_pk and lkp.dw_updated_date >= to_timestamp('2020-12-22 08:33:33.000000000', 'yyyy/mm/dd HH24:MI:SS') ) ;\r+ EXCEPTION WHEN OTHERS THEN\r

 

Workaround: None, wait for the query to complete

Environment

Release : 15.9

Component : CA PPM SAAS DATA WAREHOUSE

Resolution

This is logged as DE59155, currently in review by Engineering