Home » RDBMS Server » Performance Tuning » slow perf with Bulk collect and FORALL
slow perf with Bulk collect and FORALL [message #250905] |
Wed, 11 July 2007 14:34 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
aliceg
Messages: 29 Registered: July 2007 Location: Los Angeles
|
Junior Member |
![aliceghe](/forum/theme/orafaq/images/yahoo.png)
|
|
I read about supercharge your DML with bulk collect and FORALL and implemented on one of the slow performing procedures, but the code seem to be performing even slower.
Any suggestions, please help, have deadline.
Below is the code:
CREATE OR REPLACE PROCEDURE RESET_CHANGE_PARAM(
process_date IN DATE DEFAULT TRUNC (SYSDATE)
)
IS
CURSOR products_data is
SELECT vi.item_id, vl.location_id, mt.store_min, mt.store_max, mt.facings, mt.fixture_fill, mt.action_code,
mt.transit_time, mt.effective_date
FROM dp.t_src_matrix mt,
items vi,
t_ep_Catal tc,
location vl,
t_ep_shinr ts
WHERE (mt.action_code = 'CHG-LIVE' OR
mt.action_code = 'CHANGEPLUS' OR
mt.action_code = 'CHANGE_MINUS' OR
mt.action_code = 'CHG-RST' OR
mt.action_code = 'CHANGE')
AND mt.catalog = tc.catal
AND tc.t_ep_catal_ep_id = vi.t_ep_catal_ep_id
AND mt.ship_to_number = ts.shinr
AND vl.t_ep_shinr_ep_id = ts.t_ep_shinr_ep_id;
TYPE prod_tab IS TABLE OF products_data%ROWTYPE;
products_tab prod_tab := prod_tab();
TYPE itemloc_typ IS TABLE OF sales_data.item_id%TYPE INDEX BY PLS_INTEGER;
TYPE stf_typ IS TABLE OF sales_data.store_max%TYPE INDEX BY PLS_INTEGER;
TYPE fixfill_typ IS TABLE OF sales_data.fixture_fill%TYPE INDEX BY PLS_INTEGER;
TYPE action_typ IS TABLE OF dp.t_src_matrix.action_code%TYPE INDEX BY PLS_INTEGER;
TYPE date_typ IS TABLE OF dp.t_src_matrix.effective_date%TYPE INDEX BY PLS_INTEGER;
action action_typ;
item itemloc_typ;
loc itemloc_typ;
stmin stf_typ;
stmax stf_typ;
facing stf_typ;
fixfill fixfill_typ;
itemN itemloc_typ;
locN itemloc_typ;
stminN stf_typ;
stmaxN stf_typ;
facingN stf_typ;
fixfillN fixfill_typ;
--Date and time variables
trTime stf_typ;
efDate date_typ;
nb_err Pls_integer ;
total Pls_integer := 0 ;
v_start_date DATE := SYSDATE;
v_end_date DATE ;
cnt_rec NUMBER := 0;
v_time NUMBER := 0;
v_seq NUMBER := 0;
BEGIN
--Check the content
BEGIN
SELECT COUNT(1) INTO cnt_rec FROM dp.t_src_matrix
WHERE ROWNUM < 2;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
BEGIN -- create table that will collect the statistics
check_and_drop('alice_reset_param');
dynamic_ddl('CREATE TABLE alice_reset_param
(run_id number null,
categ_desc varchar2(100) NULL,
no_rec number null,
start_date date NULL,
end_date date NULL,
time_elapsed number)');
END;
IF cnt_rec > 0 THEN
OPEN products_data;
LOOP
FETCH products_data BULK COLLECT INTO products_tab LIMIT 999;
FOR j IN products_tab.FIRST .. products_tab.LAST
LOOP
item (j) := products_tab (j).item_id;
loc (j) := products_tab (j).location_id;
action (j) := products_tab (j).action_code;
stmin (j) := products_tab (j).store_min;
stmax (j) := products_tab (j).store_max;
fixfill (j) := products_tab (j).fixture_fill;
facing (j) := products_tab (j).facings;
trTime (j) := products_tab (j).transit_time;
efDate (j) := products_tab (j).effective_date;
IF action (j) = 'CHG-LIVE' THEN
FORALL i IN item.FIRST .. item.LAST
UPDATE sales_data
SET store_max = stmax(i),
store_min = stmin(i),
facings = facing(i),
fixture_fill = fixfill(i)
WHERE item_id = item(i)
AND location_id = loc(i)
AND store_max IS NOT NULL
AND store_min IS NOT NULL
AND facings IS NOT NULL;
ELSE
--This part will be for the CHG-RST
--------------------------------------------------
SELECT m.item_id,
m.location_id,
NVL (s.store_max, m.store_max),
NVL (s.store_min, m.store_min),
NVL (s.facings, m.facings),
NVL (s.fixture_fill, m.fixture_fill)
BULK COLLECT INTO itemN,
locN,
stmaxN,
stminN,
facingN,
fixfillN
FROM mdp_matrix m, sales_data s
WHERE s.sales_date = trunc(process_date)
AND m.item_id = products_tab (j).item_id
AND m.location_id = products_tab (j).location_id
AND m.item_id = s.item_id
AND m.location_id = s.location_id;
FOR x IN itemN.FIRST .. itemN.LAST
LOOP
IF products_tab (j).store_max <> stmaxN (x)
OR products_tab (j).store_Min <> stminN (x)
OR products_tab (j).facings <> facingN (x)
OR products_tab (j).fixture_fill <> fixfillN (x)
THEN
FORALL x IN itemN.FIRST .. itemN.LAST
UPDATE dr.sales_data
SET store_max = NVL (store_max, stmaxN (x)),
store_min = NVL (store_min, stminN (x)),
facings = NVL (facings, facingN (x)),
fixture_fill = NVL (fixture_fill, fixfillN (x))
WHERE item_id = itemN (x)
AND location_id = locN (x)
AND sales_date <
TO_DATE (efDate (j), 'DD-MON-YYYY')
- (trTime (j));
UPDATE dr.sales_data
SET store_max = NULL,
store_min = NULL,
facings = NULL,
fixture_fill = NULL
WHERE item_id = itemN (x)
AND location_id = locN (x)
AND sales_date >=
TO_DATE (efDate (j), 'DD-MON-YYYY')
- (trTime (j));
END IF;
END LOOP;
END IF;
END LOOP;
v_seq := v_seq + 1;
cnt_rec := products_tab.COUNT;
v_end_date := SYSDATE;
v_time := ROUND((v_end_date-v_start_date)*1440,5);
INSERT INTO alice_reset_param
VALUES(v_seq,'Bulk partial',cnt_rec, v_START_DATE,
v_end_date,
v_time);
v_start_date := v_end_date;
v_end_date := SYSDATE;
COMMIT;
EXIT WHEN products_data%NOTFOUND;
END LOOP;
CLOSE products_data;
INSERT INTO alice_reset_param
SELECT max(run_id)+1, 'Bulk Total', SUM(no_rec), MIN(START_DATE), MAX(end_date),
ROUND((MAX(end_date) - MIN(START_DATE))* 1440,5) FROM alice_reset_param;
COMMIT;
END IF;
END;
[mod-edit] illiterate IM speak words removed.
[Updated on: Mon, 24 September 2007 15:56] by Moderator Report message to a moderator
|
|
|
|
|
|
|
Re: PLZ HELP - slow perf with Bulk collect and FORALL [message #250939 is a reply to message #250915] |
Wed, 11 July 2007 22:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
You select rows from product_data in arrays of 999.
Then you loop through the 999 rows, and for each row (j), you update SALES_DATA for products 1..j.
So on the fist product, you perform 1 update.
On the second product, you perform 2 updates.
On the third product, you perform 3 updates.
You need to take the FORALL UPDATE statement out of the FOR loop.
Take a look at the example in this article
Ross Leishman
|
|
|
|
|
Re: PLZ HELP - slow perf with Bulk collect and FORALL [message #251326 is a reply to message #250989] |
Thu, 12 July 2007 23:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
aliceg
Messages: 29 Registered: July 2007 Location: Los Angeles
|
Junior Member |
![aliceghe](/forum/theme/orafaq/images/yahoo.png)
|
|
Just wanted to say Thanks again to rleishman for the valuables tips and precious article.
I learned from this a few things:
1. It's best to make scalar collections (of table_name.col_Name type and bulk collect the needed cursor in the variables of the above types, which works best when a multiple column table update is needed
2. FORALL functions like a simple DML statement. If multiple updates are needed, then for each one we put a FORALL statement
Below is the final version of the code in a anonymous block. It's tested and it works. (The only thing I still need to check is the speed of execution) -- Will come back with more details
[ALIGN=left]
DECLARE
CURSOR chg_live_cur is
SELECT vi.item_id, vl.location_id, mt.store_min, mt.store_max, mt.facings, mt.fixture_fill,
mt.transit_time, mt.effective_date
FROM dp.t_src_matrix mt,
items vi,
t_ep_Catal tc,
location vl,
t_ep_shinr ts
WHERE mt.action_code = 'CHG-LIVE'
AND mt.catalog = tc.catal
AND tc.t_ep_catal_ep_id = vi.t_ep_catal_ep_id
AND mt.ship_to_number = ts.shinr
AND vl.t_ep_shinr_ep_id = ts.t_ep_shinr_ep_id
AND ROWNUM < 50;
CURSOR chg_rst_cur IS
SELECT vi.item_id, vl.location_id, mt.store_min, mt.store_max, mt.facings, mt.fixture_fill,
mt.transit_time, mt.effective_date
FROM dp.t_src_matrix mt,
items vi,
t_ep_Catal tc,
location vl,
t_ep_shinr ts
WHERE (mt.action_code = 'CHANGEPLUS' OR
mt.action_code = 'CHANGE_MINUS' OR
mt.action_code = 'CHG-RST' OR
mt.action_code = 'CHANGE')
AND mt.catalog = tc.catal
AND tc.t_ep_catal_ep_id = vi.t_ep_catal_ep_id
AND mt.ship_to_number = ts.shinr
AND vl.t_ep_shinr_ep_id = ts.t_ep_shinr_ep_id
AND ROWNUM < 2;
-- collection of scalars
TYPE itemloc_typ IS TABLE OF sales_data.item_id%TYPE INDEX BY PLS_INTEGER;
TYPE stf_typ IS TABLE OF sales_data.store_max%TYPE INDEX BY PLS_INTEGER;
TYPE fixfill_typ IS TABLE OF sales_data.fixture_fill%TYPE INDEX BY PLS_INTEGER;
TYPE action_typ IS TABLE OF dp.t_src_matrix.action_code%TYPE INDEX BY PLS_INTEGER;
TYPE date_typ IS TABLE OF dp.t_src_matrix.effective_date%TYPE INDEX BY PLS_INTEGER;
action action_typ;
item itemloc_typ;
loc itemloc_typ;
stmin stf_typ;
stmax stf_typ;
facing stf_typ;
fixfill fixfill_typ;
itemN itemloc_typ;
locN itemloc_typ;
stminN stf_typ;
stmaxN stf_typ;
facingN stf_typ;
fixfillN fixfill_typ;
--Date and time variables
trTime stf_typ;
efDate date_typ;
nb_err Pls_integer ;
total Pls_integer := 0 ;
v_start_date DATE := SYSDATE;
v_end_date DATE ;
cnt_rec NUMBER := 0;
cnt_rec_ch NUMBER := 0;
v_time NUMBER := 0;
v_seq NUMBER := 0;
-- create an exception handler for ORA-24381
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
--Check the content of products_data
BEGIN
SELECT COUNT(1) INTO cnt_rec FROM dp.t_src_matrix
WHERE action_code = 'CHG-LIVE';
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
--Check the content of sales_cur
BEGIN
SELECT COUNT(1) INTO cnt_rec_ch FROM dp.t_src_matrix
WHERE action_code = 'CHANGEPLUS' OR
action_code = 'CHANGE_MINUS' OR
action_code = 'CHG-RST' OR
action_code = 'CHANGE';
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
IF cnt_rec > 0 THEN
OPEN chg_live_cur;
LOOP
FETCH chg_live_cur BULK COLLECT INTO
item, loc, stmin, stmax, facing, fixfill, trTime,
efDate LIMIT 20;
BEGIN
FORALL i IN item.FIRST .. item.LAST SAVE EXCEPTIONS
UPDATE sales_data
SET store_max = stmax(i),
store_min = stmin(i),
facings = facing(i),
fixture_fill = fixfill(i)
WHERE item_id = item(i)
AND location_id = loc(i)
AND store_max IS NOT NULL
AND store_min IS NOT NULL
AND facings IS NOT NULL;
EXCEPTION
WHEN dml_errors THEN NULL;
--FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
-- DBMS_OUTPUT.put_line('Error in CHG-LIVE');
--END LOOP;
END;
EXIT WHEN chg_live_cur%NOTFOUND;
END LOOP;
CLOSE chg_live_cur;
ELSE NULL;
END IF;
--For Chg-Rst
IF cnt_rec_ch > 0 THEN
OPEN chg_rst_cur;
LOOP
FETCH chg_rst_cur BULK COLLECT INTO
item, loc, stmin, stmax, facing, fixfill, trTime, efDate LIMIT 20;
--Update with values from the cursor if sales_date < ef_date - trtime
BEGIN
FORALL i IN item.FIRST .. item.LAST save EXCEPTIONS
UPDATE dr.sales_data
SET store_max = NVL(store_max, stmax(i)),
store_min = NVL(store_min, stmin(i)),
facings = NVL (facings, facing(i)),
fixture_fill = NVL (fixture_fill, fixfill(i))
WHERE sales_date < TO_DATE (efDate(i), 'DD-MON-YYYY') - trTime(i) AND
(item_id, location_id) IN
(SELECT s.item_id, s.location_id
FROM mdp_matrix m, sales_data s
WHERE s.item_id = item(i)
AND s.location_id = loc(i)
AND s.sales_date = TRUNC(sysdate)
AND s.item_id = m.item_id
AND s.location_id = m.location_id
AND (NVL (s.store_max, m.store_max) <> stmax(i)
OR NVL (s.store_min, m.store_min) <> stmin(i)
OR NVL (s.facings, m.facings) <> facing(i)
OR NVL (s.fixture_fill, m.fixture_fill) <> fixfill(i))
);
EXCEPTION
WHEN dml_errors THEN NULL;
--FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
-- DBMS_OUTPUT.put_line('Error in CHG-LIVE');
--END LOOP;
END;
BEGIN
FORALL i IN item.FIRST .. item.LAST save EXCEPTIONS
--Set to NULL if sales_date > ef_date - transit time
UPDATE dr.sales_data
SET store_max = NULL,
store_min = NULL,
facings = NULL,
fixture_fill = NULL
WHERE item_id = item(i)
AND location_id = loc(i)
AND sales_date >=
TO_DATE (efDate(i), 'DD-MON-YYYY')
- (trTime(i));
EXCEPTION
WHEN dml_errors THEN NULL;
--FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
-- DBMS_OUTPUT.put_line('Error in CHG-LIVE');
--END LOOP;
END;
EXIT WHEN chg_rst_cur%NOTFOUND;
END LOOP;
CLOSE chg_rst_cur;
ELSE NULL;
END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;[/ALIGN]
|
|
|
|
|
|
|
|
|
|
|
|
Re: slow perf with Bulk collect and FORALL [message #270151 is a reply to message #270128] |
Tue, 25 September 2007 23:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
This is very odd.
Even if you were to say that QUERY buffer reads, were instantaneous, your DISK reads are somewhere between 100 and 200 per second. Either your disk is implemented as grumpy little elfs stamping bits in plasticine, or there is some recursive SQL happening.
To you have any:
- triggers
- foreign keys amongst the updated columns
Post the FULL tkprof output - start to finish with plans explained as well.
Are the updates using an index?
Ross Leishman
|
|
|
|
|
Re: slow perf with Bulk collect and FORALL [message #270432 is a reply to message #270344] |
Wed, 26 September 2007 21:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Bulk Collect is no longer an issue here, as you are not executing that many statements. I believe the problem may be due to long range scans - I will follow up in your other thread instead.
Ross Leishman
|
|
|
Goto Forum:
Current Time: Mon Sep 09 08:30:20 CDT 2024
|