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

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

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