My Oracle Support Banner

Standard cost update performance issue (Doc ID 2693238.1)

Last updated on JULY 22, 2020

Applies to:

Oracle Cost Management - Version 12.1.3 and later
Information in this document applies to any platform.

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

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.