My Oracle Support Banner

Fcexec Batch: Performance Issue When Processing NIL And Large Volumes Of Cost Changes Exist (Doc ID 2847823.1)

Last updated on FEBRUARY 09, 2022

Applies to:

Oracle Retail Merchandising System - Version 16.0.3 and later
Information in this document applies to any platform.

Goal

When the future cost batch (fcexec) is processing New Item/Location (NIL) cost events it queries the Cost Change tables to determine if the new item/location is also on a future cost change. This happens during the item/location explosion and there is a long running query in the FUTURE_COST_SQL.MERGE_NIL_CC function that looks at COST_SUSP_DETAIL_LOC. However, since it also queries COST_SUSP_SUP_HEAD for dates the result is the query uses the wrong index and ends up querying the entire COST_SUSP_SUP_DETAIL_LOC table using the UK_COST_SUSP_SUP_DETAIL_LOC which lead with COST_CHANGE. For some customers, this table can contain upwards of a 100 million future item/supplier/country/location cost changes detail records spread out across 1000s of cost changes.

This scenario will cause fcexec to run for many hours.

Solution

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
Goal
Solution
References


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