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:

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

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