Customer Merge Concurrent Program Performance Is Poor
(Doc ID 2656522.1)
Last updated on MARCH 07, 2022
Applies to:
Oracle Order Management - Version 12.2.5 and laterInformation in this document applies to any platform.
Goal
On : 12.2.5 version, Customer Usage
Customer Merge concurrent program performance is not acceptable. It took 27 hours to merge 20 records. How can the performance be improved?
The most expensive query in tkprof is below which took 20 Minutes to complete:
SELECT /*+ PARALLEL (L) PARALLEL (M1) PARALLEL(M2)
PARALLEL (M3) PARALLEL (M4) PARALLEL (M5)
PARALLEL (M6) PARALLEL (M7) */ L.LINE_ID , L.HEADER_ID,
L.LAST_UPDATE_DATE, L.LINE_ID, 'Y', SOLD_TO_ORG_ID, INVOICE_TO_ORG_ID,
SHIP_TO_ORG_ID, INTMED_SHIP_TO_ORG_ID, DELIVER_TO_ORG_ID,
END_CUSTOMER_SITE_USE_ID, END_CUSTOMER_ID,'N',
NVL(M1.CUSTOMER_MERGE_ID,NVL(M2.CUSTOMER_MERGE_ID,NVL(M3.CUSTOMER_MERGE_ID,
NVL(M4.CUSTOMER_MERGE_ID,NVL(M5.CUSTOMER_MERGE_ID,
NVL(M6.CUSTOMER_MERGE_ID,M7.CUSTOMER_MERGE_ID)))))) FROM OE_CUST_MERGES_GTT
M1, RA_CUSTOMER_MERGES M2, RA_CUSTOMER_MERGES M3, RA_CUSTOMER_MERGES M4,
RA_CUSTOMER_MERGES M5, OE_CUST_MERGES_GTT M6, RA_CUSTOMER_MERGES M7,
OE_ORDER_LINES L WHERE ( L.SOLD_TO_ORG_ID = M1.DUPLICATE_ID(+) AND
L.INVOICE_TO_ORG_ID = M2.DUPLICATE_SITE_ID(+) AND L.SHIP_TO_ORG_ID =
M3.DUPLICATE_SITE_ID(+) AND L.INTMED_SHIP_TO_ORG_ID = M4.DUPLICATE_SITE_ID(+)
AND L.END_CUSTOMER_SITE_USE_ID = M5.DUPLICATE_SITE_ID(+) AND
L.END_CUSTOMER_ID = M6.DUPLICATE_ID(+) AND L.DELIVER_TO_ORG_ID =
M7.DUPLICATE_SITE_ID(+) ) AND (M1.DUPLICATE_ID IS NOT NULL OR M2.DUPLICATE_ID
IS NOT NULL OR M3.DUPLICATE_ID IS NOT NULL OR M4.DUPLICATE_ID IS NOT NULL OR
M5.DUPLICATE_ID IS NOT NULL OR M6.DUPLICATE_ID IS NOT NULL OR M7.DUPLICATE_ID
IS NOT NULL) AND M1.PROCESS_FLAG(+) = 'N' AND M2.PROCESS_FLAG(+) = 'N' AND
M3.PROCESS_FLAG(+) = 'N' AND M4.PROCESS_FLAG(+) = 'N' AND M5.PROCESS_FLAG(+)
= 'N' AND M6.PROCESS_FLAG(+) = 'N' AND M7.PROCESS_FLAG(+) = 'N' AND
M1.REQUEST_ID(+) = :B2 AND M2.REQUEST_ID(+) = :B2 AND M3.REQUEST_ID(+) = :B2
AND M4.REQUEST_ID(+) = :B2 AND M5.REQUEST_ID(+) = :B2 AND M6.REQUEST_ID(+) =
:B2 AND M7.REQUEST_ID(+) = :B2 AND M1.SET_NUMBER(+) = :B1 AND
M2.SET_NUMBER(+) = :B1 AND M3.SET_NUMBER(+) = :B1 AND M4.SET_NUMBER(+) = :B1
AND M5.SET_NUMBER(+) = :B1 AND M6.SET_NUMBER(+) = :B1 AND M7.SET_NUMBER(+) =
:B1
Solution
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
Goal |
Solution |