PRC: Update Project Summary Amounts Report Is Taking Exponentially Loner To Run (Doc ID 2075883.1)

Last updated on NOVEMBER 15, 2022

Applies to:

Symptoms

PRC: Update Project Summary Amounts report is taking exponentially longer to run.  A task which used to take 2-3 hours is now taking over 12 hours to run.

Most expensive query:

INSERT INTO PA_PJM_PO_COMMITMENTS_TMP (PROJECT_ID ,TASK_ID ,PO_NUMBER ,
  PO_DISTRIBUTION_ID ,PO_HEADER_ID ,ITEM_DESCRIPTION ,EXPENDITURE_ITEM_DATE ,
  PO_LINE ,CREATION_DATE ,APPROVED_DATE ,REQUESTOR_NAME ,BUYER_NAME ,
  APPROVED_FLAG ,PROMISED_DATE ,NEED_BY_DATE ,EXPENDITURE_ORGANIZATION_ID ,
  VENDOR_ID ,VENDOR_NAME ,EXPENDITURE_TYPE ,EXPENDITURE_CATEGORY ,
  REVENUE_CATEGORY ,UNIT_OF_MEASURE ,UNIT_PRICE ,AMOUNT_OUTSTANDING_DELIVERY ,
  QUANTITY_OUTSTANDING_DELIVERY ,QUANTITY_ORDERED ,AMOUNT_ORDERED ,
  ORIGINAL_QUANTITY_ORDERED ,ORIGINAL_AMOUNT_ORDERED ,QUANTITY_CANCELLED ,
  AMOUNT_CANCELLED ,QUANTITY_DELIVERED ,QUANTITY_INVOICED ,AMOUNT_INVOICED ,
  QUANTITY_OUTSTANDING_INVOICE ,AMOUNT_OUTSTANDING_INVOICE ,
  QUANTITY_OVERBILLED ,AMOUNT_OVERBILLED ,DENOM_CURRENCY_CODE ,
  DENOM_AMT_OUTSTANDING_DELIVERY ,ACCT_CURRENCY_CODE ,ACCT_RATE_DATE ,
  ACCT_RATE_TYPE ,ACCT_EXCHANGE_RATE ,INVENTORY_ITEM_ID ,INVENTORY_ITEM_NAME ,
  UOM_CODE ,WIP_RESOURCE_ID ,WIP_RESOURCE_NAME ) SELECT /*+ leading(prj)
  PUSH_PRED(pmppd) */ PMPPD.PROJECT_ID, PMPPD.TASK_ID, PMPPD.PO_NUMBER,
  PMPPD.PO_DISTRIBUTION_ID, PMPPD.PO_HEADER_ID, PMPPD.ITEM_DESCRIPTION,
  PMPPD.EXPENDITURE_ITEM_DATE, PMPPD.PO_LINE, PMPPD.CREATION_DATE,
  PMPPD.APPROVED_DATE, PMPPD.REQUESTOR_NAME, PMPPD.BUYER_NAME,
  PMPPD.APPROVED_FLAG, PMPPD.PROMISED_DATE, PMPPD.NEED_BY_DATE,
  PMPPD.EXPENDITURE_ORGANIZATION_ID, PMPPD.VENDOR_ID, PMPPD.VENDOR_NAME,
  PMPPD.EXPENDITURE_TYPE, PMPPD.EXPENDITURE_CATEGORY, PMPPD.REVENUE_CATEGORY,
  PMPPD.UNIT_OF_MEASURE, PMPPD.UNIT_PRICE, PMPPD.AMOUNT_OUTSTANDING_DELIVERY,
  PMPPD.QUANTITY_OUTSTANDING_DELIVERY, PMPPD.QUANTITY_ORDERED,
  PMPPD.AMOUNT_ORDERED, PMPPD.ORIGINAL_QUANTITY_ORDERED,
  PMPPD.ORIGINAL_AMOUNT_ORDERED, PMPPD.QUANTITY_CANCELLED,
  PMPPD.AMOUNT_CANCELLED, PMPPD.QUANTITY_DELIVERED, PMPPD.QUANTITY_INVOICED,
  PMPPD.AMOUNT_INVOICED, PMPPD.QUANTITY_OUTSTANDING_INVOICE,
  PMPPD.AMOUNT_OUTSTANDING_INVOICE, PMPPD.QUANTITY_OVERBILLED,
  PMPPD.AMOUNT_OVERBILLED, PMPPD.DENOM_CURRENCY_CODE,
  PMPPD.DENOM_AMT_OUTSTANDING_DELIVERY, PMPPD.ACCT_CURRENCY_CODE,
  PMPPD.ACCT_RATE_DATE, PMPPD.ACCT_RATE_TYPE, PMPPD.ACCT_EXCHANGE_RATE,
  PMPPD.INVENTORY_ITEM_ID, PMPPD.INVENTORY_ITEM_NAME, PMPPD.UOM_CODE,
  PMPPD.WIP_RESOURCE_ID, PMPPD.WIP_RESOURCE_NAME FROM PJM_PO_COMMITMENTS_V
  PMPPD, PA_PROJ_SUMM_TMP1 PRJ WHERE PMPPD.PROJECT_ID = PRJ.PROJECT_ID

INSERT INTO PA_PJM_REQ_COMMITMENTS_TMP ( PROJECT_ID ,TASK_ID ,REQ_NUMBER ,
  REQ_DISTRIBUTION_ID ,REQUISITION_HEADER_ID ,ITEM_DESCRIPTION ,
  EXPENDITURE_ITEM_DATE ,REQ_LINE ,CREATION_DATE ,REQUESTOR_NAME ,
  APPROVED_FLAG ,NEED_BY_DATE ,EXPENDITURE_ORGANIZATION_ID ,VENDOR_ID ,
  VENDOR_NAME ,EXPENDITURE_TYPE ,EXPENDITURE_CATEGORY ,REVENUE_CATEGORY ,
  UNIT_OF_MEASURE ,UNIT_PRICE ,AMOUNT ,QUANTITY ,DENOM_CURRENCY_CODE ,
  DENOM_AMOUNT ,ACCT_CURRENCY_CODE ,ACCT_RATE_DATE ,ACCT_RATE_TYPE ,
  ACCT_EXCHANGE_RATE ,INVENTORY_ITEM_ID ,INVENTORY_ITEM_NAME ,UOM_CODE ,
  WIP_RESOURCE_ID ,WIP_RESOURCE_NAME ) SELECT /*+ leading(prj) */
  PMPRD.PROJECT_ID ,PMPRD.TASK_ID ,PMPRD.REQ_NUMBER ,
  PMPRD.REQ_DISTRIBUTION_ID ,PMPRD.REQUISITION_HEADER_ID ,
  PMPRD.ITEM_DESCRIPTION ,PMPRD.EXPENDITURE_ITEM_DATE ,PMPRD.REQ_LINE ,
  PMPRD.CREATION_DATE ,PMPRD.REQUESTOR_NAME ,PMPRD.APPROVED_FLAG ,
  PMPRD.NEED_BY_DATE ,PMPRD.EXPENDITURE_ORGANIZATION_ID ,PMPRD.VENDOR_ID ,
  PMPRD.VENDOR_NAME ,PMPRD.EXPENDITURE_TYPE ,PMPRD.EXPENDITURE_CATEGORY ,
  PMPRD.REVENUE_CATEGORY ,PMPRD.UNIT_OF_MEASURE ,PMPRD.UNIT_PRICE ,
  PMPRD.AMOUNT ,PMPRD.QUANTITY ,PMPRD.DENOM_CURRENCY_CODE ,PMPRD.DENOM_AMOUNT
  ,PMPRD.ACCT_CURRENCY_CODE ,PMPRD.ACCT_RATE_DATE ,PMPRD.ACCT_RATE_TYPE ,
  PMPRD.ACCT_EXCHANGE_RATE ,PMPRD.INVENTORY_ITEM_ID ,
  PMPRD.INVENTORY_ITEM_NAME ,PMPRD.UOM_CODE ,PMPRD.WIP_RESOURCE_ID ,
  PMPRD.WIP_RESOURCE_NAME FROM PJM_REQ_COMMITMENTS_V PMPRD ,PA_PROJ_SUMM_TMP1
  PRJ WHERE PMPRD.PROJECT_ID = PRJ.PROJECT_ID

Applied PATCH 21223589 but that did not resolve the issue.

Changes

Cause

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