12.2.4 MSC_UI_POST_PLAN - Post Plan Program For UI Performance Issue Inserting To Table MSC_ANALYSIS_AGGREGATE

(Doc ID 2076735.1)

Last updated on JUNE 28, 2017

Applies to:

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

Symptoms

On 12.2.4 User Interface in Test:

Users are on 12.2.4 and have applied VCP Cum Patch 12.2.4.1 - 19549533. When running Inventory Optimization (IO) plans in this upgraded instance, MSC_UI_POST_PLAN - Post Plan Program for UI for is running for 25 hours whereas in 12.1.3 it completes within 80 minutes. Problem SQL:

SQL ID: 1q1vmuukqkpxy Plan Hash: 1759757243

INSERT INTO MSC_ANALYSIS_AGGREGATE ( PLAN_ID, RECORD_TYPE,
  ACHIEVED_SERVICE_LEVEL_QTY1, ACHIEVED_SERVICE_LEVEL_QTY2,
  TARGET_SERVICE_LEVEL, PERIOD_TYPE, WEEK_START_DATE, PERIOD_START_DATE,
  SR_INSTANCE_ID, ORGANIZATION_ID, SR_CAT_INSTANCE_ID, SR_CATEGORY_ID,
  CATEGORY_NAME, INVENTORY_ITEM_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
  CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN ) SELECT MSD.PLAN_ID, :B5 AS
  RECORD_TYPE, SUM(NVL(MSD.OLD_DEMAND_QUANTITY,0) * NVL(MSD.PROBABILITY,1)) *
  100 AS ACHIEVED_SERVICE_LEVEL_QTY1,
  SUM(DECODE(NVL(MSD.USING_REQUIREMENT_QUANTITY, 1) ,0 ,1 ,
  NVL(MSD.USING_REQUIREMENT_QUANTITY, 1)) * NVL(MSD.PROBABILITY,1)) AS
  ACHIEVED_SERVICE_LEVEL_QTY2, DECODE(COUNT(*), 0, 50,
  SUM(NVL(MSD.SERVICE_LEVEL, 50)) / COUNT(*)) AS TARGET_SERVICE_LEVEL, :B4 AS
  PERIOD_TYPE, MPB.BKT_START_DATE AS WEEK_START_DATE, MPSD.PERIOD_START_DATE,
  MSD.SR_INSTANCE_ID AS INSTANCE_ID, MSD.ORGANIZATION_ID AS ORG_ID,
  MIC.SR_INSTANCE_ID AS SR_CAT_INSTANCE_ID, MIC.SR_CATEGORY_ID AS
  SR_CATEGORY_ID, MIC.CATEGORY_NAME AS CATEGORY_NAME, MSD.INVENTORY_ITEM_ID
  AS ITEM_ID, SYSDATE, :B3 , SYSDATE, :B3 , TO_NUMBER(NULL) FROM MSC_PLANS MP,
  MSC_PLAN_ORGANIZATIONS MPO, MSC_DEMANDS MSD, MSC_PLAN_BUCKETS MPB,
  MSC_SYSTEM_ITEMS MSI, MSC_ITEM_CATEGORIES MIC, MSC_TRADING_PARTNERS OWNING,
  MSC_PERIOD_START_DATES MPSD WHERE MP.PLAN_ID = :B2 AND MSD.PLAN_ID =
  MSI.PLAN_ID AND MSD.ORIGINATION_TYPE IN (6,7,8,9,11,12,15,22,28,29,30) AND
  MSD.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID AND MSD.ORGANIZATION_ID =
  MSI.ORGANIZATION_ID AND MSD.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND
...truncated to save space on long sql

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 93147.87 95063.83 8989463 1091118 116002 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 93147.87 95063.84 8989463 1091118 116002 0

Steps To Reproduce
===============
1. Upgrade to 12.2.4 apps and apply 12.2.4.1 patch 19549533
2. Run Inventory Optimizations plans
3. Post Plan for UI takes 25+ hours
4. 12.1.3 instance takes only 80 minutes

Changes

Upgraded from 12.1.3 to 12.2.4

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