Performance Problem and System Hanging When Trying to Complete Batches and Batch Steps (Doc ID 2359303.1)

Last updated on DECEMBER 15, 2022

Applies to:

Symptoms

On : 12.1.1 version, Batches

When attempting to complete some batches or batch steps, it sometimes takes a long time, or the system hangs or
the following error occurs.

ERROR
-----------------------
Unexpected User-Defined Exception occurred

This seems to then leave data locked

Looking at a tkprof from a trace from completing a batch, the following long-running queries are seen:

SELECT NVL (C.ACTUAL_QTY, 0) ACTUAL_QTY, C.INVENTORY_ITEM_ID ITEM_ID,
 C.DTL_UM UOM
FROM
GME_BATCH_STEP_ITEMS A, GME_MATERIAL_DETAILS C WHERE 1 = 1 AND C.BATCH_ID =
 A.BATCH_ID AND A.BATCHSTEP_ID = :B1 AND C.MATERIAL_DETAIL_ID =
 A.MATERIAL_DETAIL_ID AND C.LINE_TYPE = 2 AND BY_PRODUCT_TYPE = 'W'

and

INSERT INTO FND_LOG_MESSAGES ( ECID_ID, ECID_SEQ, CALLSTACK, ERRORSTACK,
 MODULE, LOG_LEVEL, MESSAGE_TEXT, SESSION_ID, USER_ID, TIMESTAMP,
 LOG_SEQUENCE, ENCODED, NODE, NODE_IP_ADDRESS, PROCESS_ID, JVM_ID, THREAD_ID,
  AUDSID, DB_INSTANCE, TRANSACTION_CONTEXT_ID )
VALUES
( SYS_CONTEXT('USERENV', 'ECID_ID'), SYS_CONTEXT('USERENV', 'ECID_SEQ'),
 :B16 , :B15 , SUBSTRB(:B14 ,1,255), :B13 , SUBSTRB(:B12 , 1, 4000), :B11 ,
 NVL(:B10 , -1), SYSDATE, FND_LOG_MESSAGES_S.NEXTVAL, :B9 , SUBSTRB(:B8 ,1,
 60), SUBSTRB(:B7 ,1,30), SUBSTRB(:B6 ,1,120), SUBSTRB(:B5 ,1,120),
 SUBSTRB(:B4 ,1,120), :B3 , :B2 , :B1 ) RETURNING LOG_SEQUENCE INTO :O0

and

SELECT SUM (GME_YIELD_CALCULATION_PVT.CONV_UOM (MMT.INVENTORY_ITEM_ID,
 DECODE(MTLN.LOT_NUMBER,NULL,MMT.TRANSACTION_QUANTITY,
 MTLN.TRANSACTION_QUANTITY) * -1, MMT.TRANSACTION_UOM, GBS.STEP_QTY_UM)) XX
FROM
MTL_OBJECT_GENEALOGY MOB, MTL_MATERIAL_TRANSACTIONS MMT,
 MTL_TRANSACTION_LOT_NUMBERS MTLN, GME_BATCH_STEP_ITEMS GBSI,
 GME_MATERIAL_DETAILS GMD, GME_BATCH_STEPS GBS WHERE MOB.ORIGIN_TXN_ID (+) =
 MMT.TRANSACTION_ID AND MMT.TRX_SOURCE_LINE_ID = GBSI.MATERIAL_DETAIL_ID AND
 GBSI.MATERIAL_DETAIL_ID = GMD.MATERIAL_DETAIL_ID AND GBSI.BATCHSTEP_ID =
 GBS.BATCHSTEP_ID AND MMT.TRANSACTION_ID = :B2 AND MMT.TRANSACTION_ID =
 MTLN.TRANSACTION_ID(+) AND NVL(MTLN.LOT_NUMBER(+), '-') = NVL(:B1 , '-')
 AND MMT.TRANSACTION_TYPE_ID IN (35,43) AND GMD.LINE_TYPE = - 1

and

SELECT DISTINCT BATCH_ID,LEVEL
FROM
GME_BATCH_GENEALOGY START WITH BATCH_ID = :B1 CONNECT BY NOCYCLE PRIOR
 BATCH_ID = PARENT_BATCH_ID ORDER BY LEVEL


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Complete a batch or step

This only occurs with some batches, however.

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot complete those batches or steps.

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.