As described in the operations guide, location moves allow you to move location from one zone into the different zone.
This is one of the most complex batches in Oracle Retail Suite.
The batch is running in two phases. First it is needed to run Location Move Schedule batch, and after that the main Location Move batch.
In the whole process I did many optimization, and here I’ll describe one of the most recent.
When working on the large locations, Schedule batch part of the Location move batch take 6+ hours to finish.
One of the issues is inside RPM_LOC_MOVE_SQL, function POPULATE_GTT.
One of the problematic SQLs is the following one:
--original vanilla code
insert into rpm_future_retail_gtt
(price_event_id,
future_retail_id,
dept,
class,
subclass,
item,
diff_id,
item_parent,
zone_node_type,
location,
zone_id,
action_date,
selling_retail,
selling_retail_currency,
selling_uom,
multi_units,
multi_unit_retail,
multi_unit_retail_currency,
multi_selling_uom,
clear_retail,
clear_retail_currency,
clear_uom,
simple_promo_retail,
simple_promo_retail_currency,
simple_promo_uom,
complex_promo_retail,
complex_promo_retail_currency,
complex_promo_uom,
price_change_id,
price_change_display_id,
pc_exception_parent_id,
pc_change_type,
pc_change_amount,
pc_change_currency,
pc_change_percent,
pc_change_selling_uom,
pc_null_multi_ind,
pc_multi_units,
pc_multi_unit_retail,
pc_multi_unit_retail_currency,
pc_multi_selling_uom,
pc_price_guide_id,
clearance_id,
clearance_display_id,
clear_mkdn_index,
clear_start_ind,
clear_change_type,
clear_change_amount,
clear_change_currency,
clear_change_percent,
clear_change_selling_uom,
clear_price_guide_id,
loc_move_from_zone_id,
loc_move_to_zone_id,
location_move_id,
lock_version,
rfr_rowid,
timeline_seq,
on_simple_promo_ind,
on_complex_promo_ind,
max_hier_level,
cur_hier_level)
select I_loc_move_id,
DECODE(fr_rank,
3, fr_il.future_retail_id,
RPM_FUTURE_RETAIL_SEQ.NEXTVAL) future_retail_id,
fr_il.dept,
fr_il.class,
fr_il.subclass,
fr_il.item,
fr_il.diff_id,
fr_il.item_parent,
fr_il.zone_node_type,
fr_il.location,
fr_il.zone_id,
fr_il.action_date,
fr_il.selling_retail,
fr_il.selling_retail_currency,
fr_il.selling_uom,
fr_il.multi_units,
fr_il.multi_unit_retail,
fr_il.multi_unit_retail_currency,
fr_il.multi_selling_uom,
fr_il.clear_retail,
fr_il.clear_retail_currency,
fr_il.clear_uom,
fr_il.simple_promo_retail,
fr_il.simple_promo_retail_currency,
fr_il.simple_promo_uom,
fr_il.complex_promo_retail,
fr_il.complex_promo_retail_currency,
fr_il.complex_promo_uom,
fr_il.price_change_id,
fr_il.price_change_display_id,
fr_il.pc_exception_parent_id,
fr_il.pc_change_type,
fr_il.pc_change_amount,
fr_il.pc_change_currency,
fr_il.pc_change_percent,
fr_il.pc_change_selling_uom,
fr_il.pc_null_multi_ind,
fr_il.pc_multi_units,
fr_il.pc_multi_unit_retail,
fr_il.pc_multi_unit_retail_currency,
fr_il.pc_multi_selling_uom,
fr_il.pc_price_guide_id,
fr_il.clearance_id,
fr_il.clearance_display_id,
fr_il.clear_mkdn_index,
fr_il.clear_start_ind,
fr_il.clear_change_type,
fr_il.clear_change_amount,
fr_il.clear_change_currency,
fr_il.clear_change_percent,
fr_il.clear_change_selling_uom,
fr_il.clear_price_guide_id,
fr_il.loc_move_from_zone_id,
fr_il.loc_move_to_zone_id,
fr_il.location_move_id,
fr_il.lock_version,
DECODE(fr_rank,
3, fr_il.rfr_rowid,
NULL) rfr_rowid,
NULL timeline_seq,
fr_il.on_simple_promo_ind,
fr_il.on_complex_promo_ind,
fr_il.max_hier_level,
RPM_CONSTANTS.FR_HIER_ITEM_LOC
from (select rfr.future_retail_id,
rfr.dept,
rfr.class,
rfr.subclass,
t.to_item item,
t.to_diff_id diff_id,
t.to_item_parent item_parent,
t.to_zone_node_type zone_node_type,
t.to_location location,
t.to_zone_id zone_id,
rfr.action_date,
rfr.selling_retail,
rfr.selling_retail_currency,
rfr.selling_uom,
rfr.multi_units,
rfr.multi_unit_retail,
rfr.multi_unit_retail_currency,
rfr.multi_selling_uom,
rfr.clear_retail,
rfr.clear_retail_currency,
rfr.clear_uom,
rfr.simple_promo_retail,
rfr.simple_promo_retail_currency,
rfr.simple_promo_uom,
rfr.complex_promo_retail,
rfr.complex_promo_retail_currency,
rfr.complex_promo_uom,
rfr.price_change_id,
rfr.price_change_display_id,
rfr.pc_exception_parent_id,
rfr.pc_change_type,
rfr.pc_change_amount,
rfr.pc_change_currency,
rfr.pc_change_percent,
rfr.pc_change_selling_uom,
rfr.pc_null_multi_ind,
rfr.pc_multi_units,
rfr.pc_multi_unit_retail,
rfr.pc_multi_unit_retail_currency,
rfr.pc_multi_selling_uom,
rfr.pc_price_guide_id,
rfr.clearance_id,
rfr.clearance_display_id,
rfr.clear_mkdn_index,
rfr.clear_start_ind,
rfr.clear_change_type,
rfr.clear_change_amount,
rfr.clear_change_currency,
rfr.clear_change_percent,
rfr.clear_change_selling_uom,
rfr.clear_price_guide_id,
rfr.loc_move_from_zone_id,
rfr.loc_move_to_zone_id,
rfr.location_move_id,
rfr.lock_version,
rfr.rowid rfr_rowid,
NULL timeline_seq,
rfr.on_simple_promo_ind,
rfr.on_complex_promo_ind,
rfr.max_hier_level,
t.fr_rank,
MAX(t.fr_rank) OVER (PARTITION BY t.to_item,
t.to_location,
t.to_zone_node_type) max_fr_rank
from (-- get IL data from PZ timelines
select 0 fr_rank,
gtt.dept,
gtt.item_parent from_item,
NULL from_diff_id,
gtt.zone_id from_location,
RPM_CONSTANTS.ZONE_NODE_TYPE_ZONE from_zone_node_type,
gtt.item to_item,
gtt.item_parent to_item_parent,
gtt.diff_id to_diff_id,
gtt.location to_location,
gtt.zone_node_type to_zone_node_type,
gtt.zone_id to_zone_id
from rpm_merch_node_zone_node_gtt gtt
where gtt.item_parent is NOT NULL
and gtt.zone_id is NOT NULL
union all
-- get IL data from PDZ timelines
select distinct
1 fr_rank,
gtt.dept,
gtt.item_parent from_item,
gtt.diff_id from_diff_id,
gtt.zone_id from_location,
RPM_CONSTANTS.ZONE_NODE_TYPE_ZONE from_zone_node_type,
gtt.item to_item,
gtt.item_parent to_item_parent,
gtt.diff_id to_diff_id,
gtt.location to_location,
gtt.zone_node_type to_zone_node_type,
gtt.zone_id to_zone_id
from rpm_merch_node_zone_node_gtt gtt
where gtt.item_parent is NOT NULL
and gtt.diff_id is NOT NULL
and gtt.zone_id is NOT NULL
union all
-- get IL data from PL timelines
select distinct
1 fr_rank,
gtt.dept,
gtt.item_parent from_item,
NULL from_diff_id,
gtt.location from_location,
gtt.zone_node_type from_zone_node_type,
gtt.item to_item,
gtt.item_parent to_item_parent,
gtt.diff_id to_diff_id,
gtt.location to_location,
gtt.zone_node_type to_zone_node_type,
gtt.zone_id to_zone_id
from rpm_merch_node_zone_node_gtt gtt
where gtt.item_parent is NOT NULL
union all
-- get IL data from IZ timelines
select distinct
2 fr_rank,
gtt.dept,
gtt.item from_item,
NULL from_diff_id,
gtt.zone_id from_location,
RPM_CONSTANTS.ZONE_NODE_TYPE_ZONE from_zone_node_type,
gtt.item to_item,
gtt.item_parent to_item_parent,
gtt.diff_id to_diff_id,
gtt.location to_location,
gtt.zone_node_type to_zone_node_type,
gtt.zone_id to_zone_id
from rpm_merch_node_zone_node_gtt gtt
where gtt.zone_id is NOT NULL
union all
-- get IL data from PDL timelines
select distinct
2 fr_rank,
gtt.dept,
gtt.item_parent from_item,
gtt.diff_id from_diff_id,
gtt.location from_location,
gtt.zone_node_type from_zone_node_type,
gtt.item to_item,
gtt.item_parent to_item_parent,
gtt.diff_id to_diff_id,
gtt.location to_location,
gtt.zone_node_type to_zone_node_type,
gtt.zone_id to_zone_id
from rpm_merch_node_zone_node_gtt gtt
where gtt.item_parent is NOT NULL
and gtt.diff_id is NOT NULL
union all
-- get IL data from IL timelines
select distinct
3 fr_rank,
gtt.dept,
gtt.item from_item,
NULL from_diff_id,
gtt.location from_location,
gtt.zone_node_type from_zone_node_type,
gtt.item to_item,
gtt.item_parent to_item_parent,
gtt.diff_id to_diff_id,
gtt.location to_location,
gtt.zone_node_type to_zone_node_type,
gtt.zone_id to_zone_id
from rpm_merch_node_zone_node_gtt gtt) t,
rpm_future_retail rfr
where rfr.dept = t.dept
and rfr.item = t.from_item
and NVL(rfr.diff_id, -999) = NVL(t.from_diff_id, -999)
and rfr.location = t.from_location
and rfr.zone_node_type = t.from_zone_node_type) fr_il
where fr_rank = max_fr_rank;
The code is fairly complex, and includes several GTT tables that are most difficult to optimize, as they are ON COMMIT DELETE ROWS.
For that type of GTT tables it is not possible to gather statistics, and CBO (Cost Base Optimizer) engine has built in heuristics with hard coded statistics values (table/columns/index statistics…).
That assumptions might be accurate, but in most cases they are completely wrong, as you can see on the following picture.
As you can see, the code is fairly complex, and includes several GTT tables that are most difficult to optimize, as they are ON COMMIT DELETE ROWS.
For that type of GTT tables it is not possible to gather statistics, and CBO (Cost Base Optimizer) engine has built in heuristics with hard coded statistics values (table/columns/index statistics…).
That assumptions might be accurate, but in most cases they are completely wrong, as you can see on the following picture.
Difference between the estimated and the actual number of rows is several orders of magnitude.
For that reason, CBO has chosen wrong execution plan.
As a result, the whole SQL is using more resources than it should, and perform slowly.
To optimize the code, I need to simulate the whole process to fetch some data.
The optimized version of the code is the following:
--optimized code (hint /*+ leading(t, rfr) use_hash(rfr) full(rfr) parallel */ added)
insert into rpm_merch_node_zone_node_gtt
(dept,
class,
subclass,
item_parent,
item,
diff_id,
location,
zone_node_type,
zone_id)
select t.dept,
t.class,
t.subclass,
t.item_parent,
t.item,
t.diff_id,
t.location,
t.zone_node_type,
z.zone_id
from (select im.dept,
im.class,
im.subclass,
inner_il.item,
im.item_parent,
im.diff_1 diff_id,
inner_il.loc location,
L_loc_type zone_node_type,
mrde.regular_zone_group
from item_loc inner_il,
item_master im,
rpm_merch_retail_def_expl mrde
where inner_il.loc = L_location
and inner_il.item = im.item
and im.status = 'A'
and im.sellable_ind = 'Y'
and im.item_level = im.tran_level
and mrde.dept = im.dept
and mrde.class = im.class
and mrde.subclass = im.subclass) t,
(select rz.zone_group_id,
rz.zone_id
from rpm_zone_location rzl,
rpm_zone rz
where rzl.location = L_location
and rz.zone_id = rzl.zone_id
and rownum > 0) z
where t.regular_zone_group = z.zone_group_id (+);
insert into rpm_future_retail_gtt
(price_event_id,
future_retail_id,
dept,
class,
subclass,
item,
diff_id,
item_parent,
zone_node_type,
location,
zone_id,
action_date,
selling_retail,
selling_retail_currency,
selling_uom,
multi_units,
multi_unit_retail,
multi_unit_retail_currency,
multi_selling_uom,
clear_retail,
clear_retail_currency,
clear_uom,
simple_promo_retail,
simple_promo_retail_currency,
simple_promo_uom,
complex_promo_retail,
complex_promo_retail_currency,
complex_promo_uom,
price_change_id,
price_change_display_id,
pc_exception_parent_id,
pc_change_type,
pc_change_amount,
pc_change_currency,
pc_change_percent,
pc_change_selling_uom,
pc_null_multi_ind,
pc_multi_units,
pc_multi_unit_retail,
pc_multi_unit_retail_currency,
pc_multi_selling_uom,
pc_price_guide_id,
clearance_id,
clearance_display_id,
clear_mkdn_index,
clear_start_ind,
clear_change_type,
clear_change_amount,
clear_change_currency,
clear_change_percent,
clear_change_selling_uom,
clear_price_guide_id,
loc_move_from_zone_id,
loc_move_to_zone_id,
location_move_id,
lock_version,
rfr_rowid,
timeline_seq,
on_simple_promo_ind,
on_complex_promo_ind,
max_hier_level,
cur_hier_level)
select I_loc_move_id,
DECODE(fr_rank,
3, fr_il.future_retail_id,
RPM_FUTURE_RETAIL_SEQ.NEXTVAL) future_retail_id,
fr_il.dept,
fr_il.class,
fr_il.subclass,
fr_il.item,
fr_il.diff_id,
fr_il.item_parent,
fr_il.zone_node_type,
fr_il.location,
fr_il.zone_id,
fr_il.action_date,
fr_il.selling_retail,
fr_il.selling_retail_currency,
fr_il.selling_uom,
fr_il.multi_units,
fr_il.multi_unit_retail,
fr_il.multi_unit_retail_currency,
fr_il.multi_selling_uom,
fr_il.clear_retail,
fr_il.clear_retail_currency,
fr_il.clear_uom,
fr_il.simple_promo_retail,
fr_il.simple_promo_retail_currency,
fr_il.simple_promo_uom,
fr_il.complex_promo_retail,
fr_il.complex_promo_retail_currency,
fr_il.complex_promo_uom,
fr_il.price_change_id,
fr_il.price_change_display_id,
fr_il.pc_exception_parent_id,
fr_il.pc_change_type,
fr_il.pc_change_amount,
fr_il.pc_change_currency,
fr_il.pc_change_percent,
fr_il.pc_change_selling_uom,
fr_il.pc_null_multi_ind,
fr_il.pc_multi_units,
fr_il.pc_multi_unit_retail,
fr_il.pc_multi_unit_retail_currency,
fr_il.pc_multi_selling_uom,
fr_il.pc_price_guide_id,
fr_il.clearance_id,
fr_il.clearance_display_id,
fr_il.clear_mkdn_index,
fr_il.clear_start_ind,
fr_il.clear_change_type,
fr_il.clear_change_amount,
fr_il.clear_change_currency,
fr_il.clear_change_percent,
fr_il.clear_change_selling_uom,
fr_il.clear_price_guide_id,
fr_il.loc_move_from_zone_id,
fr_il.loc_move_to_zone_id,
fr_il.location_move_id,
fr_il.lock_version,
DECODE(fr_rank,
3, fr_il.rfr_rowid,
NULL) rfr_rowid,
NULL timeline_seq,
fr_il.on_simple_promo_ind,
fr_il.on_complex_promo_ind,
fr_il.max_hier_level,
RPM_CONSTANTS.FR_HIER_ITEM_LOC
from (select /*+ leading(t, rfr) use_hash(rfr) full(rfr) parallel */
rfr.future_retail_id,
rfr.dept,
rfr.class,
rfr.subclass,
t.to_item item,
t.to_diff_id diff_id,
t.to_item_parent item_parent,
t.to_zone_node_type zone_node_type,
t.to_location location,
t.to_zone_id zone_id,
rfr.action_date,
rfr.selling_retail,
rfr.selling_retail_currency,
rfr.selling_uom,
rfr.multi_units,
rfr.multi_unit_retail,
rfr.multi_unit_retail_currency,
rfr.multi_selling_uom,
rfr.clear_retail,
rfr.clear_retail_currency,
rfr.clear_uom,
rfr.simple_promo_retail,
rfr.simple_promo_retail_currency,
rfr.simple_promo_uom,
rfr.complex_promo_retail,
rfr.complex_promo_retail_currency,
rfr.complex_promo_uom,
rfr.price_change_id,
rfr.price_change_display_id,
rfr.pc_exception_parent_id,
rfr.pc_change_type,
rfr.pc_change_amount,
rfr.pc_change_currency,
rfr.pc_change_percent,
rfr.pc_change_selling_uom,
rfr.pc_null_multi_ind,
rfr.pc_multi_units,
rfr.pc_multi_unit_retail,
rfr.pc_multi_unit_retail_currency,
rfr.pc_multi_selling_uom,
rfr.pc_price_guide_id,
rfr.clearance_id,
rfr.clearance_display_id,
rfr.clear_mkdn_index,
rfr.clear_start_ind,
rfr.clear_change_type,
rfr.clear_change_amount,
rfr.clear_change_currency,
rfr.clear_change_percent,
rfr.clear_change_selling_uom,
rfr.clear_price_guide_id,
rfr.loc_move_from_zone_id,
rfr.loc_move_to_zone_id,
rfr.location_move_id,
rfr.lock_version,
rfr.rowid rfr_rowid,
NULL timeline_seq,
rfr.on_simple_promo_ind,
rfr.on_complex_promo_ind,
rfr.max_hier_level,
t.fr_rank,
MAX(t.fr_rank) OVER (PARTITION BY t.to_item,
t.to_location,
t.to_zone_node_type) max_fr_rank
from (-- get IL data from PZ timelines
select 0 fr_rank,
gtt.dept,
gtt.item_parent from_item,
NULL from_diff_id,
gtt.zone_id from_location,
RPM_CONSTANTS.ZONE_NODE_TYPE_ZONE from_zone_node_type,
gtt.item to_item,
gtt.item_parent to_item_parent,
gtt.diff_id to_diff_id,
gtt.location to_location,
gtt.zone_node_type to_zone_node_type,
gtt.zone_id to_zone_id
from rpm_merch_node_zone_node_gtt gtt
where gtt.item_parent is NOT NULL
and gtt.zone_id is NOT NULL
union all
-- get IL data from PDZ timelines
select distinct
1 fr_rank,
gtt.dept,
gtt.item_parent from_item,
gtt.diff_id from_diff_id,
gtt.zone_id from_location,
RPM_CONSTANTS.ZONE_NODE_TYPE_ZONE from_zone_node_type,
gtt.item to_item,
gtt.item_parent to_item_parent,
gtt.diff_id to_diff_id,
gtt.location to_location,
gtt.zone_node_type to_zone_node_type,
gtt.zone_id to_zone_id
from rpm_merch_node_zone_node_gtt gtt
where gtt.item_parent is NOT NULL
and gtt.diff_id is NOT NULL
and gtt.zone_id is NOT NULL
union all
-- get IL data from PL timelines
select distinct
1 fr_rank,
gtt.dept,
gtt.item_parent from_item,
NULL from_diff_id,
gtt.location from_location,
gtt.zone_node_type from_zone_node_type,
gtt.item to_item,
gtt.item_parent to_item_parent,
gtt.diff_id to_diff_id,
gtt.location to_location,
gtt.zone_node_type to_zone_node_type,
gtt.zone_id to_zone_id
from rpm_merch_node_zone_node_gtt gtt
where gtt.item_parent is NOT NULL
union all
-- get IL data from IZ timelines
select distinct
2 fr_rank,
gtt.dept,
gtt.item from_item,
NULL from_diff_id,
gtt.zone_id from_location,
RPM_CONSTANTS.ZONE_NODE_TYPE_ZONE from_zone_node_type,
gtt.item to_item,
gtt.item_parent to_item_parent,
gtt.diff_id to_diff_id,
gtt.location to_location,
gtt.zone_node_type to_zone_node_type,
gtt.zone_id to_zone_id
from rpm_merch_node_zone_node_gtt gtt
where gtt.zone_id is NOT NULL
union all
-- get IL data from PDL timelines
select distinct
2 fr_rank,
gtt.dept,
gtt.item_parent from_item,
gtt.diff_id from_diff_id,
gtt.location from_location,
gtt.zone_node_type from_zone_node_type,
gtt.item to_item,
gtt.item_parent to_item_parent,
gtt.diff_id to_diff_id,
gtt.location to_location,
gtt.zone_node_type to_zone_node_type,
gtt.zone_id to_zone_id
from rpm_merch_node_zone_node_gtt gtt
where gtt.item_parent is NOT NULL
and gtt.diff_id is NOT NULL
union all
-- get IL data from IL timelines
select distinct
3 fr_rank,
gtt.dept,
gtt.item from_item,
NULL from_diff_id,
gtt.location from_location,
gtt.zone_node_type from_zone_node_type,
gtt.item to_item,
gtt.item_parent to_item_parent,
gtt.diff_id to_diff_id,
gtt.location to_location,
gtt.zone_node_type to_zone_node_type,
gtt.zone_id to_zone_id
from rpm_merch_node_zone_node_gtt gtt) t,
rpm_future_retail rfr
where rfr.dept = t.dept
and rfr.item = t.from_item
and NVL(rfr.diff_id, -999) = NVL(t.from_diff_id, -999)
and rfr.location = t.from_location
and rfr.zone_node_type = t.from_zone_node_type) fr_il
where fr_rank = max_fr_rank;
In this case I need to hint the code to get the execution plan I want.
/*+ leading(t, rfr) use_hash(rfr) full(rfr) parallel */
This is the new execution plan:
Comparing with vanilla code, the new code is running about 3.47 times faster.
Disclaimer:
As with every optimization issue, this one is related to one particular customer only, after the monitoring and detailed analysis have been performed.
For another customer, performance boost could be much lower or the code will run even slower than before.
Thus I’m strongly discouraging you to blindly paste the code provided here, before acquiring all information.
The only purpose of this article is to show what can be done to speed up performance.
Comments