My Oracle Support Banner

Performance Issue With icxr12mi.sql When Applying R12.1.1 Maintenance Pack Patch 6678700 to Upgrade from 11.5.10.2 to R12.1.3 (Doc ID 1366569.1)

Last updated on AUGUST 28, 2017

Applies to:

Oracle iProcurement - Version 12.1.1 and later
Information in this document applies to any platform.
***Checked for relevance on 29-OCT-2013***

Symptoms

Apply R12.1.1 Maintenance Pack patch 6678700 to upgrade from 11.5.10.2 to R12.1.3.  Notice that icxr12mi.sql runs for a long time. There are 16 workers and all are running the same sql - icxr12mi.sql.

The affected SQL statement is:

SELECT /*+ LEADING(doc) use_nl(ic1,ctx) */ DOC.*, NVL(IC1.RT_CAT
EGORY_ID, -2) IP_CATEGORY_ID, IC1.CATEGORY_NAME IP_CATEGORY_NAME
, CTX.INVENTORY_ITEM_ID CTX_INVENTORY_ITEM_ID, CTX.SOURCE_TYPE C
TX_SOURCE_TYPE, CTX.ITEM_TYPE CTX_ITEM_TYPE, CTX.PURCHASING_ORG_
ID CTX_PURCHASING_ORG_ID, CTX.SUPPLIER_ID CTX_SUPPLIER_ID, CTX.S
UPPLIER_SITE_ID CTX_SUPPLIER_SITE_ID, CTX.SUPPLIER_PART_NUM CTX_
SUPPLIER_PART_NUM, CTX.SUPPLIER_PART_AUXID CTX_SUPPLIER_PART_AUX
ID, CTX.IP_CATEGORY_ID CTX_IP_CATEGORY_ID, CTX.PO_CATEGORY_ID CT
X_PO_CATEGORY_ID, CTX.IP_CATEGORY_NAME CTX_IP_CATEGORY_NAME, ROW
IDTOCHAR(CTX.ROWID) CTX_ROWID FROM ( SELECT /*+ ROWID(mi) use_nl
(mitl,mic,catMap) */ MI.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, TO_
NUMBER(:B4 ) PO_LINE_ID, TO_CHAR(:B4 ) REQ_TEMPLATE_NAME, TO_NUM
BER(:B4 ) REQ_TEMPLATE_LINE_NUM, NVL(FSP.ORG_ID, -2) ORG_ID, MIT
L.LANGUAGE, 'MASTER_ITEM' SOURCE_TYPE, NVL(FSP.ORG_ID, -2) PURCH
ASING_ORG_ID, MIC.CATEGORY_ID PO_CATEGORY_ID, CATMAP.CATEGORY_KE
Y CATEGORY_KEY, MI.INTERNAL_ORDER_ENABLED_FLAG, MI.PURCHASING_EN
ABLED_FLAG, MI.OUTSIDE_OPERATION_FLAG, MUOM.UNIT_OF_MEASURE UNIT
_MEAS_LOOKUP_CODE, DECODE(MI.PURCHASING_ENABLED_FLAG, 'Y', MI.LI
ST_PRICE_PER_UNIT, TO_NUMBER(NULL)) UNIT_PRICE, MI.RFQ_REQUIRED_
FLAG, MITL.DESCRIPTION, SUBSTR(MITL.LONG_DESCRIPTION,1,2000), MP
ARAMS.ORGANIZATION_ID, MPARAMS.MASTER_ORGANIZATION_ID FROM MTL_S
YSTEM_ITEMS_B MI, MTL_PARAMETERS MPARAMS, MTL_SYSTEM_ITEMS_TL MI
TL, MTL_ITEM_CATEGORIES MIC, MTL_UNITS_OF_MEASURE MUOM, FINANCIA
LS_SYSTEM_PARAMS_ALL FSP, ICX_POR_CATEGORY_DATA_SOURCES CATMAP W
HERE MI.INVENTORY_ITEM_ID = MITL.INVENTORY_ITEM_ID AND MI.ORGANI
ZATION_ID = MPARAMS.ORGANIZATION_ID AND MI.ORGANIZATION_ID = MIT
L.ORGANIZATION_ID AND MITL.LANGUAGE = MITL.SOURCE_LANG AND MIC.I
NVENTORY_ITEM_ID = MI.INVENTORY_ITEM_ID AND MIC.ORGANIZATION_ID
= MI.ORGANIZATION_ID AND MIC.CATEGORY_SET_ID = :B3 AND MUOM.UOM_
CODE = MI.PRIMARY_UOM_CODE AND NOT (MI.REPLENISH_TO_ORDER_FLAG =
'Y' AND MI.BASE_ITEM_ID IS NOT NULL AND MI.AUTO_CREATED_CONFIG_FLAG = 'Y') AND MI.ORGANIZATION_ID =
FSP.INVENTORY_ORGANIZATION_ID AND CATMAP.EXTERNAL_SOURCE_KEY (+) =
TO_CHAR(MIC.CATEGORY_ID) AND CATMAP.EXTERNAL_SOURCE (+) = 'Oracle' ) DOC,
ICX_CAT_CATEGORIES_TL IC1, ICX_CAT_ITEMS_CTX_HDRS_TLP CTX WHERE IC1.KEY (+)
= DOC.CATEGORY_KEY AND IC1.TYPE (+) = 2 AND IC1.LANGUAGE (+) =
DOC.LANGUAGEY_ITEM_ID (+) AND DOC.PO_LINE_ID =
CTX.PO_LINE_ID (+) AND DOC.REQ_TEMPLATE_NAME = CTX.REQ_TEMPLATE_NAME (+)
AND DOC.REQ_TEMPLATE_LINE_NUM = CTX.REQ_TEMPLATE_LINE_NUM (+) AND
DOC.ORG_ID = CTX.ORG_ID (+) AND DOC.LANGUAGE = CTX.LANGUAGE (+) AND
DOC.SOURCE_TYPE = CTX.SOURCE_TYPE (+) ORDER BY DOC.INVENTORY_ITEM_ID

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.