PIM Import Catalog Items - EGOICI- Taking Long Time To Import Items (Doc ID 1423315.1)

Last updated on MAY 15, 2016

Applies to:

Oracle Product Hub - Version 12.1.1 and later
Information in this document applies to any platform.
EGOICI

Symptoms


Importing items into PIM 12.1.1 using Import Catalog Items. Tuned the import process and performance reached after this tuning excercise is about 15K items per hour. However, since the last 4 days seeing problems with the import process. The performance has degraded to unacceptable levels and EGOICI now takes 6 hours to update around 15K items.

The following 2 SQL's from trace are of major concern:

SELECT ROWID, ORGANIZATION_ID, INVENTORY_ITEM_ID, LIFECYCLE_ID, CURRENT_PHASE_ID, INVENTORY_ITEM_STATUS_CODE, ITEM_CATALOG_GROUP_ID, TRANSACTION_ID, TRANSACTION_TYPE, ITEM_NUMBER, UNIT_WEIGHT, WEIGHT_UOM_CODE, STYLE_ITEM_FLAG, TRADE_ITEM_DESCRIPTOR, GDSN_OUTBOUND_ENABLED_FLAG, PRIMARY_UOM_CODE
FROM MTL_SYSTEM_ITEMS_INTERFACE INT
WHERE (INT.ORGANIZATION_ID = :B4 OR :B3 = 1) AND INT.SET_PROCESS_ID = :B2 AND INT.PROCESS_FLAG = :B1 AND EXISTS
(SELECT 1
FROM MTL_PARAMETERS MP
WHERE INT.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID ) FOR UPDATE OF INT.CURRENT_PHASE_ID, INT.PROCESS_FLAG


SELECT MSII.ROWID, MSII.ORGANIZATION_ID, MSII.INVENTORY_ITEM_ID, MSII.ITEM_CATALOG_GROUP_ID, MSII.INVENTORY_ITEM_STATUS_CODE, MSII.LIFECYCLE_ID, MSII.CURRENT_PHASE_ID, MSII.TRANSACTION_ID, MSII.TRANSACTION_TYPE, MSII.ITEM_NUMBER, MP.MASTER_ORGANIZATION_ID, MSII.CREATED_BY
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII, MTL_PARAMETERS MP
WHERE (MSII.ORGANIZATION_ID = :B4 OR :B3 = 1) AND MSII.SET_PROCESS_ID = :B2 AND MSII.PROCESS_FLAG = :B1 AND MSII.ORGANIZATION_ID = MP.ORGANIZATION_ID FOR UPDATE OF PROCESS_FLAG

They are running for more than one hour and consuming High CPU resources.

Two more SQL's that are stuck:

UPDATE MTL_SYSTEM_ITEMS_INTERFACE SET TRANSACTION_ID = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL WHERE TRANSACTION_ID IS NULL AND SET_PROCESS_ID = :B1

UPDATE MTL_SYSTEM_ITEMS_INTERFACE SET TRANSACTION_ID = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL WHERE TRANSACTION_ID IS NULL AND SET_PROCESS_ID = :B1

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms