Closing Thousands Of EAM or WIP Discrete Jobs, Or Users Staying In The WIP Discrete Jobs Form For Hours, Causes Contention/Performance/Errors With the Planning Manager

(Doc ID 1584162.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Materials Requirement Planning - Version 12.1.3 and later
Oracle Enterprise Asset Management - Version 12.1.3 and later
Oracle Work in Process - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Planning Manager in PROD

Instead of closing a few EAM or WIP jobs at a time, customer closes thousands of jobs (more than 15000) with the following concurrent requests

EAMCDCL module: Close EAM WorkOrders
WICDCL module: Close Discrete Jobs

However they run into performance problems when closing this many jobs - sometimes it can take 30+ hours or more and can error out

They see this sql in the backend which appears to be the offender:

SELECT JOBS.ROWID, JOBS.WIP_ENTITY_ID, JOBS.ORGANIZATION_ID FROM
 WIP_REQUIREMENT_OPERATIONS OPS, WIP_DISCRETE_JOBS JOBS, MRP_REL
IEF_INTERFACE MRP WHERE OPS.WIP_ENTITY_ID (+) = JOBS.WIP_ENTITY_
ID AND OPS.ORGANIZATION_ID (+) = JOBS.ORGANIZATION_ID AND MRP.DI
SPOSITION_TYPE = :B7 AND MRP.RELIEF_TYPE = :B6 AND MRP.REQUEST_I
D = :B5 AND MRP.PROCESS_STATUS = :B4 AND MRP.ERROR_MESSAGE IS NU
LL AND MRP.INVENTORY_ITEM_ID = DECODE(:B3 ,:B2 , MRP.INVENTORY_I
TEM_ID, :B3 ) AND MRP.ORGANIZATION_ID = DECODE(:B1 ,:B2 ,MRP.ORG
ANIZATION_ID, :B1 ) AND JOBS.PRIMARY_ITEM_ID = MRP.INVENTORY_ITE
M_ID AND JOBS.ORGANIZATION_ID = MRP.ORGANIZATION_ID AND JOBS.WIP
_ENTITY_ID = MRP.DISPOSITION_ID FOR UPDATE OF JOBS.MPS_NET_QUANT
ITY, OPS.MPS_REQUIRED_QUANTITY ORDER BY JOBS.ORGANIZATION_ID, JO
BS.WIP_ENTITY_ID

In checking locks on table wip_discrete_jobs it appears there's contention between the close job programs, the Planning Manager (MRCRLF) and a spawned child concurrent request from the Planning Manager called the MPS Relief Worker while they are processing data in tables wip_discrete_jobs and mrp_relief_interface (records with relief_type = 2, MPS relief) at the same time

EXPECTED BEHAVIOR
-----------------------
No performance and dead lock issue.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Responsibility: Enterprise Asset Management
2. Maintenance Workbench > Mass Close or Close Discrete Jobs Program in WIP
3. when there are thousands of jobs to close, the programs can hang for hours and/or fail


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