ETL Process Stalling on ODI step "Set effective to dates" is taking long time to complete

(Doc ID 2300847.1)

Last updated on AUGUST 25, 2017

Applies to:

Business Intelligence Applications Consumer - Version 11.1.1.10.1 and later
Information in this document applies to any platform.

Symptoms

Customer mentioned that ETL stalling on OOTB BI Apps ETL when the following code is executed
/* Set effective to date on flow table for full load */
update OBI11G_DW2.I$_213198550_2 I0
set EFFECTIVE_TO_DT =
 (select min(X.EFFECTIVE_FROM_DT)
  from OBI11G_DW2.I$_213198550_2 X
  where X.EMPLID = I0.EMPLID
  and X.EMPL_RCD = I0.EMPL_RCD
  and X.DATASOURCE_NUM_ID = I0.DATASOURCE_NUM_ID
  and X.EFFECTIVE_FROM_DT > I0.EFFECTIVE_FROM_DT)
where exists
 (select 1
  from OBI11G_DW2.I$_213198550_2 X
  where X.EMPLID = I0.EMPLID
  and X.EMPL_RCD = I0.EMPL_RCD
  and X.DATASOURCE_NUM_ID = I0.DATASOURCE_NUM_ID
  and X.EFFECTIVE_FROM_DT > I0.EFFECTIVE_FROM_DT)

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms