Recently I’ve solved this interesting issue on Oracle Retail production (code is from RPM – Retail Price Management).
After upgrading Oracle Retail I found problematic part of code that is running fine in the old version of the RPM.
In new version, developer has correctly changed the following part of code with much faster version by introducing the FORALL statement.
Old version (just important fragment of the code) before the upgrade – with classical (slower) FOR loop and IF condition checking at the beginning:
/*for emergency price changes insert into the temp table which is used by ordupd.pc to update orders*/ FOR i IN 1 .. LP_cr_count LOOP IF LP_VDATE = LP_action_date (i) THEN MERGE INTO emer_price_hist eph USING DUAL ON ( LP_tran_type (i) = eph.tran_type AND LP_action_date (i) = eph.action_date AND LP_loc (i) = eph.loc AND LP_item (i) = eph.item) WHEN MATCHED ... END LOOP;
New, technically much better version – with FORALL, which is infact 1000 times slower because of lack of business knowledge:
/*for emergency price changes insert into the temp table which is used by ordupd.pc to update orders*/ forall i in 1 .. LP_cr_count MERGE INTO emer_price_hist eph USING dual ON ( LP_tran_type(i) = eph.tran_type and LP_action_date(i) = eph.action_date and LP_loc(i) = eph.loc and LP_item(i) = eph.item ) WHEN MATCHED THEN ... where LP_vdate = LP_action_date(i);
In normal circumstances, the new version should run (depending of the number of elements in the loop) maybe 10 times or even more faster than the regular FOR loop.
The magic behind FORALL statement is because it send all statements (MERGE in this case) in one shot, meaning it will be only one context switch between the two engines: SQL and PL/SQL
It sounds developer did a great job by following new trends in development and continuously improving he’s/she’s knowledge with new features that Oracle has to offer (although FORALL is not brand new feature – it’s introduced in 9i version I believe which is from 2003).
But what is the problem then?
To answer that question you need to know how FORALL statement is working behind the scene.
When FORALL sends all MERGE statements (or INSERT/DELETE/UPDATE) to SQL engine, as I said it do that in one shot.
When SQL engine receives all MERGE statement, it will execute them.
It means that the condition
where LP_vdate = LP_action_date(i)
will be checked in SQL engine during execution.
But what if 99% of those MERGE statements return no rows, meaning the condition is False?
That would be very inefficient as SQL engine need to parse the statement, evaluate execution plan etc. and finally execute the whole SQL statement to find out no rows fulfill the WHERE condition.
In this case thousand times better approach will be to send only MERGE statements that will return some rows (WHERE condition is True), meaning 99% of MERGE statements won’t be send to SQL engine at all.
This will for sure improve performances, as in this, by reducing time needed by 1000x.
But how developer could possibly knows that fact?
He/she won’t and this is where the person with business knowledge jump in.
To summarize, this is classical example where technical know is not enough, and without business knowledge you won’t resolve such problem.
This case, where technically improved code in the new version of RPM is producing 1000 times slower functionality stress exactly that.
Perfect solution would be to find the person who has both: technical expertize and business knowledge, as only by combining them you can really pull out the maximum from your system.