DEFERRED WORKFLOW BOOKING PERFORMANCE AFTER UPGRADE FROM 11I10 TO 12.2.5 (Doc ID 2239496.1)

Last updated on APRIL 04, 2025

Applies to:

Symptoms

On : 12.2.5 version, Transaction Booking

ACTUAL BEHAVIOR
---------------
After upgrading from 11.5.10 to 12.2.5, the Workflow Background process to book orders is running extremely slow. It is taking over 500 minutes.
It appears the following workflow background processes are long running:
OEOH, , , Y, N, N,
OEOH, , , Y, N, N,
, , , Y, N, N,
, , , Y, N, N

When a user books the order from the forms, there is no performance issue.

EXPECTED BEHAVIOR
-----------------------
Expect for the Workflow Background process to complete in several minutes

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
Run the Workflow Background process to book large volumes of orders


Issue verified:

Based on the tkprof file "Trace.out", the following update is taking a lot of
time.
UPDATE /*+ index(a qpprod QP_PREQ_QUAL_TMP_N1) dynamic_sampling(1) */
QP_PREQ_QUAL_TMP A SET PRICING_STATUS_CODE = :B3 WHERE PRICING_STATUS_CODE =
:B2 AND PRICING_PHASE_ID = :B1 AND LIST_LINE_ID = -1 AND NOT EXISTS (SELECT
/*+ NO_UNNEST leading (c)
use_nl (c qpprod)
index (qpprod, QP_PREQ_LINE_ATTRS_TMP_N6)
index(c qp_pricing_attributes_n13) */ 'x' FROM
QP_NPREQ_LINE_ATTRS_TMP QPPROD, QP_PRICING_ATTRIBUTES C WHERE
C.LIST_HEADER_ID = A.LIST_HEADER_ID AND C.PRICING_PHASE_ID = :B1 AND
QPPROD.CONTEXT = C.PRODUCT_ATTRIBUTE_CONTEXT AND QPPROD.ATTRIBUTE =
C.PRODUCT_ATTRIBUTE AND QPPROD.VALUE_FROM = C.PRODUCT_ATTR_VALUE AND
QPPROD.PRICING_STATUS_CODE = :B2 AND C.QUALIFICATION_IND IN (:B9 , :B8 , :B7
, :B6 ) AND QPPROD.ATTRIBUTE_TYPE = :B5 AND C.EXCLUDER_FLAG = :B4 AND ROWNUM
= 1 ) AND NOT EXISTS (SELECT 'x' FROM QP_LIST_LINES D WHERE D.LIST_HEADER_ID
= A.LIST_HEADER_ID AND D.QUALIFICATION_IND IN (:B11 ,:B10 ) AND
D.PRICING_PHASE_ID = :B1 AND ROWNUM=1)
.
This is causing the booking of an order to take a lot of time.

Cause

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