Bad Performance on Planning ODS Load (Doc ID 1671631.1)

Last updated on JUNE 28, 2017

Applies to:

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

Symptoms

On : 12.1.3 version, Data Collections

ACTUAL BEHAVIOR
---------------
Planning ODS Load takes long time to run.

On the concurrent log file, we see the following part is running long.

************************************************************
07-MAR 06:13:28 : MSC_DL_TASK_START_PARTIAL (PROCEDURE=MSC_CL_DEMAND_ODS_LOAD.LOAD_DEMAND)
07-MAR 08:30:39 : 137.2 minutes elapsed.
07-MAR 08:30:39 : Linking of Sales Order line in MDS to its Parent Sales orders is successful.....
07-MAR 08:30:39 : 137.2 minutes elapsed.
************************************************************

And also on the trace file, we find that the following SQL is running long.


UPDATE MSC_DEMANDS   SET  parent_id = :demand_id_value  
WHERE
 sr_instance_id   = :instance_id    AND  plan_id = -1    AND  
 origination_type = 6    AND  link_to_line_id  = :link_id_value


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   1140   1483.72    9190.25  154252442  154946620     137581      133209
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1141   1483.72    9190.25  154252442  154946620     137581      133209



EXPECTED BEHAVIOR
-----------------------
Planning ODS Load should be run in a shorter time.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run Planning ODS Load

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