PASDUC PRC: Create And Distribute Burden Transactions Slow Performance (Doc ID 2722978.1)

Last updated on NOVEMBER 15, 2022

Applies to:

Symptoms

Slow performance with the process, PRC: Create and Distribute Burden Transactions.  Problem statement in the AWR report.

SELECT /*+ LEADING(p) INDEX (p, PA_PROJECTS_U2) */ P.PROJECT_ID , P.SEGMENT1
, P.ORG_ID , UPPER(NVL(PT.BURDEN_ACCOUNT_FLAG, 'N')) BURDEN_ACCOUNT_FLAG ,
PT.BURDEN_SUM_DEST_PROJECT_ID DEST_PROJECT_ID , PT.BURDEN_SUM_DEST_TASK_ID
DEST_TASK_ID , UPPER(PT.BURDEN_AMT_DISPLAY_METHOD) BURDEN_AMT_DISPLAY_METHOD
FROM PA_PROJECTS_ALL P, PA_PROJECT_TYPES_ALL PT WHERE PT.PROJECT_TYPE =
P.PROJECT_TYPE AND P.SEGMENT1 BETWEEN :B2 AND :B1 AND (
PT.BURDEN_AMT_DISPLAY_METHOD IN ('D', 'd') OR PT.BURDEN_AMT_DISPLAY_METHOD IN
('S', 's') AND PT.BURDEN_ACCOUNT_FLAG IN ('Y', 'y')) AND PT.ORG_ID = P.ORG_ID
AND PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(P.PROJECT_STATUS_CODE,
'GENERATE_BURDEN') = 'Y' AND EXISTS ( SELECT /*+ NO_UNNEST INDEX (cdl,
PA_COST_DISTRIBUTION_LINES_N10) */ 1 FROM PA_COST_DISTRIBUTION_LINES CDL
WHERE CDL.LINE_TYPE||'' = 'R' AND CDL.AMOUNT <> 0 AND
CDL.BURDEN_SUM_SOURCE_RUN_ID = :B4 AND CDL.PROJECT_ID = P.PROJECT_ID AND (
:B3 IS NULL OR EXISTS (SELECT 1 FROM PA_EXPENDITU RE_ITEMS EI WHERE
EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID AND EI.EXPENDITURE_ITEM_DATE
<= :B5 ) ) )

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.