Performance Issue In Organization Items INVIDITM with BOM_INVENTORY_COMPONENTS

(Doc ID 1535734.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Item Master - Version 12.0.4 and later
Information in this document applies to any platform.

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.


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms