Performance Issue Encounted When One Updates the Request Date On Order Header (Doc ID 2555543.1)

Last updated on APRIL 21, 2023

Applies to:

Symptoms

ACTUAL BEHAVIOR 
Poor performance is experienced when one
updates the request date at the sales order header and saves.

An average order is about 20 lines and it takes about 40 seconds to save.
They are using the PATTERN SEARCH ENGINE
.


MOST EXPENSIVE STATEMENT
-------------------------------------------

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     38      0.01       0.03          0          0          0           0
Fetch       38     12.79      24.23          0    2487306          0        2912
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       77     12.81      24.26          0    2487306          0        2912



EXPECTED BEHAVIOR
IF I update the same information from Mass update option. Its is roughly taking 4 to 5 seconds to update the 40 lines. We are looking for similar time frame.


ACTIONS TAKEN
1. Checked database init.ora settings. if this is 12c database make sure that optimizer_adaptive_features parameter is set to false .
2. Ran Purge Order Management Workflow , run this first for OM order Line and then For OM order header with Attempt to Close = YES
     Refer <Note 878032.1> How To Purge Order Management Workflow
3.Rebuilt indexes on tables
   QP_LIST_HEADER_PHASES QPLHP,
   QP_SECU_ATTR_GROUPS_V ATRGRPS,
   QP_NPREQ_LINE_PATRNS_TMP QPPATTMP,
   QP_NPREQ_LINES_TMP QPLINES,
   QP_PTE_SOURCE_SYSTEMS QPRS
   QP_NPREQ_LINE_ATTRS_TMP
4.Set OM system parameter Reschedule with Ship Method Change  to NO (this is under OM> setup>system parameters>  values.)
5.Implemented all possible recommendations from -  How to Improve Pricing Performance <Document 1335892.1>
6.Applied <patch 28783800>:R12.QP.C  which included few performance fixes.
    This patch will delivered file  QPXGPREB.pls version 20.132.12020000.103 (previous version was 120.132.12020000.99)
7. Gathered stats on the ONT and QP schemas.



STEPS

The issue can be reproduced at will with the following steps:
1. Responsibility: Order Management Super User (or equivalent)
2. Navigate to: Orders, Returns > Sales Orders
3. Query order; Open Order
4. While on Sales Order Header record >  Update Request Date
5. Save

Changes

Cause

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