Customer Merge Concurrent Program Performance Is Poor (Doc ID 2656522.1)

Last updated on JULY 19, 2021

Applies to:

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

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