My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.