My Oracle Support Banner

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

Last updated on AUGUST 19, 2019

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@<HOSTNAME>(
  :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


Changes

 

Cause

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
Symptoms
Changes
Cause
Solution
References


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