R12: PA: PASDUC - PRC: Create and Distribute Burden Transactions Performance Problem After Database Upgrade

(Doc ID 1948680.1)

Last updated on FEBRUARY 21, 2016

Applies to:

Oracle Project Costing - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

The PASDUC, PRC: Create and Distribute Burden Transactions process encounters slow performance.  The most expensive queries are:

begin PA_BURDEN_COSTING . CREATE_BURDEN_EXPENDITURE_ITEM (
 :start_project_number:i_start_project_number ,
 :end_project_number:i_end_project_number , :request_id:i_request_id ,
 :end_date:i_end_date , :status:i_status , :stage:i_stage , :run_id:i_run_id
 ) ; END ;

and


SELECT 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_STATU
 S_CODE, 'GENERATE_BURDEN') = 'Y' AND (EXISTS (SELECT /*+ no_unnest
 index(CDL PA_COST_DISTRIBUTION_LINES_N10 ) */ 1 FROM
 PA_COST_DISTRIBUTION_LINES CDL, PA_EXPENDITURE_ITEMS EI 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 EI.PROJECT_ID = P.PROJECT_ID AND
 EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID AND
 EI.EXPENDITURE_ITEM_DATE  (SELECT 1 FROM PA_COST_DISTRIBUTION_LINES CDL, PA_EXPENDITURE_ITEMS EI
 WHERE CDL.LINE_TYPE = 'R' AND CDL.AMOUNT <> 0 AND
 CDL.BURDEN_SUM_SOURCE_RUN_ID >0 AND NVL(CDL.REVERSED_FLAG,'N') = 'N' AND
 CDL.LINE_NUM_REVERSED IS NULL AND EI.ADJUSTMENT_TYPE ='BURDEN_RESUMMARIZE'
 AND CDL.PROJECT_ID = P.PROJECT_ID AND EI.PROJECT_ID = P.PROJECT_ID AND
 EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID AND
 EI.EXPENDITURE_ITEM_DATE


Changes

 Database upgrade.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms