My Oracle Support Banner

Form MSCFDSRA.fmb IO Service Level Setup Performance Issue When Selecting Categories (Doc ID 2450758.1)

Last updated on AUGUST 19, 2019

Applies to:

Oracle Inventory Optimization - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.4 version, User Interface in PROD

Users are on 12.2.4 and have applied 12.2.4.1 VCP patching. They are running into a severe performance problem when selecting Category list of values in the form MSCFDSRA.fmb 120.24 Service Level and Fulfillment Lead Time. Example data:

Create a service level set called PROD_SVL_IO and then select category from the list of values. It does not matter if they type it, copy/paste it, or select from the LOV, it takes about 10 minutes every time. The sql being executed is as follows

SQL ID: <ID> Plan Hash: <ID>

select distinct sr_category_id,category_set_id,category_name,description
from
msc_item_categories where enabled_flag='Y' and category_set_id =
 fnd_profile.value('MSC_SERVICE_LEVEL_CATEGORY_SET') order by category_name


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 6 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 787.60 882.91 435061 435074 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 787.61 882.91 435061 435080 0 9

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 179 (APPS)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
  9 9 9 SORT UNIQUE (cr=435096 pr=435061 pw=0 time=882911807 us cost=116867 size=396 card=9)
  588439 588439 588439 PARTITION RANGE ALL PARTITION: 1 2 (cr=435096 pr=435061 pw=0 time=890584246 us cost=110287 size=25891316 card=588439)
  588439 588439 588439 TABLE ACCESS FULL MSC_ITEM_CATEGORIES PARTITION: 1 2 (cr=435096 pr=435061 pw=0 time=889973186 us cost=110287 size=25891316 card=588439)


Rows Execution Plan
------- ---------------------------------------------------
  0 SELECT STATEMENT MODE: ALL_ROWS
  9 SORT (UNIQUE)
588439 PARTITION RANGE (ALL) PARTITION: START=1 STOP=2
588439 TABLE ACCESS MODE: ANALYZED (FULL) OF
  'MSC_ITEM_CATEGORIES' (TABLE) PARTITION: START=1 STOP=2


Elapsed times include waiting on following events:
 Event waited on Times Max. Wait Total Waited
 ---------------------------------------- Waited ---------- ------------
 SQL*Net message to client 1 0.00 0.00
 reliable message 1 0.00 0.00
 enq: KO - fast object checkpoint 1 0.02 0.02
 direct path read 3422 0.88 95.70
 latch free 44 0.00 0.00
 SQL*Net message from client 1 178.78 178.78
********************************************************************************

This is the same sql as in 12.1.3 bug 24447828. I found no new versions of the form in 12.2.4 only 12.2.5 and customer is not upgrading anytime soon.

EXPECTED BEHAVIOR
-----------------------
Expect category selection to take 2-3 seconds at most

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Navigate Inventory Planner / Setup / Service Levels and Fulfillment Lead-times
2. Create new name
3. Try to select category many different ways
4. Form hangs for about 10 minutes


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!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.