DEFERRED WORKFLOW BOOKING PERFORMANCE AFTER UPGRADE FROM 11I10 TO 12.2.5
(Doc ID 2239496.1)
Last updated on FEBRUARY 23, 2019
Applies to:Oracle Advanced Pricing - Version 12.2.5 and later
Information in this document applies to any platform.
On : 12.2.5 version, Transaction Booking
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.
Expect for the Workflow Background process to complete in several minutes
The issue can be reproduced at will with the following steps:
Run the Workflow Background process to book large volumes of orders
Based on the tkprof file "Trace.out", the following update is taking a lot of
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.
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