Performance Issue With OE_ORDER_PUB.PROCESS_ORDER API (Doc ID 1952590.1)

Last updated on JULY 26, 2017

Applies to:

Oracle Advanced Pricing - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Transaction Control

ACTUAL BEHAVIOR
---------------
Performance Issue with OE_ORDER_PUB.PROCESS_ORDER API

The OE_ORDER_PUB.PROCESS_ORDER API is taking a long time to complete.
It takes around 2hrs to book 1000 order line order.
From tkproff we see call to pricing takes time.
Following expensive query is seen form tkproff

SELECT /*+ ORDERED USE_NL(b) index(a qp_preq_line_attrs_tmp_N5)
 header_level_qual_attrs_cur */ A.LIST_HEADER_ID, A.LIST_LINE_ID,
 A.LINE_INDEX, A.GROUPING_NUMBER, A.LINE_DETAIL_INDEX, COUNT(DISTINCT
 CONTEXT || ATTRIBUTE || SETUP_VALUE_FROM ) NO_OF_QUALIFIERS_IN_GRP
FROM
QP_NPREQ_LINE_ATTRS_TMP A,QP_NPREQ_LDETS_TMP B WHERE A.PRICING_PHASE_ID =
 :B6 AND A.ATTRIBUTE_TYPE = :B5 AND A.QUALIFIER_TYPE = :B4 AND
 A.LINE_DETAIL_INDEX = B.LINE_DETAIL_INDEX AND B.PRICING_STATUS_CODE = :B3
 AND A.PRICING_STATUS_CODE IN (:B3 ,:B2 ,:B1 ) GROUP BY A.LIST_HEADER_ID,
 A.LINE_INDEX,A.LINE_DETAIL_INDEX,A.LIST_LINE_ID,A.GROUPING_NUMBER ORDER BY
 A.LIST_HEADER_ID, A.LINE_INDEX,A.LINE_DETAIL_INDEX,A.LIST_LINE_ID,
 A.GROUPING_NUMBER


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 12 0.01 0.01 0 0 0 0
Fetch 814 1198.28 1201.15 461 81008921 0 80360
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 828 1198.29 1201.16 461 81008921 0 80360


SELECT /*+ ORDERED USE_NL(b) index(a qp_preq_line_attrs_tmp_N5)
 line_level_qual_attrs_cur2 */ A.LIST_HEADER_ID, A.LIST_LINE_ID,
 A.LINE_INDEX, A.GROUPING_NUMBER, A.LINE_DETAIL_INDEX, COUNT(DISTINCT
 CONTEXT || ATTRIBUTE || SETUP_VALUE_FROM ) NO_OF_QUALIFIERS_IN_GRP
FROM
QP_NPREQ_LINE_ATTRS_TMP A,QP_NPREQ_LDETS_TMP B WHERE A.PRICING_PHASE_ID =
 :B6 AND A.ATTRIBUTE_TYPE = :B5 AND A.QUALIFIER_TYPE = :B4 AND
 A.PRICING_STATUS_CODE IN (:B1 ,:B3 ,:B2 ) AND A.LINE_DETAIL_INDEX =
 B.LINE_DETAIL_INDEX AND B.PRICING_STATUS_CODE = :B1 GROUP BY
 A.LIST_HEADER_ID, A.LINE_INDEX,A.LINE_DETAIL_INDEX,A.LIST_LINE_ID,
 A.GROUPING_NUMBER ORDER BY A.LIST_HEADER_ID, A.LINE_INDEX,
 A.LINE_DETAIL_INDEX,A.LIST_LINE_ID,A.GROUPING_NUMBER


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 1153.27 1156.47 0 80832964 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 1153.27 1156.48 0 80832964 0 0


EXPECTED BEHAVIOR
-----------------------
Need to optimize performance with pricing

STEPS
-----------------------
The issue can be reproduced at will with the following steps:

 Use OE_ORDER_PUB.PROCESS_ORDER API to create orders



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