Performance Issue On Import Items INCOIN With MTL_SYSTEM_ITEMS_INTERFACE And MTL_PARAMETERS (Doc ID 1988725.1)

Last updated on AUGUST 21, 2016

Applies to:

Oracle Item Master - Version 12.1.3 and later
Information in this document applies to any platform.
Performance Issue On Import Items INCOIN With MTL_SYSTEM_ITEMS_INTERFACE And MTL_PARAMETERS

Symptoms

ACTUAL BEHAVIOR
 
The is a performance issue with Import Items where the 5 most expensive script take up 97% of the time:

SELECT DISTINCT MSII.COPY_ORGANIZATION_CODE
FROM
MTL_SYSTEM_ITEMS_INTERFACE MSII, MTL_PARAMETERS MP WHERE MSII.PROCESS_FLAG =
 1 AND MSII.SET_PROCESS_ID = :B3 AND MSII.TRANSACTION_TYPE = 'CREATE' AND
 ((MSII.ORGANIZATION_ID = :B2 ) OR (:B1 = 1)) AND MSII.ORGANIZATION_ID =
 MP.ORGANIZATION_ID AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID AND
 ((MSII.COPY_ITEM_ID IS NOT NULL) AND (MSII.COPY_ORGANIZATION_ID IS NULL)
 AND (COPY_ORGANIZATION_CODE IS NOT NULL))

SELECT DISTINCT MSII.COPY_ITEM_NUMBER
FROM
MTL_SYSTEM_ITEMS_INTERFACE MSII, MTL_PARAMETERS MP WHERE MSII.PROCESS_FLAG =
 1 AND MSII.SET_PROCESS_ID = :B3 AND MSII.TRANSACTION_TYPE = 'CREATE' AND
 ((MSII.ORGANIZATION_ID = :B2 ) OR (:B1 = 1)) AND MSII.ORGANIZATION_ID =
 MP.ORGANIZATION_ID AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID AND
 ((MSII.COPY_ITEM_ID IS NULL) AND (MSII.COPY_ITEM_NUMBER IS NOT NULL))

SELECT DISTINCT MSII.COPY_ITEM_ID, MSII.COPY_ORGANIZATION_ID
FROM
MTL_SYSTEM_ITEMS_INTERFACE MSII, MTL_PARAMETERS MP WHERE MSII.PROCESS_FLAG =
 1 AND MSII.SET_PROCESS_ID = :B3 AND MSII.TRANSACTION_TYPE = 'CREATE' AND
 ((MSII.ORGANIZATION_ID = :B2 ) OR (:B1 = 1)) AND MSII.ORGANIZATION_ID =
 MP.ORGANIZATION_ID AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID AND
 MSII.COPY_ITEM_ID IS NOT NULL AND MSII.COPY_ORGANIZATION_ID IS NOT NULL

UPDATE MTL_SYSTEM_ITEMS_INTERFACE SET PROCESS_FLAG = 3
WHERE
COPY_ITEM_ID IS NOT NULL AND COPY_ORGANIZATION_ID IS NULL AND SET_PROCESS_ID
 = :B1 AND PROCESS_FLAG = 1 AND TRANSACTION_TYPE = 'CREATE' RETURNING
 TRANSACTION_ID INTO :O0

SELECT 'x'
FROM
MTL_SYSTEM_ITEMS MSI WHERE INVENTORY_ITEM_ID = :B1 AND ORGANIZATION_ID IN (
SELECT MASTER_ORGANIZATION_ID FROM MTL_PARAMETERS WHERE ORGANIZATION_ID =
MASTER_ORGANIZATION_ID ) AND ROWNUM = 1 AND NOT EXISTS (SELECT
UNIT_OF_MEASURE FROM MTL_UNITS_OF_MEASURE MUOM WHERE UOM_CLASS = :B2 AND
MSI.PRIMARY_UOM_CODE = MUOM.UOM_CODE)

EXPECTED BEHAVIOR

Expect import items to run faster.

STEPS

The issue can be reproduced at will with the following steps:
1. Launch custom process that loads 5000 items at a time and launches Import Items
2. Observe trace file / log file and run time.

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