My Oracle Support Banner

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.

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

To view full details, 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 a vibrant support community of peers and Oracle experts.