Performance Import Items (INCOIN) with ICX_CAT_ENDECA_ITEM_ATTRIBUTES (Doc ID 2088848.1)

Last updated on JULY 08, 2020

Applies to:

Symptoms

On : 12.2.4 version, Other Inventory Issues

ACTUAL BEHAVIOR

Inventory Import Items program runs for a long time or does not complete. Item records are being created from interface table to base tables, but the process does not complete or has very slow performance

TKPROF trace files do not show slow performance on any ICX (iProcurement) or Endeca statements. The slowest performance is at statements like the following:

SELECT I.TRANSACTION_ID, I.TRANSACTION_TYPE, I.INVENTORY_ITEM_ID,
I.ORGANIZATION_ID , I.REVISION, ROWID
FROM
MTL_ITEM_REVISIONS_INTERFACE I WHERE I.PROCESS_FLAG = 2 AND I.SET_PROCESS_ID
= :B3 AND (I.ORGANIZATION_ID = :B2 OR :B1 = 1) AND NOT EXISTS (SELECT 'X'
FROM MTL_SYSTEM_ITEMS M WHERE M.ORGANIZATION_ID = I.ORGANIZATION_ID AND
M.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID) AND NOT EXISTS (SELECT 'X' FROM
MTL_SYSTEM_ITEMS_INTERFACE MI WHERE MI.ORGANIZATION_ID = I.ORGANIZATION_ID
AND MI.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID AND PROCESS_FLAG = 4)

It seemed the same as described in [ Note 1956955.1 - Import Items Performance Issue On Update (Doc ID 1956955.1) ], but even after implementing the solution from this note the performance is still the same.
MTL_SYSTEM_ITEMS_B_N1
MTL_SYSTEM_ITEMS_INTERFACE_N4
The customer uses only segment1 in mtl_system_items_b for the item number


STEPS TO REPRODUCE

1. Navigate to Inventory responsibility
2. Run item import for large number of items
- see the slow performance

EXPECTED BEHAVIOR

Expect the process to complete in a timely manner

Cause

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