My Oracle Support Banner

Account Merge Performance Issue (Doc ID 2600157.1)

Last updated on OCTOBER 24, 2019

Applies to:

Oracle Order Management - Version 12.2.7 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.7 version, Oracle Receivables Performance Issues

ACTUAL BEHAVIOR
---------------
Performance issue – Account merge form

EXPECTED BEHAVIOR
-----------------------
Account Merge should complete in time.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Submit Account Merge
2. Taking time to get complete

==

File: Customer_Merge_log_070819.txt

shows log file

RAXMRG: Customer Merge

Current system time is 07-AUG-2019 15:22:43
Current system time is 07-AUG-2019 17:21:14

elapsed: 1:58:31 (HH:MM:SS)

==

File: aebdv022_ora_220478_CP__REQ_125116586.txt

Trace file: aebdv022_ora_220478_CP__REQ_125116586.trc
Sort options: exeela  prsela  fchela  

shows the most expensive query:

SELECT /*+ USE_NL (M A)
                 INDEX (A,OE_PRICE_ADJ_ATTRIBS_N2) */ A.price_adj_attrib_id,
Decode(A.pricing_attribute, 'QUALIFIER_ATTRIBUTE2', M.customer_id,
                            M.customer_site_id) ATTRIB_VALUE_FROM,
A.pricing_attr_value_from,
M.customer_merge_id
FROM   ra_customer_merges M,
       oe_price_adj_attribs A,
       oe_price_adjustments P,
       oe_order_headers H
WHERE  A.pricing_context = 'CUSTOMER'
       AND ( ( A.pricing_attribute IN ( 'QUALIFIER_ATTRIBUTE11',
                                        'QUALIFIER_ATTRIBUTE5',
                                        'QUALIFIER_ATTRIBUTE14' )
               AND A.pricing_attr_value_from = To_char(M.duplicate_site_id) )
              OR ( A.pricing_attribute = 'QUALIFIER_ATTRIBUTE2'
                   AND A.pricing_attr_value_from = To_char(M.duplicate_id) ) )
       AND M.process_flag = 'N'
       AND M.request_id = :B2
       AND M.set_number = :B1
       AND A.price_adjustment_id = P.price_adjustment_id
       AND P.header_id = H.header_id  

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      0   FILTER
      0    HASH JOIN
      0     NESTED LOOPS
      0      NESTED LOOPS
      0       NESTED LOOPS
      3        TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID BATCHED)
                   OF 'RA_CUSTOMER_MERGES' (TABLE)
      3         INDEX   MODE: ANALYZED (RANGE SCAN) OF
                    'RA_CUSTOMER_MERGES_N1' (INDEX)
      0        TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID BATCHED)
                   OF 'OE_PRICE_ADJ_ATTRIBS' (TABLE)
      0         INDEX   MODE: ANALYZED (RANGE SCAN) OF
                    'OE_PRICE_ADJ_ATTRIBS_N2' (INDEX)
      0       INDEX   MODE: ANALYZED (UNIQUE SCAN) OF
                  'OE_PRICE_ADJUSTMENTS_U1' (INDEX (UNIQUE))
      0      TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                 'OE_PRICE_ADJUSTMENTS' (TABLE)
      0     INDEX   MODE: ANALYZED (FULL SCAN) OF 'OE_ORDER_HEADERS_U1'
                (INDEX (UNIQUE))
 
call    count     cpu  elapsed     disk    query  current rows
------- ----- ------- -------- -------- -------- -------- ----
Parse       1    0.00     0.00        0        0        0    0
Execute     1    0.01     0.01        0        0        0    0
Fetch       1  342.63  4816.87  3636434  3636685        0    0
------- ----- ------- -------- -------- -------- -------- ----
total       3  342.64  4816.89  3636434  3636685        0    0


..

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
elapsed: 0.09

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
elapsed: 7097.35

==

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.