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

Last updated on NOVEMBER 07, 2024

Applies to:

Symptoms

Find 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


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

 Use OE_ORDER_PUB.PROCESS_ORDER API to create orders

Cause

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