My Oracle Support Banner

R12 PAXALGAT: After Upgrade to 12.2.5, PRC: Generate Allocations Transactions has Performance Issues (Doc ID 2285737.1)

Last updated on AUGUST 20, 2018

Applies to:

Oracle Project Costing - Version 12.1.3 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.

Symptoms

After upgrade to 12.2.5 from 11i, there is a performance issue with the following statement:

INSERT INTO PA_ALLOC_RUN_BASIS_DET ( RUN_ID , RULE_ID , LINE_NUM , PROJECT_ID
, TASK_ID , RESOURCE_LIST_MEMBER_ID , AMOUNT , LINE_PERCENT , CREATION_DATE
, CREATED_BY , LAST_UPDATE_DATE , LAST_UPDATED_BY , LAST_UPDATE_LOGIN ) (
SELECT PART.RUN_ID ,PART.RULE_ID ,PART.LINE_NUM ,PART.PROJECT_ID ,
PART.TASK_ID ,PARR.RESOURCE_LIST_MEMBER_ID ,NVL(SUM( DECODE (:B8 ,
'TOT_RAW_COST', NVL(PTA.TOT_RAW_COST,0) +NVL( PTA.I_TOT_RAW_COST,0),
'TOT_BILLABLE_RAW_COST', NVL(PTA.TOT_BILLABLE_RAW_COST,0) +NVL(
PTA.I_TOT_BILLABLE_RAW_COST,0), 'TOT_BILLABLE_BURDENED_COST',
NVL(PTA.TOT_BILLABLE_BURDENED_COST,0) +NVL(
PTA.I_TOT_BILLABLE_BURDENED_COST,0), 'TOT_BURDENED_COST',
NVL(PTA.TOT_BURDENED_COST,0) +NVL(PTA.I_TOT_BURDENED_COST,0),
'TOT_LABOR_HOURS', NVL(PTA.TOT_LABOR_HOURS,0) +NVL(PTA.I_TOT_LABOR_HOURS,0),
'TOT_QUANTITY', NVL(PTA.TOT_QUANTITY,0) +NVL(PTA.I_TOT_QUANTITY,0), 0 )),0)
AMOUNT ,PART.LINE_PERCENT ,:B5 ,:B4 ,:B3 ,:B2 ,:B1 FROM
PA_ALLOC_RUN_TARGETS PART, PA_ALLOC_RUN_RESOURCE_DET PARR,
PA_RESOURCE_ACCUM_DETAILS PRAD, PA_TXN_ACCUM PTA WHERE PTA.TXN_ACCUM_ID =
PRAD.TXN_ACCUM_ID AND PRAD.RESOURCE_LIST_MEMBER_ID =
PARR.RESOURCE_LIST_MEMBER_ID AND PRAD.PROJECT_ID = PART.PROJECT_ID AND
PRAD.TASK_ID = PART.TASK_ID AND PART.RUN_ID = :B7 AND PARR.RUN_ID =
PART.RUN_ID AND PARR.MEMBER_TYPE = 'B' AND PART.PROJECT_ID = :B6 AND
PART.EXCLUDE_FLAG = 'N' AND EXISTS (SELECT /*+ NO_UNNEST */ GL.PERIOD_NAME
FROM GL_PERIOD_STATUSES GL, PA_IMPLEMENTATIONS IMP WHERE PTA.GL_PERIOD =
GL.PERIOD_NAME AND GL.SET_OF_BOOKS_ID = IMP.SET_OF_BOOKS_ID AND
GL.APPLICATION_ID = PA_PERIOD_PROCESS_PKG.APPLICATION_ID AND
GL.ADJUSTMENT_PERIOD_FLAG = 'N' AND GL.CLOSING_STATUS IN ('C','F','O','P')
AND GL.END_DATE BETWEEN :B10 AND :B9 ) GROUP BY PART.RUN_ID ,PART.RULE_ID ,
PART.LINE_NUM ,PART.PROJECT_ID ,PART.TASK_ID ,PARR.RESOURCE_LIST_MEMBER_ID ,
PART.LINE_PERCENT ,:B5 ,:B4 ,:B3 ,:B2 ,:B1 )

The process does not complete. In 11i it took 30 mins to process this same number of projects.



Cause

To view full details, 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 a vibrant support community of peers and Oracle experts.