OEXOEORD: Performance Issue After Order Management RUP Patching (Doc ID 1271577.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Order Management - Version 11.5.10.2 and later
Information in this document applies to any platform.
**Checked for relevance on 23-MAY-2013***

Symptoms


=== ODM Issue Clarification ===

On :  11.5.10.2 version, Pricing, Discounting & Charges


Find extremely poor performance in production
which is preventing book of orders resulting in
backlog worth $10M.

Sometimes it is the performance for configurator back to line
Other times booking order appears really long (30-40 minutes for 37 line order !!!)
Yesterday, coming back to line from configurator took 2.5 hours on a 17 line order.


=========================================
Patches applied on 10/16 (not all pre-req's shown)
=========================================


8332175_README.txt - Consolidation of Advanced Pricing 11.5.10 dependencies
8393924_README.txt - March 2010, Advanced Pricing (11.5.10) Cumulative Patch
9859265_README.txt - 30th AUGUST 2010, ORDER MANAGEMENT (11.5.10) CUMULATIVE PATCH

It appears that QP patch delivered the following new QP XDF files which have created new indexes which have caused problems.

-rwxr-xr-x 1 oracle dba  19588 Oct 16 23:13 QP_NPREQ_LINE_PATRNS_T.xdf
-rwxr-xr-x 1 oracle dba  18589 Oct 16 23:13 QP_NPREQ_PATRN_PHASES_T.xdf
-rwxr-xr-x 1 oracle dba 117633 Oct 16 23:13 QP_PREQ_LINES_TMP_T.xdf
-rwxr-xr-x 1 oracle dba  92959 Oct 16 23:13 QP_PREQ_LINE_ATTRS_TMP_T.xdf
-rwxr-xr-x 1 oracle dba  60717 Oct 16 23:13 QP_PREQ_PATRN_QUAL_T.xdf
-rwxr-xr-x 1 oracle dba  76051 Oct 17 00:13 QP_PREQ_QUAL_TMP_T.xdf

The following indexes have been created by the new XDF files as is seen in the adwork*.log files

CREATE INDEX "QP"."QP_NPREQ_LINE_PATRNS_T_N1" ON "QP"."QP_NPREQ_LINE_PATRNS_T" ("REQUEST_ID", "PATTERN_ID", "HASH_KEY", "LINE_INDEX")
CREATE INDEX "QP"."QP_NPREQ_LINE_PATRNS_T_N2" ON "QP"."QP_NPREQ_LINE_PATRNS_T" ("REQUEST_ID", "LINE_INDEX")
CREATE INDEX "QP"."QP_NPREQ_PATRN_PHASES_TMP_N1" ON "QP"."QP_NPREQ_PATRN_PHASES_T" ("REQUEST_ID", "PRICING_PHASE_ID", "SEGMENT_INDEX", "PATTERN_ID")
CREATE INDEX "QP"."QP_PREQ_LINE_ATTRS_TMP_N6" ON "QP"."QP_PREQ_LINE_ATTRS_TMP_T" ("REQUEST_ID", "LINE_INDEX", "SEGMENT_ID")
CREATE INDEX "QP"."QP_PREQ_LINE_ATTRS_TMP_N7" ON "QP"."QP_PREQ_LINE_ATTRS_TMP_T" ("REQUEST_ID", "SEGMENT_ID", "LINE_INDEX", "VALUE_FROM")
CREATE INDEX "QP"."QP_PREQ_LINE_ATTRS_TMP_N8" ON "QP"."QP_PREQ_LINE_ATTRS_TMP_T" ("REQUEST_ID", "LINE_INDEX", "LIST_HEADER_ID", "LIST_LINE_ID", "GROUPING_NUMBER")
CREATE INDEX "QP"."QP_PREQ_LINES_TMP_N4" ON "QP"."QP_PREQ_LINES_TMP_T" ("REQUEST_ID", "LINE_INDEX")
CREATE INDEX "QP"."QP_PREQ_PATRN_QUAL_T_N1" ON "QP"."QP_PREQ_PATRN_QUAL_T" ("REQUEST_ID", "PRICING_PHASE_ID", "LIST_HEADER_ID", "LIST_LINE_ID")
CREATE INDEX "QP"."QP_PREQ_PATRN_QUAL_T_N2" ON "QP"."QP_PREQ_PATRN_QUAL_T" ("LINE_INDEX", "LIST_HEADER_ID", "LIST_LINE_ID")
CREATE INDEX "QP"."QP_PREQ_PATRN_QUAL_T_N3" ON "QP"."QP_PREQ_PATRN_QUAL_T" ("PRICING_PHASE_ID", "STAGE", "EQ_FLAG", "GROUPING_NO")
CREATE INDEX "QP"."QP_PREQ_QUAL_TMP_N6" ON "QP"."QP_PREQ_QUAL_TMP_T" ("REQUEST_ID", "PRICING_STATUS_CODE", "PRICING_PHASE_ID")

===================================================================================================
Customer has dropped two Advanced Pricing indexes (QP.QP_PREQ_LINES_TMP_N4 and QP.QP_PREQ_QUAL_TMP_N6)
then recreated the indexes using the old definition (definition in place before RUP was applied).
===================================================================================================

Index(s) definition after RUP applied:
adwork log shows how index was created:
CREATE INDEX "QP"."QP_PREQ_LINES_TMP_N4" ON "QP"."QP_PREQ_LINES_TMP_T" ("REQUEST_ID", "LINE_INDEX")
CREATE INDEX "QP"."QP_PREQ_QUAL_TMP_N6" ON "QP"."QP_PREQ_QUAL_TMP_T" ("REQUEST_ID", "PRICING_STATUS_CODE", "PRICING_PHASE_ID")


How index was re-created:
drop index QP.QP_PREQ_LINES_TMP_N4;
create index QP.QP_PREQ_LINES_TMP_N4 on QP.QP_PREQ_LINES_TMP_T (REQUEST_ID, LINE_INDEX, PROCESS_STATUS);

drop index QP.QP_PREQ_QUAL_TMP_N6;
create index QP.QP_PREQ_QUAL_TMP_N6 on QP.QP_PREQ_QUAL_TMP_T (REQUEST_ID, UNIQUE_KEY, PRICING_STATUS_CODE, PRICING_PHASE_ID);


Now, after dropping indexes, it is more like 10 minutes from cz and back to line.
This is too large of delay for the number of order lines that need booked that are backlogged.


NOTES
=======
  QP:Blind Discount Option = NO
  QP:Builld Attributes mapping options =Y
  QP:Satisfied qualifiers option = NO
  OM: Send changed lines to Pricing = YES

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Responsibility:Order Management Super User
2. Navigate to: orders, returns >order organizer
3. Query up Order (Example: 649264)
4. click on line items tab
5.  Add Item (Example: 350106) Qty 1
6. Invoke Configurator (slow to pop configurator window)
7. Configure item (no slowness) and click finish (takes 5+ minutes to come back to the order line)

The larger the order, the  time it takes.  Most time can be attributed to 1 or 2 statements which correspond to QP (advanced pricing) temporary tables.
They commonly have 50 configurations on one order.

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