Slow Performance on PRC: Update Project Performance Data (Doc ID 2687579.1)

Last updated on NOVEMBER 28, 2023

Applies to:

Goal

Upgrading to EBS R12.2.9.  Encountering slow performance for PRC: Update Project Performance Data.

Top 3 poor performing statements per AWR report based on "SQL ordered by Elapsed Time (Global)".

BEGIN PJI_PJP_SUM_MAIN.SUMMARIZE(:errbuf, :rc, :A0, :A1, :A2, :A3, :A4, :A5, :A6, :A7, :A8, :A9, :A10); END;

SELECT PCT.ROWID, PCT.CMT_LINE_ID, PCT.PROJECT_ID, PCT.TASK_ID, PA_UTILS.GETWEEKENDING(PCT.EXPENDITURE_ITEM_DATE) WEEK_ENDING_DATE, LAST_DAY(PCT.EXPENDITURE_ITEM_DATE) MONTH_ENDING_DATE, PCT.PA_PERIOD, PCT.GL_PERIOD, PCT.ORGANIZATION_ID, PCT.VENDOR_ID, PCT.EXPENDITURE_TYPE, PCT.EXPENDITURE_CATEGORY, PCT.REVENUE_CATEGORY, PCT.SYSTEM_LINKAGE_FUNCTION, PCT.CMT_IND_COMPILED_SET_ID, PCT.EXPENDITURE_ITEM_DATE, PCT.DENOM_CURRENCY_CODE, PCT.DENOM_RAW_COST, PCT.DENOM_BURDENED_COST, PCT.ACCT_CURRENCY_CODE, PCT.ACCT_RATE_DATE, PCT.ACCT_RATE_TYPE, PCT.ACCT_EXCHANGE_RATE, PCT.ACCT_RAW_COST, PCT.ACCT_BURDENED_COST, PCT.RECEIPT_CURRENCY_CODE, PCT.RECEIPT_CURRENCY_AMOUNT, PCT.RECEIPT_EXCHANGE_RATE FROM PA_COMMITMENT_TXNS PCT WHERE PCT.PROJECT_ID = :B1

INSERT /*+ parallel(PJI_AC_AGGR_PJP1) */ INTO PJI_AC_AGGR_PJP1 PARTITION(P8) ( WORKER_ID, RECORD_TYPE, PRG_LEVEL, PROJECT_ID, PROJECT_ORG_ID, PROJECT_ORGANIZATION_ID, PROJECT_ELEMENT_ID, TIME_ID, PERIOD_TYPE_ID, CALENDAR_TYPE, WBS_ROLLUP_FLAG, PRG_ROLLUP_FLAG,
.
.
.
JP1_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)))


 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.