ASCP Horizontal Plan Takes 8-10 Minutes To Open (Doc ID 1401332.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Advanced Supply Chain Planning - Version 11.5.10 and later
Information in this document applies to any platform.

Symptoms

Using Advanced Supply Chain Planning / Planner's Workbench, when attempting to open the Horizontal Plan, find that the screen becomes unresponsive for 8-10 minutes, not allowing any work to be accomplished.

Steps:
1. (R) Advanced Supply Chain Planner
2. (N) Supply Chain Plan / Workbench
3. In PWB navigator, select view by Organization
4. Expand plans,
5. ASCP plan "2011PROD-P",
6. any organization (PRD:LAF),
7. any category (RGVC),
8. any items (first 6),
9. right click and  > choose Horizontal Plan > Default

Trace file shows:
SELECT /*+ INDEX(rec, MSC_SUPPLIES_N1) */ LIST.NUMBER5 ITEM_ID, LIST.NUMBER6
ORG_ID, LIST.NUMBER3 INST_ID, DECODE(REC.ORDER_TYPE, :B27 , :B36 , :B25 ,
:B35 , :B23 , :B34 , :B22 , :B34 , :B21 ,:B28 , :B20 , :B28 , :B19 , :B34 ,
:B18 ,:B32 , :B17 , :B33 , :B15 , :B32 , :B1 , :B31 , :B6 , :B30 , :B4 ,
:B29 , :B28 ) ROW_TYPE, DECODE(REC.ORDER_TYPE, :B27 , :B26 , :B25 , :B24 ,
:B23 , :B8 , :B22 , :B8 , :B21 ,:B2 , :B20 , :B2 , :B19 , :B8 , :B18 ,:B14 ,
:B17 , :B16 , :B15 , :B14 , :B13 , :B8 , :B12 , :B8 , :B11 , :B2 , :B10 ,
:B2 , :B9 , :B8 , :B1 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 ) OFFSET,
DATES.CALENDAR_DATE NEW_DATE, DECODE(REC.ORDER_TYPE, :B1 ,
DATES.CALENDAR_DATE, REC.OLD_SCHEDULE_DATE) OLD_DATE,
SUM(DECODE(REC.DISPOSITION_STATUS_TYPE, 2, 0,
DECODE(REC.LAST_UNIT_COMPLETION_DATE, NULL, NVL(REC.FIRM_QUANTITY,
REC.NEW_ORDER_QUANTITY), REC.DAILY_RATE))) NEW_QUANTITY,
SUM(NVL(REC.OLD_ORDER_QUANTITY,0)) OLD_QUANTITY, SUM(0) DOS, 0 COST
FROM
MSC_FORM_QUERY LIST, MSC_TRADING_PARTNERS PARAM, MSC_SYSTEM_ITEMS MSI,
MSC_SUPPLIES REC, MSC_CALENDAR_DATES DATES WHERE (:B42 = 1 OR (:B42 = 2 AND 

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.03 0 0 0 0
Execute 1 0.09 0.10 0 0 0 0
Fetch 706 110.29 1395.63 329764 2538803 0 705
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 708 110.41 1395.76 329764 2538803 0

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
40161384 40161384 40161384 INDEX RANGE SCAN MSC_DEMANDS_N6 PARTITION: KEY KEY (cr=126616 pr=15919 pw=0 time=276873492 us cost=1 size=0 card=5209301)(object id

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