MSCFNSCW Performance Issue ASCP Workbench Find Criteria Form (Doc ID 2323181.1)

Last updated on OCTOBER 31, 2017

Applies to:

Symptoms

On : 12.2.5 version, User Interface in All instances

Users are on 12.2.5 with VCP patch 22493520 - Mandatory 12.2.5 VCP Consolidated Patch 2. They're having trouble in the Find Criteria for the ASCP Planner's workbench when using Condition = Equals. It usually takes a couple of minutes to come back. The problem performing sql is

SQL ID: bh63xkxhz0c5w Plan Hash: 1294235542

select distinct inventory_item_id hidden, item_name display
from
msc_system_items where plan_id = :1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 170 11.49 146.33 87797 284563 0 16864
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 174 11.49 146.33 87797 284563 0 16864

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

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
  8432 8432 8432 HASH UNIQUE (cr=142282 pr=43898 pw=0 time=73165998 us cost=77209 size=10755297 card=512157)
  1209756 1209756 1209756 PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=142282 pr=43898 pw=0 time=82754565 us cost=73860 size=10755297 card=512157)
  1209756 1209756 1209756 TABLE ACCESS BY LOCAL INDEX ROWID BATCHED MSC_SYSTEM_ITEMS PARTITION: KEY KEY (cr=142282 pr=43898 pw=0 time=82556038 us cost=73860 size=10755297 card=512157)
  1209756 1209756 1209756 INDEX RANGE SCAN MSC_SYSTEM_ITEMS_N3 PARTITION: KEY KEY (cr=5217 pr=2608 pw=0 time=446792 us cost=2114 size=0 card=512157)(object id 149994)


Rows Execution Plan
------- ---------------------------------------------------
  0 SELECT STATEMENT MODE: ALL_ROWS
  8432 HASH (UNIQUE)
1209756 PARTITION RANGE (ITERATOR) PARTITION:KEYKEY
1209756 TABLE ACCESS MODE: ANALYZED (BY LOCAL INDEX ROWID BATCHED)
  OF 'MSC_SYSTEM_ITEMS' (TABLE) PARTITION:KEYKEY
1209756 INDEX MODE: ANALYZED (RANGE SCAN) OF
  'MSC_SYSTEM_ITEMS_N3' (INDEX) PARTITION:KEYKEY

This is the same issue as 12.1.3 Note 2209946.1 - Looks like this was fixed in 12.2.6 bug 21261567 but don't see a fix in 12.2.5 specifically for this issue. Customer has version MSCFPFIN.pld 120.22.12020000.2

EXPECTED BEHAVIOR
-----------------------
Expect this to continue after 10 seconds or less

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Access ASCP workbench
2. Use Condtion = Equals
3. From takes 2 minutes+ to continue

Cause