Continuous Collection Performance Issue in MSCAUTP and MSCAUTPW (Doc ID 2116839.1)

Last updated on JUNE 28, 2017

Applies to:

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

Symptoms

On : 12.2.4 version, Data Collections in Test:

Users are on 12.2.4 and have applied 12.2.4.1 VCP patching. They are upgrading their ASCP instance from 12.1.3 to 12.2.4. In their test environment they are experiencing long running of MSCAUTP - Continuous Collections and MSCAUTPW - Continuous Collection Workers. In their 12.1.3 instance it taking 1 hour to complete, however in 12.2.4 it is taking 5 hours. 5 hours is not an acceptable run time since they are running this process at least 5 times per day. The problem sql is:

SQL ID: 5r65f0b7dntfa Plan Hash: 0

BEGIN MSC_CL_PULL.launch_monitor_cont(:errbuf,:rc,:A0,:A1,:A2,:A3,:A4,:A5,:A6,
  :A7,:A8,:A9,:A10,:A11,:A12,:A13,:A14,:A15,:A16,:A17,:A18,:A19,:A20,:A21,
  :A22,:A23,:A24,:A25,:A26,:A27,:A28,:A29,:A30,:A31,:A32,:A33,:A34,:A35,:A36,
  :A37,:A38,:A39,:A40,:A41); END;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.38 9819.59 0 24 27 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.38 9819.59 0 24 27 1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 213

Elapsed times include waiting on following events:
  Event waited on Times Max. Wait Total Waited
  ---------------------------------------- Waited ---------- ------------
  SQL*Net message to client 2 0.00 0.00
  SQL*Net message from client 2 0.00 0.00
  utl_file I/O 1140 0.00 0.00
  PL/SQL lock timer 4 60.00 130.01
  pipe get 2586 5.10 9687.44
  library cache: mutex X 2 0.00 0.00
********************************************************************************

SQL ID: b0p3k08uy5ghs Plan Hash: 0

BEGIN MRP_CL_REFRESH_SNAPSHOT.WAIT_FOR_REQUEST@OSCART14.TSG.GE.COM(
  :lv_timeout, :lv_retcode);END;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 4091.72 0 0 1 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 4091.72 0 0 1 2

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 213 (recursive depth: 1)

Elapsed times include waiting on following events:
  Event waited on Times Max. Wait Total Waited
  ---------------------------------------- Waited ---------- ------------
  SQL*Net message to dblink 2 0.00 0.00
  SQL*Net message from dblink 2 4091.72 4091.72
********************************************************************************

EXPECTED BEHAVIOR
-----------------------
Expect Continuous Collections to complete in 1 hour

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Upgrade from 12.1.3 to 12.2.4
2. Run continuous collections
3. In 12.1.3 it takes 1 hour. In 12.2.4 it takes 5 hours


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