R12.1.x Poor Performance with Order Import

(Doc ID 2316885.1)

Last updated on NOVEMBER 06, 2017

Applies to:

Oracle Advanced Pricing - Version 12.1 and later
Oracle Order Management - Version 12.1 and later
Information in this document applies to any platform.
QP_RESOLVE_INCOMPATABILITY_PVT QPXVINCB.pls
QP_PREQ_GRP QPXGPREB.pls

Symptoms



ACTUAL BEHAVIOR


Poor performance with order Import of 100 lines of a sales order; it takes about 21 minutes.
Issue started after the application of <Patch 25918432:R12.QP.B>.
It is more than 50% slower in response after the patch.


EXPECTED BEHAVIOR


Expects same performance as before patch was applied which would be around 4.5 minutes.


MOST EXPENSIVE STATEMENT SHOWN

SELECT /*+ leading(QPLINES,QPLAT_PROD,QPA,QPQ) USE_NL(QPQ QPLAT_PROD QPA QPL
 QPH) INDEX(QPA QP_PRICING_ATTRIBUTES_N5) index(qph qp_list_headers_b_n7)
 l_sec_list_cur */ QPLAT_PROD.LINE_INDEX, QPL.LIST_LINE_ID,
 QPL.ARITHMETIC_OPERATOR, QPL.OPERAND, QPL.PRICING_GROUP_SEQUENCE,
 QPL.LIST_LINE_TYPE_CODE, QPL.PRICING_PHASE_ID, QPL.AUTOMATIC_FLAG,
 QPL.INCOMPATIBILITY_GRP_CODE, QPL.PRICE_BY_FORMULA_ID,
 QPL.PRODUCT_PRECEDENCE, QPL.PRIMARY_UOM_FLAG, QPL.MODIFIER_LEVEL_CODE,
 QPL.LIST_LINE_NO, QPA.PRODUCT_UOM_CODE, QPA.EXCLUDER_FLAG,
 QPH.LIST_TYPE_CODE, QCDT.ROUNDING_FACTOR, QPLAT_PROD.LINE_DETAIL_INDEX,
 QPH.LIST_HEADER_ID LIST_HEADER_ID, QPLAT_PROD.CONTEXT
 PRODUCT_ATTRIBUTE_CONTEXT, QPLAT_PROD.ATTRIBUTE PRODUCT_ATTRIBUTE,
 QPLAT_PROD.VALUE_FROM PRODUCT_ATTR_VALUE_FROM, QPLAT_PROD.ATTRIBUTE_LEVEL
 PRODUCT_ATTRIBUTE_LEVEL, QPLAT_PROD.ATTRIBUTE_TYPE PRODUCT_ATTRIBUTE_TYPE,
 QPLAT_PROD.APPLIED_FLAG, NULL PRODUCT_OPERATOR_TYPE, NULL
 PRICING_ATTRIBUTE_CONTEXT, NULL PRICING_ATTRIBUTE, NULL
 PRICING_ATTR_VALUE_FROM, NULL PRICING_SETUP_VALUE_FROM, NULL
 PRICING_SETUP_VALUE_TO, NULL PRICING_ATTRIBUTE_LEVEL, NULL
 PRICING_ATTRIBUTE_TYPE, NULL PRICING_OPERATOR_TYPE, NULL
 PRICING_ATTRIBUTE_DATATYPE, NULL QUALIFIER_ATTRIBUTE_CONTEXT, NULL
 QUALIFIER_ATTRIBUTE, NULL QUALIFIER_ATTR_VALUE_FROM, NULL
 QUALIFIER_PRICING_ATTR_FLAG, TO_NUMBER(NULL) QUALIFIER_GROUPING_NO, NULL
 QUALIFIER_DATATYPE, NULL SETUP_VALUE_FROM, NULL SETUP_VALUE_TO, NULL
 QUALIFIER_OPERATOR_TYPE, QPQ.QUALIFIER_PRECEDENCE QUALIFIER_PRECEDENCE,
 QCDT.CURRENCY_DETAIL_ID, QCDT.CURRENCY_HEADER_ID,
 QCDT.SELLING_ROUNDING_FACTOR, QPLINES.CURRENCY_CODE ORDER_CURRENCY,
 QPLINES.PRICING_EFFECTIVE_DATE, QPH.CURRENCY_CODE BASE_CURRENCY_CODE ,
 QPL.BREAK_UOM_CODE ,QPL.BREAK_UOM_CONTEXT ,QPL.BREAK_UOM_ATTRIBUTE ,NULL
 QUALIFIER_TYPE ,QPLAT_PROD.DERIVED_QUALIFIER_FLAG
FROM
QP_NPREQ_LINES_TMP QPLINES, QP_QUALIFIERS QPQ, QP_NPREQ_LINE_ATTRS_TMP
 QPLAT_PROD, QP_PRICING_ATTRIBUTES QPA, QP_LIST_LINES QPL, QP_LIST_HEADERS_B
 QPH, QP_PRICE_REQ_SOURCES_V QPRS, QP_CURRENCY_DETAILS QCDT WHERE
 QPQ.QUALIFIER_CONTEXT = :B14 AND QPQ.QUALIFIER_ATTRIBUTE = :B13 AND
 QPQ.QUALIFIER_ATTR_VALUE = TO_CHAR(QPLINES.PRICE_LIST_HEADER_ID) AND
...
 (INSTR(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) AND QPLAT_Q.ATTRIBUTE_TYPE =
 :B19 AND (QPSQ.QUALIFIER_CONTEXT <> :B14 OR QPSQ.QUALIFIER_ATTRIBUTE <>
 :B13 ) AND QPLAT_Q.CONTEXT = QPSQ.QUALIFIER_CONTEXT AND QPLAT_Q.ATTRIBUTE =
 QPSQ.QUALIFIER_ATTRIBUTE AND QPSQ.LIST_HEADER_ID = QPQ.LIST_HEADER_ID AND
 QPSQ.ACTIVE_FLAG = :B3 AND ((QPLAT_Q.VALUE_FROM = QPSQ.QUALIFIER_ATTR_VALUE
 AND QPSQ.COMPARISON_OPERATOR_CODE = '=') OR (QPLAT_Q.VALUE_FROM <>
 QPSQ.QUALIFIER_ATTR_VALUE AND QPSQ.COMPARISON_OPERATOR_CODE = 'NOT =') OR
 QPSQ.COMPARISON_OPERATOR_CODE = :B16 ) AND QPLAT_Q.LINE_INDEX =
 QPLINES.LINE_INDEX AND QPLINES.PRICING_EFFECTIVE_DATE BETWEEN
 NVL(QPSQ.START_DATE_ACTIVE, QPLINES.PRICING_EFFECTIVE_DATE) AND
 NVL(QPSQ.END_DATE_ACTIVE,QPLINES.PRICING_EFFECTIVE_DATE) AND
 QPLAT_PROD.LINE_INDEX = QPLAT_Q.LINE_INDEX AND QPLAT_Q.PRICING_STATUS_CODE =
  :B2 AND :B7 = :B3 AND ((NVL(QPLAT_Q.DERIVED_QUALIFIER_FLAG,'N')='N') OR
 (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND
 QPSQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) ORDER BY 1,2


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.07 0 0 2 0
Execute 1 0.29 0.92 0 0 0 0
Fetch 1 159.67 542.36 12411 103554899 0 25388
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 159.99 543.36 12411 103554899 2 25388


STEPS

The issue can be reproduced at will with the following steps:
1. Populate the OM Interface tables.
2. Run order import



Changes

 Applied <Patch 25918432:R12.QP.B> for Duplicated Price List Lines: Failed to Resolve Incompatibility on Secondary Price List,
which provided the following files and versions or higher.
       QPXGPREB.pls 120.70.12010000.123
       QPXGPRES.pls 120.5.12010000.16
       QPXVINCB.pls 120.14.12010000.20
       QPXVINCS.pls 120.1.12010000.1

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