My Oracle Support Banner

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

Last updated on SEPTEMBER 22, 2018

Applies to:

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

Symptoms


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:  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

See the following expensive statement:

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'))

Changes

 

Cause

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
Symptoms
Changes
Cause
Solution
References


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