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