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

Last updated on AUGUST 01, 2024

Applies to:

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:

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

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