Category Sets Form Having Performance Issue While Querying Catalogs
Last updated on FEBRUARY 14, 2018
Applies to:
Oracle Item Master - Version 12.1.3 and laterInformation in this document applies to any platform.
Symptoms
On : 12.1.3 version, Other Inventory Issues
ACTUAL BEHAVIOR
While querying an item or category in the Item Category Form (Navigation: Inventory - Setup - Items - Categories - Category Sets) the query execution is showing a big performance issue while the control doesn't come back to the form.
Hours can pass by waiting for the form to show results but at the end the session must be killed.
The performance started after millions of records where loaded in the system for items and item organization assignments for multiple organizations.
The following excerpt from the trace tkprof file show the problematic query and the execution details:
SELECT INVENTORY_ITEM_ID,ROW_ID,INV_ITEM_DESCRIPTION,ORGANIZATION_ID,
CATEGORY_SET_ID,LAST_UPDATE_DATE,CATEGORY_ID,LAST_UPDATED_BY,CREATION_DATE,
CREATED_BY,LAST_UPDATE_LOGIN,REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,
PROGRAM_UPDATE_DATE,INV_ITEM_PADDED_CONCAT_SEGS,INV_ITEM_CONCAT_SEGS
FROM
MTL_ITEM_CATEGORIES_VIEW WHERE ORGANIZATION_ID = :1 and (CATEGORY_SET_ID=:2)
order by inv_item_padded_concat_segs
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 60.98 2623.76 829137 831608 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 60.99 2623.76 829137 831608 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 173
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY (cr=0 pr=0 pw=0 time=8 us cost=378651 size=117965544 card=999708)
0 HASH JOIN (cr=0 pr=0 pw=0 time=5 us cost=352072 size=117965544 card=999708)
1181 INDEX FULL SCAN MTL_CATEGORIES_B_U1 (cr=4 pr=3 pw=0 time=16445 us cost=4 size=5905 card=1181)(object id 38146)
0 HASH JOIN (cr=0 pr=0 pw=0 time=13 us cost=352055 size=112967004 card=999708)
6418658 TABLE ACCESS FULL MTL_SYSTEM_ITEMS_TL (cr=113689 pr=113442 pw=0 time=38442504 us cost=31254 size=37093030 card=1426655)
0 HASH JOIN (cr=0 pr=0 pw=0 time=13 us cost=312729 size=82140789 card=944147)
4144140 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B (cr=717119 pr=715692 pw=0 time=2601417315 us cost=246032 size=28533100 card=1426655)
4144140 INDEX RANGE SCAN MTL_SYSTEM_ITEMS_B_N13 (cr=25884 pr=24523 pw=0 time=271013348 us cost=8676 size=0 card=1426655)(object id 328259)
0 TABLE ACCESS BY INDEX ROWID MTL_ITEM_CATEGORIES (cr=0 pr=0 pw=0 time=0 us cost=60343 size=59742158 card=891674)
0 INDEX RANGE SCAN MTL_ITEM_CATEGORIES_N1 (cr=0 pr=0 pw=0 time=0 us cost=5270 size=0 card=891674)(object id 38221
Changes
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