Performance Problem with the Item Subinventories form INVSDSUB
(Doc ID 2376732.1)
Last updated on JANUARY 23, 2020
Applies to:Oracle Inventory Management - Version 12.1.3 and later
Information in this document applies to any platform.
Customer reports "We have added thousands of item/locator restrictions for our subinventories. When clicking the Item/Subinventory button in INVSDSUB, the screen takes about 10 minutes for it to return control back to me. Each time I move around in the form, it takes another 10 minutes per movement."
What has been checked, in no particular order:
- problem occurs for all users
- no customization is found in the form
- debug is turned off
- customer flipped the profile option Flexfields:Validate on Server from No to Yes with no change in symptoms
- time-consuming query hits MTL_ITEM_SUB_INVENTORIES_ALL_V
- Indexes on the relevant tables are identical to those in an internal test instance and are all valid.
- no external tables are used, ruling out fix from Note 2034938.1
- "Weekly, we gather schema statistics for all schemas at 10%"
One particular anomaly is observed in the TKPROFd trace:
SQL*Net message to client 6662 <IP address>
SQL*Net message from client 6662 <IP address>
This is the biggest chunk of time expended, but why? The users are not sitting there hitting Enter or OK 6662 times during this wait for the screen to come back, but why is the code waiting on a 'SQL*Net message from client' response to a 'SQL*Net message to client'?
The issue has the following business impact:
Due to this issue, users cannot feasibly utilize this form.
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