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
(Doc ID 2337374.1)
Last updated on MARCH 30, 2019
Applies to:
Primavera Analytics - Version 17.11.0.0 and laterPrimavera Data Warehouse - Version 17.7 and later
Primavera Analytics Cloud Service - Version 17.11.0.0 to 18.1 [Release 17.11 to 18.1]
Information in this document applies to any platform.
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>
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |
References |