I’ve been asked to help resolving performance issue with reclassification batch, as important business process was unable to finish within reasonable time frame.

A few facts about reclassification batch (RECLSDLY):

As described in Operations Guide (13.2.x), item reclassification is the process through which an item or item list is moved from one department/class/subclass to another.

Reclassification batch (RECLSDLY) is standard RMS batch, which running as part of Oracle Retail batch chain.

Batch has been written in Pro*C.

Only POST RECLSDLY part was working slowly.

I’ve traced batch execution (Event 10046), and got the following result (only the most important part):


SQL ID: f9mmcbv0kx07c Plan Hash: 2080240954

SELECT DILE.ROWID 
FROM
 DEAL_HEAD DH, DEAL_ITEM_LOC_EXPLODE DILE WHERE DH.DEAL_ID = DILE.DEAL_ID AND 
  DH.STATUS = 'A' AND DILE.CLOSE_DATE < DH.CLOSE_DATE AND (:B1 > 
  (DILE.CLOSE_DATE + NVL(DH.BBD_ADD_REP_DAYS,0))) FOR UPDATE OF DILE.DEAL_ID 
  NOWAIT


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1     32.35    1124.50     326983     774002         97           0
Fetch        1      0.00       0.00          0          0          0          94
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     32.35    1124.50     326983     774002         97          94


SELECT * FROM table(dbms_xplan.display_awr('f9mmcbv0kx07c', 2080240954, null, 'all'));

Plan hash value: 2080240954
 
----------------------------------------------------------------------------------------
|Id |Operation                            |Name                     |Rows  |Cost (%CPU)|
----------------------------------------------------------------------------------------
|0  |SELECT STATEMENT                     |                         |      |73980 (100)|
|1  | FOR UPDATE                          |                         |      |           |
|2  |  BUFFER SORT                        |                         |      |           |
|3  |   NESTED LOOPS                      |                         |      |           |
|4  |    NESTED LOOPS                     |                         |20946 |73980   (1)|
|5  |     TABLE ACCESS FULL               |DEAL_HEAD                | 1545 |  173   (1)|
|6  |     PARTITION HASH ITERATOR         |                         | 7140 |   28   (0)|
|7  |      INDEX RANGE SCAN               |DEAL_ITEM_LOC_EXPLODE_I1 | 7140 |   28   (0)|
|8  |    TABLE ACCESS BY LOCAL INDEX ROWID|DEAL_ITEM_LOC_EXPLODE    |   14 |  368   (0)|
----------------------------------------------------------------------------------------

The most resource intensive code from the trace file is part of the RECLASS_SQL package, function POST_PROCESS.

This is the original, vanilla Oracle code from the package:


   cursor C_CLOSED_DEAL is
      select dile.rowid
        from deal_head dh,
             deal_item_loc_explode dile
       where dh.deal_id = dile.deal_id
         and dh.status = 'A'
         and dile.close_date < dh.close_date 
         and (L_vdate > (dile.close_date + NVL(dh.bbd_add_rep_days,0)))
         for update of dile.deal_id nowait;

 

I’ve tested optimized code without FOR UPDATE part:


with 
dh as
(select /*+ materialize */ * from deal_head where status = 'A')
select /*+ test2 */ dile.rowid
from 
dh, 
deal_item_loc_explode dile
where 
dh.deal_id = dile.deal_id
and dile.close_date < dh.close_date 
and (to_date('02.07.2015', 'dd.mm.rrrr') > (dile.close_date + nvl(dh.bbd_add_rep_days,0))); 



Plan hash value: 1967604062
 
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name                        | Rows  | Cost (%CPU)|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                             |       |   162K(100)|
|   1 |  TEMP TABLE TRANSFORMATION   |                             |       |            |
|   2 |   LOAD AS SELECT             |                             |       |            |
|*  3 |    TABLE ACCESS FULL         | DEAL_HEAD                   |  1730 |   207   (1)|
|*  4 |   HASH JOIN                  |                             | 31702 |   162K  (1)|
|   5 |    PART JOIN FILTER CREATE   | :BF0000                     |  1730 |    12   (0)|
|   6 |     VIEW                     |                             |  1730 |    12   (0)|
|   7 |      TABLE ACCESS FULL       | SYS_TEMP_0FD9D7CD8_3873580B |  1730 |    12   (0)|
|   8 |    PARTITION HASH JOIN-FILTER|                             |    12M|   162K  (1)|
|   9 |     TABLE ACCESS FULL        | DEAL_ITEM_LOC_EXPLODE       |    12M|   162K  (1)|
-----------------------------------------------------------------------------------------

 

The only thing I have to do is to replace vanilla code with optimized version.


--optimized code is the following 
    cursor C_CLOSED_DEAL is
    with 
    dh as
    (select /*+ materialize */ * from deal_head where status = 'A')
    select /*+ leading(dh) use_hash(dile) */ dile.rowid
    from 
    dh, 
    deal_item_loc_explode dile
    where 
    dh.deal_id = dile.deal_id
    and dile.close_date < dh.close_date     and (l_vdate > (dile.close_date + nvl(dh.bbd_add_rep_days,0)))
    for update of dile.deal_id nowait;

 

After optimization, reclassification batch is working about 60 times faster then before.

 

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.