Performance Import Items (INCOIN) with ICX_CAT_ENDECA_ITEM_ATTRIBUTES

Last updated on AUGUST 21, 2016

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