Performance Degradation While Running "MERGE INTO W_CODES_RESOURCE_D" or "MERGE INTO W_CODES_ACTIVITY_D" Statement During The ETL Process (ODI Or TRADITIONAL) For A P6 EPPM Datasource

Last updated on JULY 12, 2018

Applies to:

Symptoms

WHEN EXECUTING AN ODI ETL

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 executes:

• 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'

Note:

• The statement can run for hours in addition to requiring a large amount of memory for the database server.
• This issue can also occur while running the same scd.load2scd_odi package procedure for executing a similar MERGE INTO W_CODES_ACTIVITY_D... from the associated W_CODES_ACTIVITY_HD table.

WHEN EXECUTING A TRADITIONAL ETL

Performance degradation with one or both of the following merge statements which run during a P6 EPPM ETL:

From:

• w_codes_resource_hd.sql
• w_codes_activity_hd.sql

Statement:

• scd.load2scd('W_CODES_RESOURCE_D','W_CODES_RESOURCE_HD', vsrc_id);
• scd.load2scd('W_CODES_ACTIVITY_D','W_CODES_ACTIVITY_HD', vsrc_id);

Which executes:

• 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'

• MERGE INTO W_CODES_ACTIVITY_D M USING....<SIMILAR STATEMENT AS THE W_CODES_RESOURCE_D MERGE STATEMENT>

Changes

Cause