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.

loc_move_schedule_batch_problematic_sql

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:

loc_move_schedule_batch_problematic_sql2

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

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.