My Oracle Support Banner

Performance Bottlenecks Found After Patch 27582319:R12.QP.C Calling Price Request API (Doc ID 2653010.1)

Last updated on APRIL 06, 2020

Applies to:

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

Symptoms

On : 12.2.6 version, Pricing Engine

ACTUAL BEHAVIOR
Find that code calling API price_request has poor performance, even after patch 27582319.

EXPECTED BEHAVIOR
For the same one customer and one price list this program need to complete ~ 2min


EXPENSIVE STATEMENT(S)

#1

UPDATE QP_NPREQ_LINES_TMP C SET PRIMARY_QUALIFIERS_MATCH_FLAG = 'Y'
WHERE
 EXISTS( SELECT /*+ index(a QP_QUALIFIERS_N7) */ 'x' FROM
  QP_NPREQ_LINE_ATTRS_TMP B ,QP_QUALIFIERS A WHERE B.CONTEXT =
  A.QUALIFIER_CONTEXT AND B.ATTRIBUTE = A.QUALIFIER_ATTRIBUTE AND
  B.VALUE_FROM <> A.QUALIFIER_ATTR_VALUE AND A.COMPARISON_OPERATOR_CODE =
  'NOT =' AND B.PRICING_STATUS_CODE = :B4 AND B.ATTRIBUTE_TYPE = :B3 AND
  TRUNC(C.PRICING_EFFECTIVE_DATE) BETWEEN NVL(A.START_DATE_ACTIVE,
  TRUNC(C.PRICING_EFFECTIVE_DATE)) AND NVL(A.END_DATE_ACTIVE,
  TRUNC(C.PRICING_EFFECTIVE_DATE)) AND A.LIST_HEADER_ID =
  C.PRICE_LIST_HEADER_ID AND A.ACTIVE_FLAG = :B2 AND B.LINE_INDEX =
  C.LINE_INDEX AND C.VALIDATED_FLAG = :B1 ) AND C.VALIDATED_FLAG = :B1



********************************************************************************

#2

UPDATE QP_NPREQ_LINES_TMP C SET PRIMARY_QUALIFIERS_MATCH_FLAG = 'Y'
WHERE
 EXISTS( SELECT /*+ index(a QP_QUALIFIERS_N7) */ 'x' FROM
  QP_NPREQ_LINE_ATTRS_TMP B ,QP_QUALIFIERS A WHERE B.CONTEXT =
  A.QUALIFIER_CONTEXT AND B.ATTRIBUTE = A.QUALIFIER_ATTRIBUTE AND
  A.COMPARISON_OPERATOR_CODE = 'BETWEEN' AND B.PRICING_STATUS_CODE = :B4 AND
  B.ATTRIBUTE_TYPE = :B3 AND TRUNC(C.PRICING_EFFECTIVE_DATE) BETWEEN
  NVL(A.START_DATE_ACTIVE, TRUNC(C.PRICING_EFFECTIVE_DATE)) AND
  NVL(A.END_DATE_ACTIVE,TRUNC(C.PRICING_EFFECTIVE_DATE)) AND A.LIST_HEADER_ID
  = C.PRICE_LIST_HEADER_ID AND A.ACTIVE_FLAG = :B2 AND B.LINE_INDEX =
  C.LINE_INDEX AND C.VALIDATED_FLAG = :B1 ) AND C.VALIDATED_FLAG = :B1



OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
-- elapsed 415.44

STEPS
The issue can be reproduced at will with the following steps:
1. run custom code which calls API price_request.

File Versions:
  $QP_TOP/patch/115/odf/qptab.odf 120.51.12020000.19
  $QP_TOP/patch/115/sql/QPXGPREB.pls 120.132.12020000.88
  $QP_TOP/patch/115/sql/QPXGPRES.pls 120.11.12020000.12
  $QP_TOP/patch/115/xdf/QP_PREQ_LINE_ATTRS_TMP_T.xdf 120.6.12020000.8
  $QP_TOP/patch/115/xdf/QP_PREQ_QUAL_TMP_T.xdf 120.5.12020000.4

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.