How To Calculate Newitemlocbatch.Sh Chunk Count? (Doc ID 437750.1)

Last updated on AUGUST 17, 2016

Applies to:

Oracle Retail Price Management - Version 11.0.8 and later
Information in this document applies to any platform.
Checked for relevance on Jul-2013


Goal

We wish for some greater clarity on the chunking algorithm used by the newitemlocationbatch.sh. 

Specifically, how were ~129,000 item/loc records in RPM_STAGE_ITEM_LOC and RPM_STAGE_ITEM_LOC_CLEAN tables processed in a single chunk?  The THREAD_LUW_COUNT is set at 1000 and NUM_THREADS=24.  The number of chunks calculated is 1, which does not seem to be correct.

Number of records in RPM_STAGE_ITEM_LOC: ~129,000
Number of records in RPM_STAGE_ITEM_LOC_CLEAN: ~129,000
THREAD_LUW_COUNT: 1000
NUM_THREADS: 24


Scenario 1: RPM_BATCH_CONTROL:

THREAD_LUW_COUNT = 1000
NUM_THREADS = 24

  Number of chunks calculated by the program is 1


Scenario 2: RPM_BATCH_CONTROL:

THREAD_LUW_COUNT = 10
NUM_THREADS = 24

  Number of chunks calculated by the program is 28

Calculating rank:

In the RPM_NEW_ITEM_LOC.STAGE_ITEM_LOCS_TO_PROCESS() function, the following SQL is used to calculate the rank:


insert into rpm_stage_item_loc_clean
select stage_item_loc_id,
ceil(rank/I_chunk_size) thread_num,
item,
loc,
loc_type,
selling_unit_retail,
selling_uom,
item_parent,
dept,
class,
subclass,
diff_1,
diff_2,
diff_3,
diff_4,
sys_date
from (
select rsil.stage_item_loc_id,
dense_rank() over(order by nvl(im.item_parent, im.item)) rank,
rsil.item,
rsil.loc,
rsil.loc_type,
rsil.selling_unit_retail,
rsil.selling_uom,
im.item_parent,
im.dept,
im.class,
im.subclass,
im.diff_1,
im.diff_2,
im.diff_3,
im.diff_4,
sysdate sys_date
from rpm_stage_item_loc rsil,
item_master im
where rsil.item = im.item
and im.status = 'A'
and rsil.status = I_stage_status
) t
order by thread_num, item, loc;


The chunk_size is then calculated using the ceil(rank/THREAD_LUW_COUNT).

Solution

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