Intermittent Performance Issue For ATP Enabled ASCP Plans - MSC_ATP_PDS_SUMM - ATP Post Plan Processing (Doc ID 2172208.1)

Last updated on OCTOBER 20, 2022

Applies to:

Symptoms

On : 12.1.3 and 12.2.4 version, Memory Based Planner in UAT:

 Users are on 12.2.4 and have applied VCP Cum Patch 12.2.4.1 - 19549533 - Also applied one off patch 23480123:R12.MSC.C which gave them MSONWL64.exe 120.15.12020000.20.1202060.31 - They recently upgraded from 12.1.3 to 12.2.4 - They are seeing an intermittent problem with performance for ATP enabled plan runs on concurrent request MSC_ATP_PDS_SUMM: ATP Post Plan Processing

**Starts**20-JUL-2016 20:26:10
**Ends**20-JUL-2016 21:51:46

So this took 1 hr 25 minutes - expected is 5 minutes. They had this same problem in 12.1.3 and DEV provided them with one off patch 22083706:R12.MSC.B to fix the problem. Customer current file versions are:

MSCRATPB.pls 120.48.12020000.2
MSCAPEGB.pls 120.13.12020000.4

1. Top two time consuming queries are :
Query 1)
--------------------
INSERT INTO MSC_ATP_DETAIL_PEG_TEMP ( REFERENCE_ITEM_ID, BASE_ITEM_ID,
...
OVERCONSUMPTION_QTY )
SELECT /*+ ORDERED */ DISTINCT :B3 , MSI.BASE_ITEM_ID,
...
...
MSI.REPLENISH_TO_ORDER_FLAG = 'Y'
---------------------

Query 2)
---------------------
SELECT SD_DATE, SUM(SD_QTY)
FROM
( SELECT TRUNC(DECODE(D.RECORD_SOURCE, 2, NVL(D.SCHEDULE_SHIP_DATE,
...
...
TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) + 1)
) GROUP BY SD_DATE ORDER BY SD_DATE
---------------------

EXPECTED BEHAVIOR
-----------------------
Expect post plan to run in 5 minutes or less

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Upgrade from 12.1.3 to 12.2.4
2. Run ATP enabled ASCP plans
3. Intermittently run into performance problems with ATP Post Plan Processing

Cause

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