R12.2.x Performance Issue In Oracle Pricing For Entering Online Orders (Doc ID 2244353.1)

Last updated on AUGUST 02, 2017

Applies to:

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

Symptoms

On : 12.2.5 version, Pricing Engine

ACTUAL BEHAVIOR
---------------
Performance issue in Oracle Pricing for Online Orders and change in seeded code

During performance testing, we have identified Online Order Creation Business Process is running long and not meeting the SLA. Upon analysis, we found a query in the seeded package "QP_PREQ_GRP" having high execution time. We have modified the Index and observed performance improvement in next load tests.

I've added the tkprof files, query details, business process for your reference and request oracle support to validate and give blessings on these changes

Run time details from Peak Load performance testing
Target SLA: 43.41sec
Achieved response time: 59.07seconds

after modified the query in seeded package, the runtime was reduced to less than 40sec at business process level during peak load testing

Note:
we have modified the same package for another performance issues in a batch job and it is being tracked through SR #3-14107672101

EXPECTED BEHAVIOR
-----------------------
Expect performance to be better in R12.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1 Login to OTC system
2 Navigate to MHE Order Entry Level 3 – USA > Sales Order
3 Provide Customer Number in ‘Cust #’ field, Order Type in ‘Order Type’ field, PO Amount in ‘PO Amount’ field in DFF and Customer PO in Customer PO’ field and save the record
5 Navigate to the lines, Enter the Ordered Item, Quantity and Navigate down. Enter the next line and quantity and arrow down again
6 Press ‘Book’ Button after placing all the order lines
7 If the following error message pops up:
“A hold prevents booking of this order.” then
Press ‘Continue’
navigate to: Order Information Tab -> Action -> Release Holds -> Press OK -> Enter ‘Configuration is valid.’ In the Reason field -> Press OK -> Press ‘Release’

In case of any other message, ignore and proceed

8 A note ‘APP-ONT-251123: Hold Released Successfully’ will appear. Press OK
9 Press ‘Book’ Button
10 Press ‘OK’ when ‘the message ‘ Order has been booked’ pops up. In case of any other message, ignore and proceed



Verified the issue in the log file as noted below:

LOG FILE
-----------------------
Filename = trc_tkp_LUSER121.zip
See the following error:

SELECT /*+ ORDERED NO_EXPAND USE_NL(qpq qplatq qph) index
(qplatq,QP_PREQ_LINE_ATTRS_TMP_N6)
index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7)
l_mini_outer_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID
LIST_LINE_ID, QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT,
QPQ.HEADER_QUALS_EXIST_FLAG, QPQ.SEARCH_IND, :B9 VALIDATED_FLAG, :B9
APPLIED_FLAG, :B10 ATTRIBUTE_LEVEL, :B4 ATTRIBUTE_TYPE, QPLATQ.CONTEXT ,
QPLATQ.ATTRIBUTE , QPLATQ.VALUE_FROM , QPQ.QUALIFIER_GROUPING_NO
QUALIFIER_GROUPING_NO , QPQ.QUALIFIER_PRECEDENCE , QPQ.QUALIFIER_DATATYPE,
QPQ.QUALIFIER_ATTR_VALUE , QPQ.QUALIFIER_ATTR_VALUE_TO ,
QPQ.START_DATE_ACTIVE, QPQ.END_DATE_ACTIVE, :B9 PRICING_ATTR_FLAG,
QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE, QPH.ASK_FOR_FLAG,
QPLATQ.PRICING_STATUS_CODE, 'OHQ1' , NULL LINE_INDEX, NULL
PRICING_EFFECTIVE_DATE, QPH.START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST,
QPH.END_DATE_ACTIVE_FIRST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_CODE, :B1
....ND QPQ.COMPARISON_OPERATOR_CODE = 'NOT =' AND QPQ.QUALIFIER_ATTR_VALUE <>
QPLATQ.VALUE_FROM AND QPQ.SEARCH_IND = 2 AND ((QPH.CURRENCY_CODE IS NOT NULL
AND QPH.CURRENCY_CODE = :B8 ) OR QPH.CURRENCY_CODE IS NULL) AND
(NVL(QPH.END_DATE_ACTIVE,:B7 ) >= :B7 AND NVL(QPH.START_DATE_ACTIVE, :B6 ) <=
:B6 ) AND (NVL(QPQ.END_DATE_ACTIVE,:B7 ) >= :B7 AND
NVL(QPQ.START_DATE_ACTIVE, :B6 ) <= :B6 ) AND QPH.LIST_TYPE_CODE NOT IN
('PRL','AGR') AND QPH.ACTIVE_FLAG = :B5 AND QPQ.ACTIVE_FLAG = :B5 AND
QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID AND QPLATQ.LINE_INDEX > 0 AND
QPLATQ.DISTINCT_QUALIFIER_FLAG = :B5 AND QPLATQ.PRICING_STATUS_CODE = :B2 AND
QPLATQ.ATTRIBUTE_TYPE = :B4 AND QPRS.REQUEST_TYPE_CODE = :B3 AND
QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE AND
((NVL(QPLATQ.DERIVED_QUALIFIER_FLAG,'N') = 'N') OR
(QPLATQ.DERIVED_QUALIFIER_FLAG = 'Y' AND QPQ.QUALIFY_HIER_DESCENDENTS_FLAG =
'Y'))

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