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
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.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.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,
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
Expect better performance with MSRFWOR
The issue can be reproduced at will with the following steps:
1. Make transit time changes to zones
2. Run data collections
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
|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.|