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

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