Category Sets Form Having Performance Issue While Querying Catalogs

(Doc ID 2361374.1)

Last updated on FEBRUARY 14, 2018

Applies to:

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


On : 12.1.3 version, Other Inventory Issues


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:

 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





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