My Oracle Support Banner

PJM Cost Collection Manager Running For Over An Hour. Unable To Transfer WIP Resource Costs To PA (Doc ID 2597573.1)

Last updated on OCTOBER 15, 2019

Applies to:

Oracle Project Manufacturing - Version 12.2.8 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.8 version, SUPPORT

ACTUAL BEHAVIOR
---------------
PJM Cost Collection Manager Running for over an hour. Unable to transfer WIP Resource Costs to Projects


The Trace shows the following slow query

UPDATE /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N13) */
 MTL_MATERIAL_TRANSACTIONS MMT SET MMT.PM_COST_COLLECTED = NULL,
 MMT.LAST_UPDATE_DATE = SYSDATE, MMT.LAST_UPDATED_BY = :B7 ,
 MMT.LAST_UPDATE_LOGIN = :B6 , MMT.REQUEST_ID = :B5 ,
 MMT.PROGRAM_APPLICATION_ID = :B4 , MMT.PROGRAM_ID = :B3 ,
 MMT.PROGRAM_UPDATE_DATE = SYSDATE
WHERE
NOT EXISTS ( SELECT /*+ NO_UNNEST*/NULL FROM CST_PM_MATL_TXN_V CPMTV WHERE
 CPMTV.TRANSACTION_ID = MMT.TRANSACTION_ID ) AND MMT.ORGANIZATION_ID = :B2
 AND MMT.TRANSACTION_DATE <= ((TRUNC(SYSDATE) - :B1 ) + 0.99999) AND
 MMT.COSTED_FLAG IS NULL AND MMT.PM_COST_COLLECTED = 'N'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 9463.10 9463.59 3 214894953 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 9463.10 9463.59 3 214894953 0 0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 44 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
In the base it is showing:
 ....
1413384025 1413384025 1413384025 INDEX RANGE SCAN MTL_MATERIAL_TRANSACTIONS_N13 (cr=6767105 pr=0 pw=0 time=508565569 us cost=0 size=0 card=1)(object id 117551)
....

Changes

 

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!


In this Document
Symptoms
Changes
Cause
Solution
References


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