Slow Performance with 'Import Price Catalog' Due to "INSERT INTO ICX_CAT_ENDECA_ITEM_ATTRIBUTES"

(Doc ID 2110950.1)

Last updated on FEBRUARY 26, 2016

Applies to:

Oracle Purchasing - Version 12.2.4 and later
Information in this document applies to any platform.

Symptoms

It took 12+ hours for 'Import Price Catalog' (PDOI) to import 9000+ records.

TKPROF Trace indicates the slow performance was caused due to many of INSERT statements of INSERT INTO ICX_CAT_ENDECA_ITEM_ATTRIBUTES, like 

INSERT INTO ICX_CAT_ENDECA_ITEM_ATTRIBUTES( recordkey, attributekey,
attributevalue, LANGUAGE) SELECT pav.INVENTORY_ITEM_ID|| '#' ||
pav.PO_LINE_ID|| '#' || pav.REQ_TEMPLATE_NAME|| '#' ||
pav.REQ_TEMPLATE_LINE_NUM|| '#' || pav.ORG_ID|| '#' || pav.language
"RecordSpec",Icatl.KEY "Attribute",TL_TEXT_CAT_ATTRIBUTE11 , pav.language
FROM po_attribute_values_tlp pav,Icx_Cat_Attributes_Tl Icatl WHERE
icatl.stored_in_column = 'TL_TEXT_CAT_ATTRIBUTE11' AND
pav.INVENTORY_ITEM_ID =61870 and pav.PO_LINE_ID = 58137 and
pav.REQ_TEMPLATE_NAME ='-2' and pav.REQ_TEMPLATE_LINE_NUM =-2 and
pav.ORG_ID =82 and icatl.language='US' AND icatl.stored_in_table =
'PO_ATTRIBUTE_VALUES_TLP' AND icatl.language = pav.language And
(Icatl.Rt_Category_Id = Pav.Ip_Category_Id or Icatl.Rt_Category_Id=0) AND
TL_TEXT_CAT_ATTRIBUTE11 IS NOT NULL

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