Collections ODS Load Performance Problem - When Updating MSC_REGION_LOCATIONS Profile MSC:Collection Window for Trading Partner
Last updated on JUNE 28, 2017
Applies to:Oracle Advanced Supply Chain Planning - Version 126.96.36.199 and later
Information in this document applies to any platform.
EXECUTABLE:MSCPDC - Planning ODS Load
EXECUTABLE:MSCPDCW - Planning ODS Load Worker
11.5.10 in Production and Test
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.
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.
- If WSH_REGION_LOCATIONS has 300K rows, then run first time with profile MSC:Collection Window for Trading Partner Changes (Days) = 0
- See that MSC_REGION_LOCATIONS has 300K rows and tables match.
- Set profile MSC:Collection Window for Trading Partner Changes (Days) = 30
- Run data collections again and find that MSC_REGION_LOCATIONS has 600K rows
- Run data collections again and find that MSC_REGION_LOCATIONS has 900K rows
Customer reports that when monitoring the collection, we found the ODS load is spending most of its time in running following query:
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,
:v_last_collection_id, :v_current_user, :v_current_date,
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 (
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.
Data Collections takes too long to complete and data is not correct in the table.
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