R12: PA: Performance issues with PRC: Update Project Performance Data process (Doc ID 1292449.1)

Last updated on DECEMBER 18, 2017

Applies to:

Symptoms

Performance issues with the PRC: Update Project Performance Data process. 

The performance may be inconsistent, e.g., there are times when the process might in 10-15 minutes, but others it may take 2-3 hours to complete even for single project.

Problem statements per the trace files:

INSERT /*+ parallel(PJI_FP_AGGR_PJP1) */ INTO PJI_FP_AGGR_PJP1 PARTITION(P1)
( WORKER_ID, RECORD_TYPE, PRG_LEVEL, LINE_TYPE, PROJECT_ID, PROJECT_ORG_ID,
PROJECT_ORGANIZATION_ID, PROJECT_ELEMENT_ID, TIME_ID, PERIOD_TYPE_ID,
CALENDAR_TYPE, RBS_AGGR_LEVEL, WBS_ROLLUP_FLAG, PRG_ROLLUP_FLAG,
CURR_RECORD_TYPE_ID, CURRENCY_CODE, RBS_ELEMENT_ID, RBS_VERSION_ID,
....
= SUP_FIN_VER.STATUS_CODE (+) AND PJP1_I.SUP_EMT_ID =
SUP_WPA.PROJ_ELEMENT_ID (+) AND 'N' = SUP_WPA.WP_ENABLE_VERSION_FLAG (+) AND
(PJP1_I.SUP_ID IS NULL OR (PJP1_I.SUP_ID IS NOT NULL AND
(SUP_FIN_VER.PROJECT_ID IS NOT NULL OR SUP_WPA.PROJ_ELEMENT_ID IS NOT
NULL)))

and

MERGE INTO PJI_FP_XBS_ACCUM_F FACT USING ( SELECT /*+ leading(tmp)
rowid(tmp)
*/ TMP.WORKER_ID , TMP.PROJECT_ID , TMP.PROJECT_ORG_ID ,
.....
PJP1.CUSTOM9 , PJP1.CUSTOM10 , PJP1.CUSTOM11 , PJP1.CUSTOM12 ,
PJP1.CUSTOM13 , PJP1.CUSTOM14 , PJP1.CUSTOM15 , PJP1.PLAN_TYPE_CODE )

Changes

 On 11i the issue was noticed to occur after applying the following patches:

• 7860027
• 10105518
• 10220250
• 10222224

Cause