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)

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_SYSTEM_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

Changes

Cause

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