Performance Issue On Import Items INCOIN With MTL_SYSTEM_ITEMS_INTERFACE And MTL_PARAMETERS
Last updated on FEBRUARY 15, 2018
Applies to:
Oracle Item Master - Version 12.1.3 and laterInformation 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)
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