Performance Degradation While Running "merge into w_codes_resource_d" Statement During The ODI ETL Process For A P6 Datasource

Last updated on DECEMBER 08, 2017

Applies to:

Symptoms

Performance degradation with the following merge statement which runs during ODI ETL:

From:

• PROC_AFTER_LOADING_PKG_CODES_RESOURCE > 1 - Load Codes Resource HD > 4 -
• Procedure - Load Codes Resource HD - Load resource Codes HD:

Statement: 

BEGIN
    scd.load2scd_odi('W_CODES_RESOURCE_D','W_CODES_RESOURCE_HD',
#PRIMAVERA_ANALYTICS_PROJECT.VAR_DATASOURCE_ID,
'#PRIMAVERA_ANALYTICS_PROJECT.VAR_EFFECTIVE_START_DATE');
END;

Which calls:

MERGE INTO W_CODES_RESOURCE_D M  USING (select  T.RESOURCE_OBJECT_ID,
T.datasource_id FROM W_CODES_RESOURCE_HD T, W_CODES_RESOURCE_D S WHERE
S.RESOURCE_OBJECT_ID = T.RESOURCE_OBJECT_ID AND (((1=1) AND
((NVL(S.CODE_VALUE_01, '<UNDEFINED>') in ('<NO VALUE>','<UNDEFINED>') AND
(T.CODE_VALUE_01 is not null AND T.CODE_VALUE_01 <> NVL(S.CODE_VALUE_01,
'<UNDEFINED>'))) OR (NVL(S.CODE_DESCR_01, '<UNDEFINED>') in ('<NO
VALUE>','<UNDEFINED>') AND (T.CODE_DESCR_01 is not null AND T.CODE_DESCR_01
<> NVL(S.CODE_DESCR_01, '<UNDEFINED>'))) OR (NVL(S.CODE_VALUE_02,
'<UNDEFINED>') in ('<NO VALUE>','<UNDEFINED>') AND (T.CODE_VALUE_02 is not
null AND T.CODE_VALUE_02 <> NVL(S.CODE_VALUE_02, '<UNDEFINED>'))) OR
(NVL(S.CODE_DESCR_02, '<UNDEFINED>') in ('<NO VALUE>','<UNDEFINED>') AND
(T.CODE_DESCR_02 is not null AND T.CODE_DESCR_02 <> NVL(S.CODE_DESCR_02,
'<UNDEFINED>'))) OR (NVL(S.CODE_VALUE_03, '<UNDEFINED>') in ('<NO
VALUE>','<UNDEFINED>') AND (T.CODE_VALUE_03 is not null AND T.CODE_VALUE_03
<> NVL(S.CODE_VALUE_03, '<UNDEFINED>'))) OR (NVL(S.CODE_DESCR_03,
'<UNDEFINED>') in ('<NO VALUE>','<UNDEFINED>') AND (T.CODE_DESCR_03 is not
null AND T.CODE_DESCR_03 <> NVL(S.CODE_DESCR_03, '<UNDEFINED>'))) OR
(NVL(S.CODE_VALUE_04, '<UNDEFINED>') in ('<NO VALUE>','<UNDEFINED>') AND
(T.CODE_VALUE_04 is not null AND T.CODE_VALUE_04 <> NVL(S.CODE_VALUE_04,
'<UNDEFINED>'))) OR (NVL(S.CODE_DESCR_04, '<UNDEFINED>') in ('<NO
VALUE>','<UNDEFINED>') AND (T.CODE_DESCR_04 is not null AND T.CODE_DESCR_04
<> NVL(S.CODE_DESCR_04, '<UNDEFINED>'))) OR (NVL(S.CODE_VALUE_05,
'<UNDEFINED>') in ('<NO VALUE>','<UNDEFINED>') AND (T.CODE_VALUE_05 is not
null AND T.CODE_VALUE_05 <> NVL(S.CODE_VALUE_05, '<UNDEFINED>'))) OR
(NVL(S.CODE_DESCR_05, '<UNDEFINED>') in ('<NO VALUE>','<UNDEFINED>') AND
(T.CODE_DESCR_05 is not null AND T.CODE_DESCR_05 <> NVL(S.CODE_DESCR_05,
'<UNDEFINED>'))) OR (NVL(S.CODE_VALUE_06, '<UNDEFINED>') in ('<NO
VALUE>','<UNDEFINED>') AND (T.CODE_VALUE_06 is not null AND T.CODE_VALUE_06
<> NVL(S.CODE_VALUE_06, '<UNDEFINED>')))..<Repeats For 240 Columns>))) AND
S.datasource_id = T.datasource_id AND T.datasource_id = 1 AND T.current_flag
= 1) T ON ( M.RESOURCE_OBJECT_ID = T.RESOURCE_OBJECT_ID AND M.datasource_id =
T.datasource_id) WHEN MATCHED THEN  UPDATE SET M.scd_flag = 'Y'

The statement can run for hours in addition to requiring a large amount of memory for the database server.

Cause