MSRFWOR Refresh Collection Snapshots Performance On WSH_ZONE_REGIONS (Doc ID 427338.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Advanced Supply Chain Planning - Version 11.5.10 and later
Information in this document applies to any platform.
EXECUTABLE:MSRFWOR - Refresh Collection Snapshots


Symptoms

On 11.5.10 in Production and Test in a decentralized environment:

Users made 19 changes to transit times on zones. Data collection (complete refresh) was run and
now the Refresh Collection Snapshots - MSRFWOR - process on the source side has a severe
performance issue (running for over 6 hours). It appears to be hung up in the MAP_REGION_TO_SITE
function in WSH_REGIONS_SEARCH_PKG. It alternates between the sql statements below:

select R.REGION_ID, R.REGION_TYPE, TL.COUNTRY,
TL.COUNTRY_REGION, TL.STATE, TL.CITY,
TL.POSTAL_CODE_FROM, TL.POSTAL_CODE_TO,
TL.ZONE, R.ZONE_LEVEL, R.COUNTRY_CODE,
R.COUNTRY_REGION_CODE, R.STATE_CODE, R.CITY_CODE, 'N'
from WSH_REGIONS R, WSH_REGIONS_TL TL
where R.REGION_ID = TL.REGION_ID and R.REGION_TYPE = :region_type
AND UPPER(R.COUNTRY_CODE) = UPPER(:country_code)
and :postal_code_from between TL.POSTAL_CODE_FROM
and TL.POSTAL_CODE_TO
and TL.LANGUAGE = :lang_code

SELECT DISTINCT R.REGION_ID, R.REGION_TYPE, NULL, NULL, NULL, NULL, NULL, NULL,
RT.ZONE, R.ZONE_LEVEL, NULL, NULL, NULL, NULL, 'N'
FROM WSH_REGIONS R,
WSH_REGIONS_TL RT,
WSH_ZONE_REGIONS Z
WHERE R.REGION_ID = Z.PARENT_REGION_ID
AND Z.REGION_ID = :B2 AND R.REGION_TYPE = 10
AND RT.REGION_ID = R.REGION_ID AND RT.LANGUAGE = NVL(:B1 ,RT.LANGUAGE)

From the log file of MSRFWOR - it shows 4.5 hours performing the following:

Calling function MAP_REGION_TO_SITE with max_lrd:12-DEC-06
Time consumed for calling map_region_to_site:272.2
after calling MRP_MAP_REG_SITE.MAP_REGION_TO_SITE


EXPECTED BEHAVIOR
Expect better performance with MSRFWOR

STEPS
The issue can be reproduced at will with the following steps:
1. Make transit time changes to zones
2. Run data collections

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