PERFORMANCE ISSUES ON ICX_CAT_ITEMS_CTX_HDRS_TLP WHEN RUNNING IMPORT ITEMS (INCOIN) (Doc ID 1055457.1)

Last updated on AUGUST 21, 2016

Applies to:

Item Master - Version: 12.0.6 - Release: 12
Information in this document applies to any platform.
Executable:INCOIN - Import Items

Symptoms

On : 12.0.6 version, Item Issues

There is a performance issue with Item Open Interface (INCOIN). The most expensive statement is:

UPDATE ICX_CAT_ITEMS_CTX_HDRS_TLP SET CTX_DESC = NULL, PURCHASING_ORG_ID =
:B34 , IP_CATEGORY_ID = :B33 , IP_CATEGORY_NAME = :B32 , PO_CATEGORY_ID =
:B31 , SUPPLIER_ID = :B30 , SUPPLIER_PART_NUM = :B29 , SUPPLIER_PART_AUXID =
:B28 , SUPPLIER_SITE_ID = :B27 , REQ_TEMPLATE_PO_LINE_ID = :B26 ,
ITEM_REVISION = :B25 , PO_HEADER_ID = :B24 , DOCUMENT_NUMBER = :B23 ,
LINE_NUM = :B22 , ALLOW_PRICE_OVERRIDE_FLAG = :B21 , NOT_TO_EXCEED_PRICE =
:B20 , LINE_TYPE_ID = :B19 , UNIT_MEAS_LOOKUP_CODE = :B18 ,
SUGGESTED_QUANTITY = :B17 , UNIT_PRICE = :B16 , AMOUNT = :B15 ,
CURRENCY_CODE = :B14 , RATE_TYPE = :B13 , RATE_DATE = :B12 , RATE = :B11 ,
BUYER_ID = :B10 , SUPPLIER_CONTACT_ID = :B9 , RFQ_REQUIRED_FLAG = :B8 ,
NEGOTIATED_BY_PREPARER_FLAG = :B7 , DESCRIPTION = :B6 ,
ORDER_TYPE_LOOKUP_CODE = :B5 , SUPPLIER = :B4 , GLOBAL_AGREEMENT_FLAG = :B3
, MERGED_SOURCE_TYPE = :B2 , ITEM_TYPE = :B1 , LAST_UPDATE_LOGIN = :B47 ,
LAST_UPDATED_BY = :B47 , LAST_UPDATE_DATE = SYSDATE, INTERNAL_REQUEST_ID =
:B46 , REQUEST_ID = :B45 , PROGRAM_APPLICATION_ID = :B44 , PROGRAM_ID =
:B43 , PROGRAM_LOGIN_ID = :B42
WHERE
INVENTORY_ITEM_ID = :B41 AND PO_LINE_ID = :B40 AND REQ_TEMPLATE_NAME = :B39
AND REQ_TEMPLATE_LINE_NUM = :B38 AND ORG_ID = :B37 AND LANGUAGE = :B36 AND
SOURCE_TYPE = :B35


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 32885 1282.20 1266.61 13 2005751 6002800 964299
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 32886 1282.20 1266.61 13 2005751 6002800 964299


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Load MTL_SYSTEM_ITEMS_INTERFACE with a custom loader that kicks of INCOIN.
2. There is a performance issue with this concurrent process



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