Collections ODS Load Performance Problem - When Updating MSC_REGION_LOCATIONS Profile MSC:Collection Window for Trading Partner (Doc ID 563046.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Advanced Supply Chain Planning - Version 11.5.10.2 and later
Information in this document applies to any platform.
EXECUTABLE:MSCPDC - Planning ODS Load
EXECUTABLE:MSCPDCW - Planning ODS Load Worker


Symptoms

11.5.10 in Production and Test

PROBLEM

Data Collections is taking a very long time in the ODS Load, and performance is degrading over time.

Noted that MSC_REGION_LOCATIONS has grown to 53 million rows and WSH_REGION_LOCATIONS has 300K rows
-- These tables should have the same count of rows

Support duplicated the issue internally where count of tables does not match and MSC table is appended each time collections is run.


STEPS
Run data collections with profile MSC:Collection Window for Trading Partner Changes (Days) > ZERO
Note that number of rows in MSC_REGION_LOCATIONS is appended.

Example:

  1. If WSH_REGION_LOCATIONS has 300K rows, then run first time with profile MSC:Collection Window for Trading Partner Changes (Days) = 0
  2. See that MSC_REGION_LOCATIONS has 300K rows and tables match.
  3. Set profile MSC:Collection Window for Trading Partner Changes (Days) = 30
  4. Run data collections again and find that MSC_REGION_LOCATIONS has 600K rows
  5. Run data collections again and find that MSC_REGION_LOCATIONS has 900K rows

DETAILS:
Customer reports that when monitoring the collection, we found the ODS load is spending most of its time in running following query:

UPDATE msc_region_locations mrl1
SET (mrl1.region_type, mrl1.region_id, mrl1.parent_region_flag,
mrl1.location_source, mrl1.exception_type, mrl1.refresh_number,
mrl1.last_updated_by, mrl1.last_update_date, mrl1.last_update_login) =
(SELECT msrl.region_type, msrl.region_id, msrl.parent_region_flag,
msrl.location_source, msrl.exception_type,
:v_last_collection_id, :v_current_user, :v_current_date,
:v_current_user
FROM msc_st_region_locations msrl
WHERE msrl.sr_instance_id = :v_instance_id
AND mrl1.sr_instance_id = msrl.sr_instance_id
AND mrl1.region_type = msrl.region_type
AND mrl1.location_id = msrl.location_id)
WHERE EXISTS (
SELECT 1
FROM msc_st_region_locations msrl1
WHERE mrl1.sr_instance_id = msrl1.sr_instance_id
AND mrl1.region_type = msrl1.region_type
AND mrl1.location_id = msrl1.location_id)
AND mrl1.sr_instance_id = :v_instance_id;

On further analysis, we found msc_region_locations appending the data every time we run a collection.
This is already having 53 million records now, whereas there are only 300K records in msc_st_region_locations.

BUSINESS IMPACT
Data Collections takes too long to complete and data is not correct in the table.

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