PAXALGAT - PRC: GENERATE ALLOCATIONS TRANSACTIONS HAS PERFORMANCE ISSUES (Doc ID 967474.1)

Last updated on AUGUST 01, 2016

Applies to:

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

Symptoms

On a 12.0.6 instance, the PRC: Generate Allocation Transactions has slow performance. The most expensive query is :

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 /*+ ORDERED INDEX (prad, PA_RESOURCE_ACCUM_DETAILS_N2) */
PART.RUN_ID ,PART.RULE_ID ,PART.LINE_NUM ,PART.PROJECT_ID ,PART.TASK_ID ,
PARR.RESOURCE_LIST_MEMBER_ID ,NVL(SUM( DECODE (:B9 , '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 PTA.PROJECT_ID = PART.PROJECT_ID AND
PTA.TASK_ID = PART.TASK_ID AND PART.RUN_ID = :B8 AND PARR.RUN_ID =
PART.RUN_ID AND PARR.MEMBER_TYPE = 'B' AND PART.PROJECT_ID = :B7 AND
PART.EXCLUDE_FLAG = 'N' AND PTA.PA_PERIOD = :B6 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 )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 139 5597.51 5756.54 267221 605766203 555 864
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 140 5597.52 5756.54 267221 605766203 555 864

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