Performance Issue In Organization Items INVIDITM with BOM_INVENTORY_COMPONENTS (Doc ID 1535734.1)

Last updated on JULY 08, 2020

Applies to:

Symptoms

ISSUE

When querying an specific item in the Organization Items form there is a large performance issue.

Most expensive script:
SELECT /*+ LEADING(INV) INDEX(INV BOM_INVENTORY_COMPONENTS_N1) USE_NL(INV BOM)
  */ COUNT(1)
FROM
BOM_INVENTORY_COMPONENTS INV, BOM_BILL_OF_MATERIALS BOM WHERE
 INV.COMPONENT_ITEM_ID = :B1 AND INV.BILL_SEQUENCE_ID=BOM.BILL_SEQUENCE_ID
 AND BOM.ORGANIZATION_ID IN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS
 WHERE MASTER_ORGANIZATION_ID= :B2 ) AND ROWNUM = 1



STEPS TO RECREATE

The issue can be reproduced at will with the following steps:
1. Navigation : Inventory / Items / Organization items
2. Query high use item
3. observe slow performance in return.

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.