My Oracle Support Banner

32520287 All Inventories Value Report Running For Long Time (Doc ID 2759348.1)

Last updated on MARCH 20, 2021

Applies to:

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

Symptoms

On : 12.2.9 version, Reports Issues

ACTUAL BEHAVIOR
---------------
All Inventories Value Report Running for Long time

We recently upgraded our EBSCRP instance from 12.0.6 to 12.2.9 version.

After Upgrade All Inventories Value Report is running for long time (Attached trace file).
Could you please review and update

Target query appeared to be:


SQL ID: 0y9ngmbbubszx Plan Hash: 1870946329

INSERT INTO CST_INV_QTY_TEMP( QTY_SOURCE, ORGANIZATION_ID, INVENTORY_ITEM_ID,
  CATEGORY_ID, REVISION, COST_TYPE_ID, COST_GROUP_ID, FROM_ORGANIZATION_ID,
  TO_ORGANIZATION_ID, ROLLBACK_QTY, INTRANSIT_INV_ACCOUNT ) SELECT 8, DECODE(
  NVL(MMT.FOB_POINT,MIP.FOB_POINT), 1,MMT.TRANSFER_ORGANIZATION_ID, 2,
  MMT.ORGANIZATION_ID ), ITEMS.INVENTORY_ITEM_ID, ITEMS.CATEGORY_ID,
  MMT.REVISION, ITEMS.COST_TYPE_ID, CGS.COST_GROUP_ID, MMT.ORGANIZATION_ID,
  MMT.TRANSFER_ORGANIZATION_ID, SUM( DECODE( NVL(MMT.FOB_POINT,MIP.FOB_POINT),
  1, INV_CONVERT.INV_UM_CONVERT( MMT.INVENTORY_ITEM_ID,NULL,
  MMT.TRANSACTION_QUANTITY, MMT.TRANSACTION_UOM,MSI_TO.PRIMARY_UOM_CODE,NULL,
  NULL ), 2, MMT.PRIMARY_QUANTITY ) ), NVL(MMT.INTRANSIT_ACCOUNT,
  MIP.INTRANSIT_INV_ACCOUNT) FROM MTL_MATERIAL_TRANSACTIONS MMT,
  CST_ITEM_LIST_TEMP ITEMS, CST_CG_LIST_TEMP CGS, MTL_INTERORG_PARAMETERS MIP,
  MTL_SYSTEM_ITEMS MSI_TO, MTL_TRANSACTION_TYPES MTT WHERE
  MMT.TRANSFER_ORGANIZATION_ID = :B3 AND MMT.TRANSACTION_ACTION_ID = 21 AND
  MMT.COSTED_FLAG IS NULL AND ( NVL(MMT.FOB_POINT,MIP.FOB_POINT) = 2 OR
  MMT.ORGANIZATION_ID NOT IN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS
  WHERE PROCESS_ENABLED_FLAG = 'Y') OR EXISTS (SELECT 1 FROM
  MTL_MATERIAL_TRANSACTIONS WHERE PARENT_TRANSACTION_ID = MMT.TRANSACTION_ID
  AND TRANSACTION_ACTION_ID = 15 AND ORGANIZATION_ID = :B3 AND COSTED_FLAG IS
  NULL)) AND MMT.TRANSACTION_DATE > :B2 AND MMT.INVENTORY_ITEM_ID =
  ITEMS.INVENTORY_ITEM_ID AND DECODE(MMT.TRANSACTION_ACTION_ID, 12,
  MMT.TRANSFER_COST_GROUP_ID, 21, MMT.TRANSFER_COST_GROUP_ID, 15,
  MMT.COST_GROUP_ID, 22, MMT.COST_GROUP_ID) = CGS.COST_GROUP_ID AND
  MIP.TO_ORGANIZATION_ID = MMT.TRANSFER_ORGANIZATION_ID AND
  MIP.FROM_ORGANIZATION_ID = MMT.ORGANIZATION_ID AND MSI_TO.ORGANIZATION_ID =
  MMT.TRANSFER_ORGANIZATION_ID AND MSI_TO.INVENTORY_ITEM_ID =
  MMT.INVENTORY_ITEM_ID AND MTT.TRANSACTION_ACTION_ID =
  MMT.TRANSACTION_ACTION_ID AND MTT.TRANSACTION_SOURCE_TYPE_ID =
  MMT.TRANSACTION_SOURCE_TYPE_ID AND MTT.TRANSACTION_TYPE_ID =
  MMT.TRANSACTION_TYPE_ID AND MMT.CREATION_DATE <= NVL(:B1 ,MMT.CREATION_DATE)
  GROUP BY DECODE( NVL(MMT.FOB_POINT,MIP.FOB_POINT), 1,
  MMT.TRANSFER_ORGANIZATION_ID, 2,MMT.ORGANIZATION_ID ),
  ITEMS.INVENTORY_ITEM_ID, ITEMS.CATEGORY_ID, MMT.REVISION,
  ITEMS.COST_TYPE_ID, CGS.COST_GROUP_ID, MMT.ORGANIZATION_ID,
  MMT.TRANSFER_ORGANIZATION_ID, NVL(MMT.INTRANSIT_ACCOUNT,
  MIP.INTRANSIT_INV_ACCOUNT)



call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 302.11 8533.07 1795078 1962035 7 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 302.11 8533.07 1795078 1962035 7 0

 

EXPECTED BEHAVIOR
-----------------------
Expect quick performance on report

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.