Standard cost update performance issue (Doc ID 2693238.1)

Last updated on OCTOBER 14, 2023

Applies to:

Symptoms

Standard cost update is running for longtime.
In Tkprof, the following sql is running for longtime.

INSERT INTO CST_ITEM_LIST_TEMP(INVENTORY_ITEM_ID) SELECT /*+ Index(CIC_M
  CST_ITEM_COSTS_U1)*/ ITEM_ID FROM( SELECT NVL(CICD1.INVENTORY_ITEM_ID,
  CICD2.INVENTORY_ITEM_ID) ITEM_ID,CICD1.INVENTORY_ITEM_ID FROZEN_ITEM_ID,
  NVL(CICD1.ORGANIZATION_ID,CICD2.ORGANIZATION_ID) ORGANIZATION_ID,
  CICD2.INVENTORY_ITEM_ID USER_ITEM_ID FROM (SELECT * FROM
  CST_ITEM_COST_DETAILS CICD_I1 WHERE CICD_I1.COST_TYPE_ID = 1 AND
  CICD_I1.ORGANIZATION_ID= :B1 ) CICD1 FULL OUTER JOIN (SELECT * FROM
  CST_ITEM_COST_DETAILS CICD_I2 WHERE CICD_I2.COST_TYPE_ID =:B2 AND
  CICD_I2.ORGANIZATION_ID= :B1 ) CICD2 ON CICD1.INVENTORY_ITEM_ID=
  CICD2.INVENTORY_ITEM_ID AND CICD1.ORGANIZATION_ID= CICD2.ORGANIZATION_ID
  AND CICD1.COST_ELEMENT_ID = CICD2.COST_ELEMENT_ID AND CICD1.RESOURCE_ID =
  CICD2.RESOURCE_ID AND CICD1.LEVEL_TYPE= CICD2.LEVEL_TYPE AND
  CICD1.ROLLUP_SOURCE_TYPE=CICD2.ROLLUP_SOURCE_TYPE AND
  CICD1.USAGE_RATE_OR_AMOUNT = CICD2.USAGE_RATE_OR_AMOUNT AND
  CICD1.BASIS_TYPE = CICD2.BASIS_TYPE AND CICD1.BASIS_FACTOR=
  CICD2.BASIS_FACTOR AND CICD1.ITEM_COST = CICD2.ITEM_COST WHERE
  CICD1.INVENTORY_ITEM_ID IS NULL OR CICD2.INVENTORY_ITEM_ID IS NULL ) CICD ,
  CST_ITEM_COSTS CIC_M ,MTL_SYSTEM_ITEMS_B MSI WHERE CIC_M.INVENTORY_ITEM_ID
=
  CICD.ITEM_ID AND CIC_M.ORGANIZATION_ID = CICD.ORGANIZATION_ID AND
  CIC_M.COST_TYPE_ID = :B2 AND MSI.ORGANIZATION_ID = CICD.ORGANIZATION_ID AND

  MSI.INVENTORY_ITEM_ID = CICD.ITEM_ID AND MSI.COSTING_ENABLED_FLAG = 'Y'
  GROUP BY ITEM_ID

Cause

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