My Oracle Support Banner

MRPSCPWB MRP Planners Workbench Performance Querying Individual Items Is In Excess Of 4 Minutes (Doc ID 1484059.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Materials Requirement Planning - Version 11.5.8 and later
Information in this document applies to any platform.
***Checked for relevance on 02-JUN-2014***

Goal

In 11.5.8 MRP Workbench in PROD:

Oracle MRP Workbench response time (MRPSCPWB - Planners Workbench), Querying individual items response time is in excess of 4 minutes. Normal response is 10-20 seconds.This issue is specific to the SCH organization only. The trace file showed the following problem area:

SELECT REC.INVENTORY_ITEM_ID ITEM_ID, REC.ORGANIZATION_ID ORG_ID,
 DECODE(REC.ORDER_TYPE, :B24 , :B31 , :B22 , :B30 , :B20 , :B29 , :B19 ,
 :B29 , :B18 ,:B25 , :B17 , :B25 , :B16 , :B29 , :B14 ,:B27 , :B13 , :B28 ,
 :B11 , :B27 , :B1 , :B26 , :B25 ) ROW_TYPE, DECODE(REC.ORDER_TYPE, :B24 ,
 :B23 , :B22 , :B21 , :B20 , :B15 , :B19 , :B15 , :B18 ,:B2 , :B17 , :B2 ,
 :B16 , :B15 , :B14 ,:B10 , :B13 , :B12 , :B11 , :B10 , :B9 , :B4 , :B8 ,
 :B4 , :B7 , :B4 , :B6 , :B4 , :B5 , :B4 , :B1 , :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, 1,
 DECODE(REC.LAST_UNIT_COMPLETION_DATE, NULL, REC.NEW_ORDER_QUANTITY,
 REC.DAILY_RATE) * DECODE(REC.ORDER_TYPE, :B9 , -1, :B8 , -1, :B7 , -1, :B6 ,
  -1, :B5 , -1, 1) , 0)) NEW_QUANTITY, SUM(NVL(REC.OLD_ORDER_QUANTITY,0))
 OLD_QUANTITY
FROM
MRP_FORM_QUERY LIST, MTL_PARAMETERS PARAM, MRP_RECOMMENDATIONS REC,
 BOM_CALENDAR_DATES DATES WHERE (:B36 = 1 OR (:B36 = 2 AND REC.PROJECT_ID IS
 NULL) OR (DECODE(:B36 , 3,NVL(REC.PLANNING_GROUP,'-23453'), 4,
 NVL(TO_CHAR(REC.PROJECT_ID), '-23453')) = NVL(:B35 ,'-23453')) OR (:B36 = 5
 AND NVL(TO_CHAR(REC.PROJECT_ID), '-23453') = NVL(:B35 ,'-23453') AND
 NVL(REC.TASK_ID, -23453) = NVL(:B34 , -23453))) AND DATES.EXCEPTION_SET_ID =
  PARAM.CALENDAR_EXCEPTION_SET_ID AND DATES.CALENDAR_CODE =
 PARAM.CALENDAR_CODE AND (( REC.LAST_UNIT_COMPLETION_DATE IS NOT NULL AND
 DATES.SEQ_NUM IS NOT NULL ) OR ( REC.LAST_UNIT_COMPLETION_DATE IS NULL ))
 AND DATES.CALENDAR_DATE BETWEEN REC.NEW_SCHEDULE_DATE AND
 NVL(REC.LAST_UNIT_COMPLETION_DATE, REC.NEW_SCHEDULE_DATE) AND
 (REC.NEW_SCHEDULE_DATE < :B33 OR REC.OLD_SCHEDULE_DATE < :B33 ) AND
 REC.COMPILE_DESIGNATOR = LIST.CHAR1 AND REC.INVENTORY_ITEM_ID =
 LIST.NUMBER1 AND REC.ORGANIZATION_ID = LIST.NUMBER2 AND
 PARAM.ORGANIZATION_ID = LIST.NUMBER2 AND LIST.QUERY_ID = :B32 GROUP BY
 REC.INVENTORY_ITEM_ID, REC.ORGANIZATION_ID, DECODE(REC.ORDER_TYPE, :B24 ,
 :B31 , :B22 , :B30 , :B20 , :B29 , :B19 , :B29 , :B18 ,:B25 , :B17 , :B25 ,
 :B16 , :B29 , :B14 ,:B27 , :B13 , :B28 , :B11 , :B27 , :B1 , :B26 , :B25 ),
 DECODE(REC.ORDER_TYPE, :B24 , :B23 , :B22 , :B21 , :B20 , :B15 , :B19 ,
 :B15 , :B18 ,:B2 , :B17 , :B2 , :B16 , :B15 , :B14 ,:B10 , :B13 , :B12 ,
 :B11 , :B10 , :B9 , :B4 , :B8 , :B4 , :B7 , :B4 , :B6 , :B4 , :B5 , :B4 ,
 :B1 , :B3 , :B2 ), DATES.CALENDAR_DATE, DECODE(REC.ORDER_TYPE, :B1 ,
 DATES.CALENDAR_DATE, REC.OLD_SCHEDULE_DATE) UNION ALL SELECT
 MGR.INVENTORY_ITEM_ID ITEM_ID, MGR.ORGANIZATION_ID ORG_ID,
 DECODE(MGR.ORIGINATION_TYPE, 1, :B47 , 2, :B47 , 3, :B47 , 4, :B47 , 5,
 :B50 , 6, :B49 , 7, :B48 , 8, :B44 , 9, :B44 , 10, :B44 , 11, :B44 , 12,
 :B44 , 15, :B44 , 16, :B46 , 17, :B46 , 18, :B46 , 19, :B46 , 20, :B46 , 21,
  :B46 , 22, :B47 , 23, :B46 , 24, :B47 , 25, :B47 , 26, :B46 , :B39 , :B45 ,
  :B44 ) ROW_TYPE, DECODE(MGR.ORIGINATION_TYPE, 1, :B4 , 2, :B4 , 3, :B4 , 4,
  :B4 , 5, :B43 , 6, :B42 , 7, :B41 , 8, :B37 , 9, :B37 , 10, :B37 , 11,
 :B37 , 12, :B37 , 15, :B37 , 16, :B40 , 17, :B40 , 18, :B40 , 19, :B40 , 20,
  :B40 , 21, :B40 , 22, :B4 , 23, :B40 , 24, :B4 , 25, :B4 , 26, :B40 , :B39
 , :B38 , :B37 ) OFFSET, DATES.CALENDAR_DATE NEW_DATE, DATES.CALENDAR_DATE
 OLD_DATE, SUM(DECODE(MGR.ASSEMBLY_DEMAND_COMP_DATE, NULL,
 USING_REQUIREMENTS_QUANTITY, DAILY_DEMAND_RATE)) NEW_QUANTITY, 0
 OLD_QUANTITY FROM MRP_FORM_QUERY LIST, MTL_PARAMETERS PARAM,
 MRP_GROSS_REQUIREMENTS MGR, BOM_CALENDAR_DATES DATES WHERE (:B36 = 1 OR
 (:B36 = 2 AND MGR.PROJECT_ID IS NULL) OR (DECODE(:B36 , 3,
 NVL(MGR.PLANNING_GROUP,'-23453'), 4,NVL(TO_CHAR(MGR.PROJECT_ID), '-23453'))
 = NVL(:B35 ,'-23453')) OR (:B36 = 5 AND NVL(TO_CHAR(MGR.PROJECT_ID),
 '-23453') = NVL(:B35 ,'-23453') AND NVL(MGR.TASK_ID, -23453) = NVL(:B34 ,
 -23453))) AND DATES.EXCEPTION_SET_ID = PARAM.CALENDAR_EXCEPTION_SET_ID AND
 DATES.CALENDAR_CODE = PARAM.CALENDAR_CODE AND ((
 MGR.ASSEMBLY_DEMAND_COMP_DATE IS NOT NULL AND DATES.SEQ_NUM IS NOT NULL )
 OR ( MGR.ASSEMBLY_DEMAND_COMP_DATE IS NULL )) AND DATES.CALENDAR_DATE
 BETWEEN MGR.USING_ASSEMBLY_DEMAND_DATE AND
 NVL(MGR.ASSEMBLY_DEMAND_COMP_DATE, MGR.USING_ASSEMBLY_DEMAND_DATE) AND
 MGR.USING_ASSEMBLY_DEMAND_DATE < :B33 AND MGR.COMPILE_DESIGNATOR =
 LIST.CHAR1 AND MGR.INVENTORY_ITEM_ID = LIST.NUMBER1 AND MGR.ORGANIZATION_ID
 = LIST.NUMBER2 AND PARAM.ORGANIZATION_ID = LIST.NUMBER2 AND LIST.QUERY_ID =
 :B32 GROUP BY MGR.INVENTORY_ITEM_ID, MGR.ORGANIZATION_ID,
 DECODE(MGR.ORIGINATION_TYPE, 1, :B47 , 2, :B47 , 3, :B47 , 4, :B47 , 5,
 :B50 , 6, :B49 , 7, :B48 , 8, :B44 , 9, :B44 , 10, :B44 , 11, :B44 , 12,
 :B44 , 15, :B44 , 16, :B46 , 17, :B46 , 18, :B46 , 19, :B46 , 20, :B46 , 21,
  :B46 , 22, :B47 , 23, :B46 , 24, :B47 , 25, :B47 , 26, :B46 , :B39 , :B45 ,
  :B44 ), DECODE(MGR.ORIGINATION_TYPE, 1, :B4 , 2, :B4 , 3, :B4 , 4, :B4 , 5,
  :B43 , 6, :B42 , 7, :B41 , 8, :B37 , 9, :B37 , 10, :B37 , 11, :B37 , 12,
 :B37 , 15, :B37 , 16, :B40 , 17, :B40 , 18, :B40 , 19, :B40 , 20, :B40 , 21,
  :B40 , 22, :B4 , 23, :B40 , 24, :B4 , 25, :B4 , 26, :B40 , :B39 , :B38 ,
 :B37 ), DATES.CALENDAR_DATE, DATES.CALENDAR_DATE, 0 UNION ALL SELECT
 AVAIL.INVENTORY_ITEM_ID ITEM_ID, AVAIL.ORGANIZATION_ID ORG_ID, :B52
 ROW_TYPE, :B51 OFFSET, AVAIL.SCHEDULE_DATE NEW_DATE, AVAIL.SCHEDULE_DATE
 OLD_DATE, AVAIL.QUANTITY_AVAILABLE NEW_QUANTITY, 0 OLD_QUANTITY FROM
 MRP_FORM_QUERY LIST, MRP_AVAILABLE_TO_PROMISE AVAIL WHERE
 AVAIL.SCHEDULE_DATE < :B33 AND AVAIL.ORGANIZATION_ID = LIST.NUMBER2 AND
 AVAIL.COMPILE_DESIGNATOR = LIST.CHAR1 AND AVAIL.INVENTORY_ITEM_ID =
 LIST.NUMBER1 AND LIST.QUERY_ID = :B32 UNION ALL SELECT
 ITEMS.INVENTORY_ITEM_ID ITEM_ID, ITEMS.ORGANIZATION_ID ORG_ID, :B56
 ROW_TYPE, :B55 OFFSET, TO_DATE(1, 'J') NEW_DATE, TO_DATE(1, 'J') OLD_DATE,
 ITEMS.NETTABLE_QUANTITY NEW_QUANTITY, 0 OLD_QUANTITY FROM MRP_PLANS PLANS,
 MRP_ONHAND_QUANTITIES ITEMS, MRP_FORM_QUERY LIST WHERE ((:B36 = 1 ) OR
 (:B36 = 2 AND ITEMS.PROJECT_ID IS NULL) OR (DECODE(:B36 , 3,
 NVL(ITEMS.PLANNING_GROUP,'-23453'), 4, NVL(TO_CHAR(ITEMS.PROJECT_ID),
 '-23453')) = NVL(:B35 ,'-23453')) OR ( :B36 = 5 AND
 NVL(TO_CHAR(ITEMS.PROJECT_ID),'-23453') = NVL(:B35 ,'-23453') AND
 NVL(ITEMS.TASK_ID,-23453) = NVL(:B34 ,-23453))) AND
 PLANS.CURR_RESERVATION_LEVEL IN (1, 2, 3) AND PLANS.ORGANIZATION_ID = :B54
 AND PLANS.COMPILE_DESIGNATOR = :B53 AND ITEMS.ORGANIZATION_ID =
 LIST.NUMBER2 AND ITEMS.COMPILE_DESIGNATOR = LIST.CHAR1 AND
 ITEMS.INVENTORY_ITEM_ID = LIST.NUMBER1 AND LIST.QUERY_ID = :B32 UNION ALL
 SELECT ITEMS.INVENTORY_ITEM_ID ITEM_ID, ITEMS.ORGANIZATION_ID ORG_ID, :B56
 ROW_TYPE, :B55 OFFSET, TO_DATE(1, 'J') NEW_DATE, TO_DATE(1, 'J') OLD_DATE,
 ITEMS.NETTABLE_INVENTORY_QUANTITY NEW_QUANTITY, 0 OLD_QUANTITY FROM
 MRP_PLANS PLANS, MRP_SYSTEM_ITEMS ITEMS, MRP_FORM_QUERY LIST WHERE :B36 = 1
 AND (PLANS.CURR_RESERVATION_LEVEL = 4 OR PLANS.CURR_RESERVATION_LEVEL IS
 NULL) AND PLANS.ORGANIZATION_ID = :B54 AND PLANS.COMPILE_DESIGNATOR = :B53
 AND ITEMS.ORGANIZATION_ID = LIST.NUMBER2 AND ITEMS.COMPILE_DESIGNATOR =
 LIST.CHAR1 AND ITEMS.INVENTORY_ITEM_ID = LIST.NUMBER1 AND LIST.QUERY_ID =
 :B32 UNION ALL SELECT SAFETY.INVENTORY_ITEM_ID ITEM_ID,
 SAFETY.ORGANIZATION_ID ORG_ID, :B58 ROW_TYPE, :B57 OFFSET,
 SAFETY.PERIOD_START_DATE NEW_DATE, SAFETY.PERIOD_START_DATE OLD_DATE,
 SAFETY.SAFETY_STOCK_QUANTITY NEW_QUANTITY, 0 OLD_QUANTITY FROM
 MRP_SAFETY_STOCK SAFETY, MRP_FORM_QUERY LIST WHERE SAFETY.PERIOD_START_DATE
 < :B33 AND SAFETY.ORGANIZATION_ID = LIST.NUMBER2 AND
 SAFETY.COMPILE_DESIGNATOR = LIST.CHAR1 AND SAFETY.INVENTORY_ITEM_ID =
 LIST.NUMBER1 AND LIST.QUERY_ID = :B32 UNION ALL SELECT
 SCHED.INVENTORY_ITEM_ID ITEM_ID, SCHED.ORGANIZATION_ID ORG_ID, :B60
 ROW_TYPE, :B59 OFFSET, DATES.CALENDAR_DATE NEW_DATE, DATES.CALENDAR_DATE
 OLD_DATE, SCHED.DAILY_RATE NEW_QUANTITY, 0 OLD_QUANTITY FROM MRP_FORM_QUERY
 LIST, MTL_PARAMETERS PARAM, MRP_AGGREGATE_RATES SCHED, BOM_CALENDAR_DATES
 DATES WHERE DATES.EXCEPTION_SET_ID = PARAM.CALENDAR_EXCEPTION_SET_ID AND
 DATES.CALENDAR_CODE = PARAM.CALENDAR_CODE AND DATES.SEQ_NUM IS NOT NULL AND
 DATES.CALENDAR_DATE BETWEEN SCHED.FIRST_UNIT_COMPLETION_DATE AND
 SCHED.LAST_UNIT_COMPLETION_DATE AND SCHED.FIRST_UNIT_COMPLETION_DATE < :B33
 AND SCHED.COMPILE_DESIGNATOR = LIST.CHAR1 AND SCHED.INVENTORY_ITEM_ID =
 LIST.NUMBER1 AND SCHED.ORGANIZATION_ID = PARAM.ORGANIZATION_ID AND
 PARAM.ORGANIZATION_ID = LIST.NUMBER2 AND LIST.QUERY_ID = :B32 UNION ALL
 SELECT LIST.NUMBER1, LIST.NUMBER2, :B56 , :B55 , TO_DATE(1, 'J'), TO_DATE(1,
  'J'), 0, 0 FROM MRP_FORM_QUERY LIST WHERE LIST.QUERY_ID = :B32 ORDER BY 1,
 2, 5, 3


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.13 0.10 0 0 0 0
Execute 1 0.13 0.12 0 0 0 0
Fetch 27 1168.86 1181.33 2511 13508329 0 26
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 29 1169.12 1181.56 2511 13508329 0 26

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 183 (APPS) (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
  26 SORT ORDER BY
  26 UNION-ALL
  0 SORT GROUP BY
  0 TABLE ACCESS BY INDEX ROWID MRP_RECOMMENDATIONS
245727993 NESTED LOOPS
  4748 NESTED LOOPS
  8765 MERGE JOIN CARTESIAN
  8765 TABLE ACCESS FULL BOM_CALENDAR_DATES
  8765 BUFFER SORT
  1 TABLE ACCESS BY INDEX ROWID MRP_FORM_QUERY
  1 INDEX RANGE SCAN MRP_FORM_QUERY_N1 (object id 39673)
  4748 TABLE ACCESS BY INDEX ROWID MTL_PARAMETERS
  8765 INDEX UNIQUE SCAN MTL_PARAMETERS_U1 (object id 38682)
245723244 INDEX RANGE SCAN MRP_RECOMMENDATIONS_N2 (object id 39875)
  24 SORT GROUP BY
  108 TABLE ACCESS BY INDEX ROWID MRP_GROSS_REQUIREMENTS
 58918 NESTED LOOPS
  4748 NESTED LOOPS
  8765 MERGE JOIN CARTESIAN
  8765 TABLE ACCESS FULL BOM_CALENDAR_DATES
  8765 BUFFER SORT
  1 TABLE ACCESS BY INDEX ROWID MRP_FORM_QUERY
  1 INDEX RANGE SCAN MRP_FORM_QUERY_N1 (object id 39673)
  4748 TABLE ACCESS BY INDEX ROWID MTL_PARAMETERS
  8765 INDEX UNIQUE SCAN MTL_PARAMETERS_U1 (object id 38682)
 54169 INDEX RANGE SCAN MRP_GROSS_REQUIREMENTS_N1 (object id 39752)
  0 TABLE ACCESS BY INDEX ROWID MRP_AVAILABLE_TO_PROMISE
  2 NESTED LOOPS
  1 TABLE ACCESS BY INDEX ROWID MRP_FORM_QUERY
  1 INDEX RANGE SCAN MRP_FORM_QUERY_N1 (object id 39673)
  0 INDEX RANGE SCAN MRP_AVAILABLE_TO_PROMISE_U1 (object id 39704)
  0 TABLE ACCESS BY INDEX ROWID MRP_ONHAND_QUANTITIES
  1 NESTED LOOPS
  0 NESTED LOOPS
  0 TABLE ACCESS BY INDEX ROWID MRP_PLANS
  1 INDEX UNIQUE SCAN MRP_PLANS_U1 (object id 39866)
  0 TABLE ACCESS BY INDEX ROWID MRP_FORM_QUERY
  0 INDEX RANGE SCAN MRP_FORM_QUERY_N1 (object id 39673)
  0 INDEX RANGE SCAN MRP_ONHAND_QUANTITIES_N1 (object id 40161)
  1 FILTER
  1 NESTED LOOPS
  1 NESTED LOOPS
  1 TABLE ACCESS BY INDEX ROWID MRP_PLANS
  1 INDEX UNIQUE SCAN MRP_PLANS_U1 (object id 39866)
  1 TABLE ACCESS BY INDEX ROWID MRP_FORM_QUERY
  1 INDEX RANGE SCAN MRP_FORM_QUERY_N1 (object id 39673)
  1 TABLE ACCESS BY INDEX ROWID MRP_SYSTEM_ITEMS
  1 INDEX UNIQUE SCAN MRP_SYSTEM_ITEMS_U1 (object id 40004)
  0 TABLE ACCESS BY INDEX ROWID MRP_SAFETY_STOCK
  2 NESTED LOOPS
  1 TABLE ACCESS BY INDEX ROWID MRP_FORM_QUERY
  1 INDEX RANGE SCAN MRP_FORM_QUERY_N1 (object id 39673)
  0 INDEX RANGE SCAN MRP_SAFETY_STOCK_U1 (object id 39942)
  0 TABLE ACCESS BY INDEX ROWID BOM_CALENDAR_DATES
  1 NESTED LOOPS
  0 NESTED LOOPS
  0 NESTED LOOPS
  0 TABLE ACCESS BY INDEX ROWID MRP_AGGREGATE_RATES
  0 INDEX SKIP SCAN MRP_AGGREGATE_RATES_U1 (object id 39683)
  0 TABLE ACCESS BY INDEX ROWID MRP_FORM_QUERY
  0 INDEX RANGE SCAN MRP_FORM_QUERY_N1 (object id 39673)
  0 TABLE ACCESS BY INDEX ROWID MTL_PARAMETERS
  0 INDEX UNIQUE SCAN MTL_PARAMETERS_U1 (object id 38682)
  0 INDEX RANGE SCAN BOM_CALENDAR_DATES_U1 (object id 30235)
  1 TABLE ACCESS BY INDEX ROWID MRP_FORM_QUERY
  1 INDEX RANGE SCAN MRP_FORM_QUERY_N1 (object id 39673)


Rows Execution Plan
------- ---------------------------------------------------
  0 SELECT STATEMENT GOAL: CHOOSE
  26 SORT (ORDER BY)
  26 UNION-ALL
  0 SORT (GROUP BY)
  0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
  'MRP_RECOMMENDATIONS'
245727993 NESTED LOOPS
  4748 NESTED LOOPS
  8765 MERGE JOIN (CARTESIAN)
  8765 TABLE ACCESS GOAL: ANALYZED (FULL) OF
  'BOM_CALENDAR_DATES'
  8765 BUFFER (SORT)
  1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
  OF 'MRP_FORM_QUERY'
  1 INDEX GOAL: ANALYZED (RANGE SCAN) OF
  'MRP_FORM_QUERY_N1' (NON-UNIQUE)
  4748 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
  'MTL_PARAMETERS'
  8765 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
  'MTL_PARAMETERS_U1' (UNIQUE)
245723244 INDEX GOAL: ANALYZED (RANGE SCAN) OF
  'MRP_RECOMMENDATIONS_N2' (NON-UNIQUE)

Note the problem is with index MRP_RECOMMENDATIONS_N2 querying 245+ million rows. How was this resolved?
 

Solution

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
Goal
Solution


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.