MSCFNSCW -Planner Workbench Performance Issue When Querying By Order Number (Doc ID 473675.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Advanced Supply Chain Planning - Version 11.5.10.2 to 11.5.10.2 [Release 11.5]
Oracle Advanced Supply Chain Planning - Version 12.1.3.9 to 12.1.3.9 [Release 12.1]
Oracle Advanced Supply Chain Planning - Version 12.1.3 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.
***Checked for relevance on 3-Jul-2013***

FORM:MSCFNSCW.FMB - Planner Workbench



Symptoms

11.5.10 with Engine/UI Rollup patch #26 - 6332127

Also in 12.1.3.9

PROBLEM
Querying on an order number in the workbench is very slow

STEPS
ASCP / Supply Chain Plan / Planner Workbench
open plan, click supply/demand, query using Order Number - Equals - <order number>


BUSINESS IMPACT
Cannot use this functionality in the workbench effectively.

Verified by tkprof of Trace files

SELECT DECODE(:B6 , 5, DECODE( :B1 , NULL, TO_CHAR(:B3 ), :B1 ||'
'||TO_CHAR(:B3 )) , 14, DECODE(:B4 , -1, :B1 ,
DECODE(SUBSTR(MSC_GET_NAME.GET_ORDER_NUMBER( :B5 , :B4 , :B2 , 1),1,240),
NULL, TO_CHAR(:B2 ), SUBSTR(MSC_GET_NAME.GET_ORDER_NUMBER(:B5 , :B4 , :B2 ,
1),1,240)||' ' || TO_CHAR(:B2 ))), 17,
DECODE(SUBSTR(MSC_GET_NAME.GET_ORDER_NUMBER( :B5 , :B4 , :B2 , 1),1,240),
NULL, TO_CHAR(:B2 ), SUBSTR(MSC_GET_NAME.GET_ORDER_NUMBER( :B5 , :B4 , :B2 ,
1),1,240)||' ' || TO_CHAR(:B2 )), 51,TO_CHAR(:B3 ), 52,TO_CHAR(:B3 ), 15,
TO_CHAR(:B2 ), 16,TO_CHAR(:B2 ), 28,TO_CHAR(:B2 ), :B1 )
FROM
DUAL


call     count       cpu    elapsed       disk      query    current    rows
------- ------  -------- ---------- ---------- ---------- ----------  --------
Parse        1      0.00       0.00          0          0          0       0
Execute 288387    815.05     856.55          0          0          0       0
Fetch   288387     19.97      21.35         67     865161          0  288387
------- ------  -------- ---------- ---------- ---------- ----------  --------
total   576775    835.02     877.90         67     865161          0  288387

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