R12: PAXACMPT PRC: Update Project Summary Amounts Performance Issue (Doc ID 1677980.1)

Last updated on NOVEMBER 18, 2022

Applies to:

Symptoms

Users encounter slow performance when running PAXACMPT PRC: Update Project Summary Amounts.  The most expensive query is:

INSERT INTO PA_COMMITMENT_TXNS_TMP (PROJECT_ID, TASK_ID, TRANSACTION_SOURCE,
LINE_TYPE, CMT_NUMBER, CMT_DISTRIBUTION_ID, CMT_HEADER_ID, DESCRIPTION,
EXPENDITURE_ITEM_DATE, PA_PERIOD, GL_PERIOD, CMT_LINE_NUMBER,
CMT_CREATION_DATE, CMT_APPROVED_DATE, CMT_REQUESTOR_NAME, CMT_BUYER_NAME,
CMT_APPROVED_FLAG, CMT_PROMISED_DATE, CMT_NEED_BY_DATE, ORGANIZATION_ID,
VENDOR_ID, VENDOR_NAME, EXPENDITURE_TYPE, EXPENDITURE_CATEGORY,
REVENUE_CATEGORY, SYSTEM_LINKAGE_FUNCTION, UNIT_OF_MEASURE, UNIT_PRICE,
CMT_IND_COMPILED_SET_ID, TOT_CMT_QUANTITY, QUANTITY_ORDERED,
AMOUNT_ORDERED,
ORIGINAL_QUANTITY_ORDERED, ORIGINAL_AMOUNT_ORDERED, QUANTITY_CANCELLED,
AMOUNT_CANCELLED, QUANTITY_DELIVERED, QUANTITY_INVOICED, AMOUNT_INVOICED,
QUANTITY_OUTSTANDING_DELIVERY, AMOUNT_OUTSTANDING_DELIVERY,
QUANTITY_OUTSTANDING_INVOICE, AMOUNT_OUTSTANDING_INVOICE,
QUANTITY_OVERBILLED, AMOUNT_OVERBILLED, ORIGINAL_TXN_REFERENCE1,
ORIGINAL_TXN_REFERENCE2, ORIGINAL_TXN_REFERENCE3, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, ACCT_RAW_COST,
ACCT_BURDENED_COST, DENOM_CURRENCY_CODE, DENOM_RAW_COST,
DENOM_BURDENED_COST, ACCT_CURRENCY_CODE, ACCT_RATE_DATE, ACCT_RATE_TYPE,
ACCT_EXCHANGE_RATE, RECEIPT_CURRENCY_CODE, RECEIPT_CURRENCY_AMOUNT,
RECEIPT_EXCHANGE_RATE, INVENTORY_ITEM_ID, UOM_CODE, WIP_RESOURCE_ID,
RESOURCE_CLASS ) SELECT /*+ push_pred(PCTV) */ PCTV.PROJECT_ID,
PCTV.TASK_ID, PCTV.TRANSACTION_SOURCE, PCTV.LINE_TYPE, PCTV.CMT_NUMBER,
PCTV.CMT_DISTRIBUTION_ID, PCTV.CMT_HEADER_ID, PCTV.DESCRIPTION,
PCTV.EXPENDITURE_ITEM_DATE, PCTV.PA_PERIOD, PCTV.GL_PERIOD,
PCTV.CMT_LINE_NUMBER, PCTV.CMT_CREATION_DATE, PCTV.CMT_APPROVED_DATE,
PCTV.CMT_REQUESTOR_NAME, PCTV.CMT_BUYER_NAME, PCTV.CMT_APPROVED_FLAG,
PCTV.CMT_PROMISED_DATE, PCTV.CMT_NEED_BY_DATE, PCTV.ORGANIZATION_ID,
PCTV.VENDOR_ID, PCTV.VENDOR_NAME, PCTV.EXPENDITURE_TYPE,
PCTV.EXPENDITURE_CATEGORY, PCTV.REVENUE_CATEGORY,
PCTV.SYSTEM_LINKAGE_FUNCTION, PCTV.UNIT_OF_MEASURE, PCTV.UNIT_PRICE,
PCTV.CMT_IND_COMPILED_SET_ID, PCTV.TOT_CMT_QUANTITY, PCTV.QUANTITY_ORDERED,
PCTV.AMOUNT_ORDERED, PCTV.ORIGINAL_QUANTITY_ORDERED,
PCTV.ORIGINAL_AMOUNT_ORDERED, PCTV.QUANTITY_CANCELLED,
PCTV.AMOUNT_CANCELLED, PCTV.QUANTITY_DELIVERED, PCTV.QUANTITY_INVOICED,
PCTV.AMOUNT_INVOICED, PCTV.QUANTITY_OUTSTANDING_DELIVERY,
PCTV.AMOUNT_OUTSTANDING_DELIVERY, PCTV.QUANTITY_OUTSTANDING_INVOICE,
PCTV.AMOUNT_OUTSTANDING_INVOICE, PCTV.QUANTITY_OVERBILLED,
PCTV.AMOUNT_OVERBILLED, PCTV.ORIGINAL_TXN_REFERENCE1,
PCTV.ORIGINAL_TXN_REFERENCE2, PCTV.ORIGINAL_TXN_REFERENCE3, SYSDATE, :B7 ,
SYSDATE, :B6 , :B5 , :B4 , :B3 , :B2 , NULL, PCTV.ACCT_RAW_COST,
PCTV.ACCT_BURDENED_COST, PCTV.DENOM_CURRENCY_CODE, PCTV.DENOM_RAW_COST,
PCTV.DENOM_BURDENED_COST, PCTV.ACCT_CURRENCY_CODE, PCTV.ACCT_RATE_DATE,
PCTV.ACCT_RATE_TYPE, PCTV.ACCT_EXCHANGE_RATE, PCTV.RECEIPT_CURRENCY_CODE,
PCTV.RECEIPT_CURRENCY_AMOUNT, PCTV.RECEIPT_EXCHANGE_RATE,
PCTV.INVENTORY_ITEM_ID, PCTV.UOM_CODE, PCTV.WIP_RESOURCE_ID,
PCTV.RESOURCE_CLASS FROM PA_COMMITMENTS_V PCTV WHERE
NVL(PCTV.SYSTEM_LINKAGE_FUNCTION,'X') = NVL(:B1 ,
NVL(PCTV.SYSTEM_LINKAGE_FUNCTION,'X'))

Cause

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