Home » RDBMS Server » Performance Tuning » Bulk update performance issue (Oracle 11g, Windows XP)
| Bulk update performance issue [message #525335] |
Sat, 01 October 2011 03:03  |
 |
ninan
Messages: 163
Registered: June 2011
Location: Noida
|
Senior Member |
|
|
Hi,
I have a table with 8726387 records.
There is this procedure to update a column of the same table, from derived values of another column of the same table.
I have index on columns used in WHERE clause except the on which update is happening. I am checking for any duplicate values in the column which is getting updated, since the requirement is to rollup column values in the child hierarchy and update the column against the parent , with comma separated row values of child records.
I am using a LIMIT of 100000 to BULK FETCH in each iteration.
I am using PLSQL records, and bulk fetching it into a ref cursor and updating using FOR ALL statment.
This is taking 5 hours 40 minutes to update 8726387 records.
Attached is the Update statement, script for table creation and insertion, explain plan etc.,
Kindly suggest how can we improve this, since I think 8726387 is not too high for Oracle. I am doubting it could be a DB issue of Buffer of the REF CURSOR being full and not able to complete.
/* Formatted on 10/1/2011 12:23:31 PM (QP5 v5.139.911.3011) */
CREATE TABLE itemexcption
(
item_id VARCHAR2 (40),
part VARCHAR2 (50),
sub_part VARCHAR2 (40),
reason VARCHAR2 (4000),
rolled_reason VARCHAR2 (4000)
)
CREATE index itemidx on itemexcption (item_id);
CREATE INDEX partidx
ON itemexcption (part);
CREATE INDEX subpartidx
ON itemexcption (sub_part);
INSERT INTO itemexcption (item_id,
part,
sub_part,
reason)
VALUES ('ITEM1',
NULL,
NULL,
4);
INSERT INTO itemexcption (item_id,
part,
sub_part,
reason)
VALUES ('ITEM1',
'PART1',
NULL,
2);
INSERT INTO itemexcption (item_id,
part,
sub_part,
reason)
VALUES ('ITEM1',
'PART2',
NULL,
3);
INSERT INTO itemexcption (item_id,
part,
sub_part,
reason)
VALUES ('ITEM1',
'PART1',
'SUBPART1',
1);
INSERT INTO itemexcption (item_id,
part,
sub_part,
reason)
VALUES ('ITEM1',
'PART1',
'SUBPART2',
2);
INSERT INTO itemexcption (item_id,
part,
sub_part,
reason)
VALUES ('ITEM1',
'PART2',
NULL,
1);
INSERT INTO itemexcption (item_id,
part,
sub_part,
reason)
VALUES ('ITEM1',
'PART2',
'SUBPART1',
1);
INSERT INTO itemexcption (item_id,
part,
sub_part,
reason)
VALUES ('ITEM1',
'PART2',
'SUBPART2',
3);
INSERT INTO itemexcption (item_id,
part,
sub_part,
reason)
VALUES ('ITEM2',
NULL,
NULL,
2);
INSERT INTO itemexcption (item_id,
part,
sub_part,
reason)
VALUES ('ITEM2',
NULL,
NULL,
2);
INSERT INTO itemexcption (item_id,
part,
sub_part,
reason)
VALUES ('ITEM2',
'PART1',
NULL,
1);
INSERT INTO itemexcption (item_id,
part,
sub_part,
reason)
VALUES ('ITEM2',
'PART2',
NULL,
3);
INSERT INTO itemexcption (item_id,
part,
sub_part,
reason)
VALUES ('ITEM2',
'PART1',
'SUBPART1',
4);
INSERT INTO itemexcption (item_id,
part,
sub_part,
reason)
VALUES ('ITEM2',
'PART1',
'SUBPART2',
1);
INSERT INTO itemexcption (item_id,
part,
sub_part,
reason)
VALUES ('ITEM2',
'PART2',
NULL,
3);
INSERT INTO itemexcption (item_id,
part,
sub_part,
reason)
VALUES ('ITEM2',
'PART2',
'SUBPART1',
2);
INSERT INTO itemexcption (item_id,
part,
sub_part,
reason)
VALUES ('ITEM2',
'PART2',
'SUBPART2',
4);
COMMIT;
SELECT * FROM ITMEXCPTION
----------------------------------------------------------
ITEM PART SUBPART REASON ROLLED_REASON
ITEM1 PART1 2
ITEM1 PART2 3
ITEM1 PART1 SUBPART1 1
ITEM1 PART1 SUBPART2 2
ITEM1 PART2 1
ITEM1 PART2 SUBPART1 1
ITEM1 PART2 SUBPART2 3
ITEM2 2
ITEM2 PART1 1
ITEM2 PART2 3
ITEM2 2
ITEM2 PART1 SUBPART1 4
ITEM2 PART1 SUBPART2 1
ITEM2 PART2 3
ITEM2 PART2 SUBPART1 2
ITEM2 PART2 SUBPART2 4
---------------------------------------------------------
Procedure for updating is working fine as I have tested with a lesser data set. Problem is with performance of this UPDATE and the time it takes to update the entire table.
The output for above is given below. (i.e, ROLLED_REASON column after updation )
ROLLED_REASON is rolled up and updated only for PARENT i.e., ITEM and PART ie., here ITEM1 will have 4,2,3,1 since PARTs and SUBPARTS under ITEM1 have these distinct values. There is no order for this updation. It can be 1,2,3,4 or any order as this is from a BULK update.
Similiarly PART1 under ITEM1 will have 2,1 since SUBPARTs under PART1 have these distinct values in Column REASON.
After updation the result will look like below.
select * from itemexcption order by item_id, part nulls first, sub_part nulls first;
----------------------------------------------------------
ITEM PART SUBPART REASON ROLLED_REASON
-----------------------------------------------------------
ITEM1 4 1,2,3,4,
ITEM1 PART1 2 1,2,4,
ITEM1 PART1 SUBPART1 1
ITEM1 PART1 SUBPART2 2
ITEM1 PART2 3 1,2,3,4,
ITEM1 PART2 1 1,2,3,4,
ITEM1 PART2 SUBPART1 1
ITEM1 PART2 SUBPART2 3
ITEM2 2 1,2,3,4,
ITEM2 2 1,2,3,4,
ITEM2 PART1 1 1,2,4,
ITEM2 PART1 SUBPAR1 4
ITEM2 PART1 SUBPART2 1
ITEM2 PART2 3 1,2,3,4,
ITEM2 PART2 3 1,2,3,4,
ITEM2 PART2 SUBPART1 2
ITEM2 PART2 SUBPART2 4
---------------------------------------------------------
Script for this updation is given below.
DECLARE
TYPE item_rec IS RECORD (
item itemexcption.item_id%TYPE,
rsncodes itemexcption.reason%TYPE
);
TYPE item_tab IS TABLE OF item_rec;
v_limit NUMBER := 500;
l_start NUMBER;
mod_rec item_tab;
itm_rec item_tab;
TYPE itemrec IS REF CURSOR;
rec itemrec;
rec1 itemrec;
BEGIN
l_start := DBMS_UTILITY.get_time;
UPDATE itemexcption
SET rolled_reason = NULL
WHERE rolled_reason IS NOT NULL;
COMMIT;
/***** Reason Code updation Against the PARTs******************************************************/
OPEN rec FOR
SELECT part, reason
FROM (SELECT b.part part,
b.reason reason, ROW_NUMBER ()
OVER (PARTITION BY b.part, b.reason ORDER BY b.part)
rn
FROM itemexcption b
WHERE b.reason IS NOT NULL)
WHERE rn = 1; /** For getting distinct values against PART****/
LOOP
FETCH rec
BULK COLLECT INTO mod_rec
LIMIT 100000;
FORALL i IN mod_rec.FIRST .. mod_rec.LAST
SAVE EXCEPTIONS
UPDATE /* bulk bind */
itemexcption a
SET a.rolled_reason=
a.rolled_reason || mod_rec (i).rsncodes || ','
WHERE mod_rec (i).item = a.PART AND a.SUB_PART IS NULL
AND (a.rolled_reason IS NULL OR a.rolled_reason not like '%'||mod_rec(i).rsncodes||'%' );
COMMIT;
EXIT WHEN rec%NOTFOUND;
END LOOP;
CLOSE rec;
COMMIT;
/***** Reason Code updation Against the ITEMs******************************************************/
OPEN rec FOR
SELECT item_id, reason
FROM (SELECT b.item_id item_id,
b.reason reason, ROW_NUMBER ()
OVER (PARTITION BY b.item_id, b.reason ORDER BY b.item_id)
rn
FROM itemexcption b
WHERE b.reason IS NOT NULL)
WHERE rn = 1; /** For getting distinct values against PART****/
LOOP
FETCH rec
BULK COLLECT INTO itm_rec
LIMIT 100000;
FORALL i IN itm_rec.FIRST .. itm_rec.LAST
SAVE EXCEPTIONS
UPDATE /* bulk bind */
itemexcption a
SET a.rolled_reason=
a.rolled_reason || itm_rec (i).rsncodes || ','
WHERE itm_rec (i).item = a.ITEM_ID AND a.PART IS NULL
AND ( a.rolled_reason IS NULL OR a.rolled_reason not like '%'||itm_rec(i).rsncodes||'%' );
COMMIT;
EXIT WHEN rec%NOTFOUND;
END LOOP;
CLOSE rec;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Exception' || SQLERRM);
END;
-----------
This took 2 hours to update 21,48,725 records.
Any suggestions, what can be wrong in the proc.
[Updated on: Sat, 01 October 2011 03:33] Report message to a moderator |
|
|
|
| Re: Bulk update performance issue [message #525344 is a reply to message #525335] |
Sat, 01 October 2011 05:04  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Well, the main problem is that ROLLED_REASON shouldn't be stored in the first place.
You can use STRAGG or other methods to calculate it in on the fly the select.
If you absolutely insist on storing it, you can also use that select to do a straight SQL update of the entire table without using a procedure.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Bulk update performance issue [message #525416 is a reply to message #525394] |
Sun, 02 October 2011 14:21 |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I believe it would be more efficient to do your aggregation in the select, so that you would only have to do one update per row, instead of one update for each reason per row. Please see the demonstration below in which I changed your select and update statements.
SCOTT@orcl_11gR2> column item_id format a8
SCOTT@orcl_11gR2> column part format a8
SCOTT@orcl_11gR2> column sub_part format a8
SCOTT@orcl_11gR2> column reason format a8
SCOTT@orcl_11gR2> column rolled_reason format a8
SCOTT@orcl_11gR2> select *
2 from itemexcption
3 order by item_id, part nulls first, sub_part nulls first
4 /
ITEM_ID PART SUB_PART REASON ROLLED_R
-------- -------- -------- -------- --------
ITEM1 4
ITEM1 PART1 2
ITEM1 PART1 SUBPART1 1
ITEM1 PART1 SUBPART2 2
ITEM1 PART2 3
ITEM1 PART2 1
ITEM1 PART2 SUBPART1 1
ITEM1 PART2 SUBPART2 3
ITEM2 2
ITEM2 2
ITEM2 PART1 1
ITEM2 PART1 SUBPART1 4
ITEM2 PART1 SUBPART2 1
ITEM2 PART2 3
ITEM2 PART2 3
ITEM2 PART2 SUBPART1 2
ITEM2 PART2 SUBPART2 4
17 rows selected.
SCOTT@orcl_11gR2> DECLARE
2 TYPE item_rec IS RECORD (
3 item itemexcption.item_id%TYPE,
4 rsncodes itemexcption.reason%TYPE
5 );
6 TYPE item_tab IS TABLE OF item_rec;
7 mod_rec item_tab;
8 itm_rec item_tab;
9 TYPE itemrec IS REF CURSOR;
10 rec itemrec;
11 rec1 itemrec;
12 BEGIN
13 UPDATE itemexcption
14 SET rolled_reason = NULL
15 WHERE rolled_reason IS NOT NULL;
16 COMMIT;
17 /***** Reason Code updation Against the PARTs******************************************************/
18 OPEN rec FOR
19 select part,
20 listagg (reason, ',') within group (order by reason) reason
21 from (select distinct part, reason
22 from itemexcption
23 where part is not null)
24 group by part;
25 LOOP
26 FETCH rec
27 BULK COLLECT INTO mod_rec
28 LIMIT 100000;
29 FORALL i IN mod_rec.FIRST .. mod_rec.LAST
30 SAVE EXCEPTIONS
31 update itemexcption a
32 set a.rolled_reason = mod_rec(i).rsncodes
33 where a.part = mod_rec(i).item
34 and a.sub_part is null;
35 EXIT WHEN rec%NOTFOUND;
36 END LOOP;
37 CLOSE rec;
38 COMMIT;
39 /***** Reason Code updation Against the ITEMs******************************************************/
40 OPEN rec FOR
41 select item_id,
42 listagg (reason, ',') within group (order by reason) reason
43 from (select distinct item_id, reason
44 from itemexcption
45 where item_id is not null)
46 group by item_id;
47 LOOP
48 FETCH rec
49 BULK COLLECT INTO itm_rec
50 LIMIT 100000;
51 FORALL i IN itm_rec.FIRST .. itm_rec.LAST
52 SAVE EXCEPTIONS
53 update itemexcption a
54 set a.rolled_reason = itm_rec(i).rsncodes
55 where a.item_id = itm_rec(i).item
56 and a.part is null;
57 EXIT WHEN rec%NOTFOUND;
58 END LOOP;
59 CLOSE rec;
60 COMMIT;
61 END;
62 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> select *
2 from itemexcption
3 order by item_id, part nulls first, sub_part nulls first
4 /
ITEM_ID PART SUB_PART REASON ROLLED_R
-------- -------- -------- -------- --------
ITEM1 4 1,2,3,4
ITEM1 PART1 2 1,2,4
ITEM1 PART1 SUBPART1 1
ITEM1 PART1 SUBPART2 2
ITEM1 PART2 3 1,2,3,4
ITEM1 PART2 1 1,2,3,4
ITEM1 PART2 SUBPART1 1
ITEM1 PART2 SUBPART2 3
ITEM2 2 1,2,3,4
ITEM2 2 1,2,3,4
ITEM2 PART1 1 1,2,4
ITEM2 PART1 SUBPART1 4
ITEM2 PART1 SUBPART2 1
ITEM2 PART2 3 1,2,3,4
ITEM2 PART2 3 1,2,3,4
ITEM2 PART2 SUBPART1 2
ITEM2 PART2 SUBPART2 4
17 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
|
|
| Re: Bulk update performance issue [message #525522 is a reply to message #525433] |
Mon, 03 October 2011 12:01 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you can't use listagg, then you can use the undocumented wm_concat, as demonstrated below, or any of various other string aggregation techniques. It should be better to do your aggregation in the select, so that it drastically reduces the number of updates. Your method with dense_rank does not do that.
With normal loops, it is unadvisable to commit within the loop, as you may get a snapshot too old error or some such thing. However, forall is a little different and if your system lacks the capacity to process as many rows as you have all at once, then you can decrease the limits (1000 is a typical number) and commit after each update within the forall if you like. Ideally, if your system could handle it, the most efficient method would be to do it all in one sql update statement without any pl/sql.
SCOTT@orcl_11gR2> column item_id format a8
SCOTT@orcl_11gR2> column part format a8
SCOTT@orcl_11gR2> column sub_part format a8
SCOTT@orcl_11gR2> column reason format a8
SCOTT@orcl_11gR2> column rolled_reason format a8
SCOTT@orcl_11gR2> select *
2 from itemexcption
3 order by item_id, part nulls first, sub_part nulls first
4 /
ITEM_ID PART SUB_PART REASON ROLLED_R
-------- -------- -------- -------- --------
ITEM1 4
ITEM1 PART1 2
ITEM1 PART1 SUBPART1 1
ITEM1 PART1 SUBPART2 2
ITEM1 PART2 3
ITEM1 PART2 1
ITEM1 PART2 SUBPART1 1
ITEM1 PART2 SUBPART2 3
ITEM2 2
ITEM2 2
ITEM2 PART1 1
ITEM2 PART1 SUBPART1 4
ITEM2 PART1 SUBPART2 1
ITEM2 PART2 3
ITEM2 PART2 3
ITEM2 PART2 SUBPART1 2
ITEM2 PART2 SUBPART2 4
17 rows selected.
SCOTT@orcl_11gR2> DECLARE
2 TYPE item_rec IS RECORD (
3 item itemexcption.item_id%TYPE,
4 rsncodes itemexcption.reason%TYPE
5 );
6 TYPE item_tab IS TABLE OF item_rec;
7 mod_rec item_tab;
8 itm_rec item_tab;
9 TYPE itemrec IS REF CURSOR;
10 rec itemrec;
11 rec1 itemrec;
12 BEGIN
13 UPDATE itemexcption
14 SET rolled_reason = NULL
15 WHERE rolled_reason IS NOT NULL;
16 COMMIT;
17 /***** Reason Code updation Against the PARTs******************************************************/
18 OPEN rec FOR
19 select part, wm_concat (reason) reason
20 from (select distinct part, reason
21 from itemexcption
22 where part is not null)
23 group by part;
24 LOOP
25 FETCH rec
26 BULK COLLECT INTO mod_rec
27 LIMIT 1000;
28 FORALL i IN mod_rec.FIRST .. mod_rec.LAST
29 SAVE EXCEPTIONS
30 update itemexcption a
31 set a.rolled_reason = mod_rec(i).rsncodes
32 where a.part = mod_rec(i).item
33 and a.sub_part is null;
34 COMMIT;
35 EXIT WHEN rec%NOTFOUND;
36 END LOOP;
37 CLOSE rec;
38 COMMIT;
39 /***** Reason Code updation Against the ITEMs******************************************************/
40 OPEN rec FOR
41 select item_id, wm_concat (reason)
42 from (select distinct item_id, reason
43 from itemexcption
44 where item_id is not null)
45 group by item_id;
46 LOOP
47 FETCH rec
48 BULK COLLECT INTO itm_rec
49 LIMIT 1000;
50 FORALL i IN itm_rec.FIRST .. itm_rec.LAST
51 SAVE EXCEPTIONS
52 update itemexcption a
53 set a.rolled_reason = itm_rec(i).rsncodes
54 where a.item_id = itm_rec(i).item
55 and a.part is null;
56 COMMIT;
57 EXIT WHEN rec%NOTFOUND;
58 END LOOP;
59 CLOSE rec;
60 COMMIT;
61 END;
62 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> select *
2 from itemexcption
3 order by item_id, part nulls first, sub_part nulls first
4 /
ITEM_ID PART SUB_PART REASON ROLLED_R
-------- -------- -------- -------- --------
ITEM1 4 4,1,2,3
ITEM1 PART1 2 2,4,1
ITEM1 PART1 SUBPART1 1
ITEM1 PART1 SUBPART2 2
ITEM1 PART2 3 1,4,2,3
ITEM1 PART2 1 1,4,2,3
ITEM1 PART2 SUBPART1 1
ITEM1 PART2 SUBPART2 3
ITEM2 2 1,2,3,4
ITEM2 2 1,2,3,4
ITEM2 PART1 1 2,4,1
ITEM2 PART1 SUBPART1 4
ITEM2 PART1 SUBPART2 1
ITEM2 PART2 3 1,4,2,3
ITEM2 PART2 3 1,4,2,3
ITEM2 PART2 SUBPART1 2
ITEM2 PART2 SUBPART2 4
17 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: Bulk update performance issue [message #525524 is a reply to message #525522] |
Mon, 03 October 2011 12:15 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You might try using just sql update statements, as deonstrated below, and see if your system can handle it. You might be surprised. If not, there are parameters and such that can be increased.
SCOTT@orcl_11gR2> column item_id format a8
SCOTT@orcl_11gR2> column part format a8
SCOTT@orcl_11gR2> column sub_part format a8
SCOTT@orcl_11gR2> column reason format a8
SCOTT@orcl_11gR2> column rolled_reason format a8
SCOTT@orcl_11gR2> select *
2 from itemexcption
3 order by item_id, part nulls first, sub_part nulls first
4 /
ITEM_ID PART SUB_PART REASON ROLLED_R
-------- -------- -------- -------- --------
ITEM1 4
ITEM1 PART1 2
ITEM1 PART1 SUBPART1 1
ITEM1 PART1 SUBPART2 2
ITEM1 PART2 3
ITEM1 PART2 1
ITEM1 PART2 SUBPART1 1
ITEM1 PART2 SUBPART2 3
ITEM2 2
ITEM2 2
ITEM2 PART1 1
ITEM2 PART1 SUBPART1 4
ITEM2 PART1 SUBPART2 1
ITEM2 PART2 3
ITEM2 PART2 3
ITEM2 PART2 SUBPART1 2
ITEM2 PART2 SUBPART2 4
17 rows selected.
SCOTT@orcl_11gR2> UPDATE itemexcption
2 SET rolled_reason = NULL
3 WHERE rolled_reason IS NOT NULL;
0 rows updated.
SCOTT@orcl_11gR2> COMMIT;
Commit complete.
SCOTT@orcl_11gR2> update itemexcption a
2 set a.rolled_reason =
3 (select rsncodes
4 from (select part, wm_concat (reason) rsncodes
5 from (select distinct part, reason
6 from itemexcption
7 where part is not null)
8 group by part) i
9 where a.part = i.part)
10 where a.sub_part is null;
9 rows updated.
SCOTT@orcl_11gR2> COMMIT;
Commit complete.
SCOTT@orcl_11gR2> update itemexcption a
2 set a.rolled_reason =
3 (select rsncodes
4 from (select item_id, wm_concat (reason) rsncodes
5 from (select distinct item_id, reason
6 from itemexcption
7 where item_id is not null)
8 group by item_id) i
9 where a.item_id = i.item_id)
10 where a.part is null;
3 rows updated.
SCOTT@orcl_11gR2> COMMIT;
Commit complete.
SCOTT@orcl_11gR2> select *
2 from itemexcption
3 order by item_id, part nulls first, sub_part nulls first
4 /
ITEM_ID PART SUB_PART REASON ROLLED_R
-------- -------- -------- -------- --------
ITEM1 4 1,4,3,2
ITEM1 PART1 2 1,4,2
ITEM1 PART1 SUBPART1 1
ITEM1 PART1 SUBPART2 2
ITEM1 PART2 3 1,4,3,2
ITEM1 PART2 1 1,4,3,2
ITEM1 PART2 SUBPART1 1
ITEM1 PART2 SUBPART2 3
ITEM2 2 1,4,3,2
ITEM2 2 1,4,3,2
ITEM2 PART1 1 1,4,2
ITEM2 PART1 SUBPART1 4
ITEM2 PART1 SUBPART2 1
ITEM2 PART2 3 1,4,3,2
ITEM2 PART2 3 1,4,3,2
ITEM2 PART2 SUBPART1 2
ITEM2 PART2 SUBPART2 4
17 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
|
|
|
|
| Re: Bulk update performance issue [message #525563 is a reply to message #525552] |
Mon, 03 October 2011 14:07 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I glanced at your dense_rank too quickly and thought that you had just replaced the row_number in your previous code with dense_rank and missed the sys_connect_by_path. That should work fine with either method as demonstrated below.
SCOTT@orcl_11gR2> column item_id format a8
SCOTT@orcl_11gR2> column part format a8
SCOTT@orcl_11gR2> column sub_part format a8
SCOTT@orcl_11gR2> column reason format a8
SCOTT@orcl_11gR2> column rolled_reason format a8
SCOTT@orcl_11gR2> select *
2 from itemexcption
3 order by item_id, part nulls first, sub_part nulls first
4 /
ITEM_ID PART SUB_PART REASON ROLLED_R
-------- -------- -------- -------- --------
ITEM1 4
ITEM1 PART1 2
ITEM1 PART1 SUBPART1 1
ITEM1 PART1 SUBPART2 2
ITEM1 PART2 3
ITEM1 PART2 1
ITEM1 PART2 SUBPART1 1
ITEM1 PART2 SUBPART2 3
ITEM2 2
ITEM2 2
ITEM2 PART1 1
ITEM2 PART1 SUBPART1 4
ITEM2 PART1 SUBPART2 1
ITEM2 PART2 3
ITEM2 PART2 3
ITEM2 PART2 SUBPART1 2
ITEM2 PART2 SUBPART2 4
17 rows selected.
SCOTT@orcl_11gR2> DECLARE
2 TYPE item_rec IS RECORD (
3 item itemexcption.item_id%TYPE,
4 rsncodes itemexcption.reason%TYPE
5 );
6 TYPE item_tab IS TABLE OF item_rec;
7 mod_rec item_tab;
8 itm_rec item_tab;
9 TYPE itemrec IS REF CURSOR;
10 rec itemrec;
11 rec1 itemrec;
12 BEGIN
13 UPDATE itemexcption
14 SET rolled_reason = NULL
15 WHERE rolled_reason IS NOT NULL;
16 COMMIT;
17 /***** Reason Code updation Against the PARTs******************************************************/
18 OPEN rec FOR
19 select part,
20 ltrim (max (sys_connect_by_path (reason, ',')), ',') rsn_codes
21 from (select part, reason,
22 dense_rank () over
23 (partition by part order by reason) rn
24 from itemexcption
25 where part is not null)
26 start with rn = 1
27 connect by rn = prior rn + 1
28 group by part;
29 LOOP
30 FETCH rec
31 BULK COLLECT INTO mod_rec
32 LIMIT 1000;
33 FORALL i IN mod_rec.FIRST .. mod_rec.LAST
34 SAVE EXCEPTIONS
35 update itemexcption a
36 set a.rolled_reason = mod_rec(i).rsncodes
37 where a.part = mod_rec(i).item
38 and a.sub_part is null;
39 COMMIT;
40 EXIT WHEN rec%NOTFOUND;
41 END LOOP;
42 CLOSE rec;
43 COMMIT;
44 /***** Reason Code updation Against the ITEMs******************************************************/
45 OPEN rec FOR
46 select item_id,
47 ltrim (max (sys_connect_by_path (reason, ',')), ',') rsn_codes
48 from (select item_id, reason,
49 dense_rank () over
50 (partition by item_id order by reason) rn
51 from itemexcption
52 where item_id is not null)
53 start with rn = 1
54 connect by rn = prior rn + 1
55 group by item_id;
56 LOOP
57 FETCH rec
58 BULK COLLECT INTO itm_rec
59 LIMIT 1000;
60 FORALL i IN itm_rec.FIRST .. itm_rec.LAST
61 SAVE EXCEPTIONS
62 update itemexcption a
63 set a.rolled_reason = itm_rec(i).rsncodes
64 where a.item_id = itm_rec(i).item
65 and a.part is null;
66 COMMIT;
67 EXIT WHEN rec%NOTFOUND;
68 END LOOP;
69 CLOSE rec;
70 COMMIT;
71 END;
72 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> select *
2 from itemexcption
3 order by item_id, part nulls first, sub_part nulls first
4 /
ITEM_ID PART SUB_PART REASON ROLLED_R
-------- -------- -------- -------- --------
ITEM1 4 1,2,3,4
ITEM1 PART1 2 1,2,4
ITEM1 PART1 SUBPART1 1
ITEM1 PART1 SUBPART2 2
ITEM1 PART2 3 1,2,4,4
ITEM1 PART2 1 1,2,4,4
ITEM1 PART2 SUBPART1 1
ITEM1 PART2 SUBPART2 3
ITEM2 2 1,2,3,4
ITEM2 2 1,2,3,4
ITEM2 PART1 1 1,2,4
ITEM2 PART1 SUBPART1 4
ITEM2 PART1 SUBPART2 1
ITEM2 PART2 3 1,2,4,4
ITEM2 PART2 3 1,2,4,4
ITEM2 PART2 SUBPART1 2
ITEM2 PART2 SUBPART2 4
17 rows selected.
SCOTT@orcl_11gR2>
SCOTT@orcl_11gR2> column item_id format a8
SCOTT@orcl_11gR2> column part format a8
SCOTT@orcl_11gR2> column sub_part format a8
SCOTT@orcl_11gR2> column reason format a8
SCOTT@orcl_11gR2> column rolled_reason format a8
SCOTT@orcl_11gR2> select *
2 from itemexcption
3 order by item_id, part nulls first, sub_part nulls first
4 /
ITEM_ID PART SUB_PART REASON ROLLED_R
-------- -------- -------- -------- --------
ITEM1 4
ITEM1 PART1 2
ITEM1 PART1 SUBPART1 1
ITEM1 PART1 SUBPART2 2
ITEM1 PART2 3
ITEM1 PART2 1
ITEM1 PART2 SUBPART1 1
ITEM1 PART2 SUBPART2 3
ITEM2 2
ITEM2 2
ITEM2 PART1 1
ITEM2 PART1 SUBPART1 4
ITEM2 PART1 SUBPART2 1
ITEM2 PART2 3
ITEM2 PART2 3
ITEM2 PART2 SUBPART1 2
ITEM2 PART2 SUBPART2 4
17 rows selected.
SCOTT@orcl_11gR2> UPDATE itemexcption
2 SET rolled_reason = NULL
3 WHERE rolled_reason IS NOT NULL;
0 rows updated.
SCOTT@orcl_11gR2> COMMIT;
Commit complete.
SCOTT@orcl_11gR2> update itemexcption a
2 set a.rolled_reason =
3 (select rsncodes
4 from (select part,
5 ltrim (max (sys_connect_by_path (reason, ',')), ',') rsncodes
6 from (select part, reason,
7 dense_rank() over
8 (partition by part order by reason) rn
9 from itemexcption
10 where part is not null)
11 start with rn = 1
12 connect by rn = prior rn + 1
13 group by part) i
14 where a.part = i.part)
15 where a.sub_part is null;
9 rows updated.
SCOTT@orcl_11gR2> COMMIT;
Commit complete.
SCOTT@orcl_11gR2> update itemexcption a
2 set a.rolled_reason =
3 (select rsncodes
4 from (select item_id,
5 ltrim (max (sys_connect_by_path (reason, ',')), ',') rsncodes
6 from (select item_id, reason,
7 dense_rank() over
8 (partition by item_id order by reason) rn
9 from itemexcption
10 where item_id is not null)
11 start with rn = 1
12 connect by rn = prior rn + 1
13 group by item_id) i
14 where a.item_id = i.item_id)
15 where a.part is null;
3 rows updated.
SCOTT@orcl_11gR2> COMMIT;
Commit complete.
SCOTT@orcl_11gR2> select *
2 from itemexcption
3 order by item_id, part nulls first, sub_part nulls first
4 /
ITEM_ID PART SUB_PART REASON ROLLED_R
-------- -------- -------- -------- --------
ITEM1 4 1,2,3,4
ITEM1 PART1 2 1,2,4
ITEM1 PART1 SUBPART1 1
ITEM1 PART1 SUBPART2 2
ITEM1 PART2 3 1,2,4,4
ITEM1 PART2 1 1,2,4,4
ITEM1 PART2 SUBPART1 1
ITEM1 PART2 SUBPART2 3
ITEM2 2 1,2,3,4
ITEM2 2 1,2,3,4
ITEM2 PART1 1 1,2,4
ITEM2 PART1 SUBPART1 4
ITEM2 PART1 SUBPART2 1
ITEM2 PART2 3 1,2,4,4
ITEM2 PART2 3 1,2,4,4
ITEM2 PART2 SUBPART1 2
ITEM2 PART2 SUBPART2 4
17 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
| Re: Bulk update performance issue [message #525862 is a reply to message #525564] |
Thu, 06 October 2011 06:26 |
|
ninan
Messages: 163
Registered: June 2011
Location: Noida
|
Senior Member |
|
|
46 select item_id,
47 ltrim (max (sys_connect_by_path (reason, ',')), ',') rsn_codes
48 from (select item_id, reason,
49 dense_rank () over
50 (partition by item_id order by reason) rn
51 from itemexcption
52 where item_id is not null)
53 start with rn = 1
54 connect by rn = prior rn + 1
55 group by item_id;
The above code is giving an issue with sys_connect_by_path in the following scenario.
For a certain item_id='0700-0787' has got duplicate values against of 500 records with reason=1 and with same part and sub_part as null. Total no. of records for this item is 700
The distinct values of reason is 1,2,4 against this item_id.
If I run the query on this 500 recordset alone the sys_connect_by_path rollups correctly to 1,2,4 for Rolled_reason.
But since this 700 records is subset of a large table with 8 million records, where lot of item_ids have duplicate records like this against them, sys_connect_by_path is failing.
Against this item_id mentioned above the Rolled_reason values was only 1. It did'nt roll up 1,2,4 as final value.
Is this because sys_connect_by_path has some short comings working against large data set ? Is this a bug with sys_connect_by_path.
Anyone came across such issue.
You need to test this with a large table with lot of duplicate records, to encounter this issue.
|
|
|
|
|
|
|
|
| Re: Bulk update performance issue [message #525880 is a reply to message #525875] |
Thu, 06 October 2011 08:17 |
|
ninan
Messages: 163
Registered: June 2011
Location: Noida
|
Senior Member |
|
|
Barbara,
When I said there are duplicate records, the distinct value in this column can be only 4, i.e, 1,2, 3,4. so there is no chance the length can go beyond 8 characters.
But since the sys_connect_by_path has to loop through so many duplicate records, it is not able to connect to the second distinct, even though the inner query with dense_rank() gets these 4 distinct values.
for eg. this is how the data will be
item_id part sub_part reason
---------------------------------
0070787 4
0070787 part1 1
0070787 part1 1
0070787 part1 1
0070787 part1 subpart 1
0070787 part1 subpart 1
0070787 part1 subpart 1
0070787 part1 subpart 1
0070787 part1 subpart 1
0070787 part1 1
0070787 part2 1
0070787 part2 1
0070787 part2 1
0070787 part2 1
0070787 part2 1
0070787 part3 2
0070787 part3 2
0070787 part3 2
0070787 part3 2
0070787 part3 2
like this the records are repeated with values (1 or 2 or 3 or 4) for 500 times ( n times for 1, n times for 2 like that..)
the query
46 select item_id,
47 ltrim (max (sys_connect_by_path (reason, ',')), ',') rsn_codes
48 from (select item_id, reason,
49 dense_rank () over
50 (partition by item_id order by reason) rn
51 from itemexcption
52 where item_id is not null)
53 start with rn = 1
54 connect by rn = prior rn + 1
55 group by item_id;
The dense_rank is getting the distinct values, i.e, 4,1,2
but since the table has around 8 million records with reason ( 1 or 2 or 3 or 4 ) maximum length of rollup will be 8 ( i.e., length of rolled up value 1,2,3,4, ). To get this max string of 8 characters it sys_connect_by_path has to loop through the entire table. Here it fails and just gets the value as 4, which is incorrect rollup of the values returned by dense_rank ( i.e., 4,1,2 )..
I think this is where sys_connect_by_path failing. It may be a bug.
To verify this I extracted only 500 records into another table from the big table, and run the same query on the table with small data set.
It rolled up correctly to 4,1,2, this time.
|
|
|
|
|
|
| Re: Bulk update performance issue [message #525886 is a reply to message #525881] |
Thu, 06 October 2011 08:54 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Ask your sysadmin
The below operations, whilst not identical to yours, were run on a laptop with 4 cores - bog standard work issue - not a fancy one. Took approximately 30-40 minutes all in, the update took about 17 minutes.
create table t nologging as select * from dba_objects
/
create table t2 nologging as select * from dba_objects
/
select count(*) from t
/
alter session enable parallel dml
/
begin
for i in 1..150 loop
insert /*+ append parallel(2)*/ into t select * from t2;
commit;
end loop;
end;
/
select count(*) from t
/
alter table t add test_col number
/
update t set test_col = (object_id * dbms_random.value() )
/
10,965,921 rows updated.
As I say - not identical to yours - but 8 million rows isn't huge.
|
|
|
|
|
|
| Re: Bulk update performance issue [message #529072 is a reply to message #528677] |
Fri, 28 October 2011 10:06 |
Vackar
Messages: 81
Registered: October 2007
|
Member |
|
|
Kevin Meade wrote on Wed, 26 October 2011 06:48Quote:Lessons learnt.: Oracle is good, if you are playing with a low volume of data. Otherwise it will never going to work. Telling this after spending more than a week trying many options avaiable in Oracle.
If you really concluded this then maybe you are better off on SQLSERVER because you clearly don't appreciate what it is you have. SQLSERVER is a toy database compared to Oracle and Oracle will outperform SQLSERVER and any other database in terms of capacity, scalability, functionality, and speed in almost every situation. However, any database can only do so much to fix bad code.
....
Good luck on SQLSERVER. Kevin
I'd have to agree with that. Generally when queries are taking hours, it's becuase you're doing something silly with your code. Fix that 'silly' thing and nothing will beat Oracle - I've yet to come accross a real world example of any RDBMS solution outperforming Oracle.
|
|
|
|
| Re: Bulk update performance issue [message #529221 is a reply to message #529072] |
Sat, 29 October 2011 19:54 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
The problem with your update may be those bitmap indexes. Your update is updating I am guessing half of the rows in the table or more. I have seen great abuse of bitmap indexes. Most people don't understand what they are really for and how to use them.
For this update I would do something like the following. I have not checked any of the sql for syntax, I leave that to you.
Quote:1) do not allow the query to use any of the bitmap indexes on the itemexcption table. This is killing you. Just doing this may be enough to get the speed you want.
2) use SQL like the following. Substitute the appropriate string concatenation method for your version of Oracle. NOTICE THE APPROACH
A) first build your answer set
B) then update the table
C) you can even create a new table
Unless you are working on some slow machines, or have other issues, this update should get done in 10 minutes or less. If you don't like the idea of creating an answerset first, then rewrite the updates as you see fit once you demonstrate the timings you want using these methods.
You should see your query doing FULL TABLE SCANS and variations of HASH JOINS in order to query the data. If you are using indexes for a query that looks at everything, the stop that.
Good luck, Kevin
with
itemexcption as (
select 'ITEM1' item_id,'' part,'' subpart,'4' reason from dual union all
select 'ITEM1' item_id,'PART1' part,'' subpart,'2' reason from dual union all
select 'ITEM1','PART2','','3' from dual union all
select 'ITEM1','PART1','SUBPART1','1' from dual union all
select 'ITEM1','PART1','SUBPART2','2' from dual union all
select 'ITEM1','PART2','','1' from dual union all
select 'ITEM1','PART2','SUBPART1','1' from dual union all
select 'ITEM1','PART2','SUBPART2','3' from dual
)
select *
from itemexcption
order by item_id,part nulls first,subpart nulls first,reason
/
with
itemexcption as (
select 'ITEM1' item_id,'' part,'' subpart,'4' reason from dual union all
select 'ITEM1' item_id,'PART1' part,'' subpart,'2' reason from dual union all
select 'ITEM1','PART2','','3' from dual union all
select 'ITEM1','PART1','SUBPART1','1' from dual union all
select 'ITEM1','PART1','SUBPART2','2' from dual union all
select 'ITEM1','PART2','','1' from dual union all
select 'ITEM1','PART2','SUBPART1','1' from dual union all
select 'ITEM1','PART2','SUBPART2','3' from dual
)
, distinct_list as (
select distinct item_id,reason
from itemexcption
)
select *
from distinct_list
/
with
itemexcption as (
select 'ITEM1' item_id,'' part,'' subpart,'4' reason from dual union all
select 'ITEM1' item_id,'PART1' part,'' subpart,'2' reason from dual union all
select 'ITEM1','PART2','','3' from dual union all
select 'ITEM1','PART1','SUBPART1','1' from dual union all
select 'ITEM1','PART1','SUBPART2','2' from dual union all
select 'ITEM1','PART2','','1' from dual union all
select 'ITEM1','PART2','SUBPART1','1' from dual union all
select 'ITEM1','PART2','SUBPART2','3' from dual
)
, distinct_list as (
select distinct item_id,reason
from itemexcption
)
--
-- substitute the best available string aggregation method for your version of Oracle
-- try to stay away from PLSQL functions if possible
--
, solution_11g as (
select item_id,listagg(reason,',') within group (order by reason) rolled_reason
from distinct_list
group by item_id
)
select *
from solution_11g
/
create table kev_item_temp
nologging
as
with
distinct_list as (
select distinct item_id,reason
from itemexcption
where item_id is not null
)
, solution_11g as (
select item_id,listagg(reason,',') within group (order by reason) rolled_reason
from distinct_list
group by item_id
)
select *
from solution_11g
/
update itemexcption set
rolled_reason = (
select rolled_reason
from kev_item_temp
where kev_item_temp.item_id = itemexcption.item_id
)
where part is null
/
create table kev_item_part_temp
nologging
as
with
distinct_list as (
select distinct item_id,part,reason
from itemexcption
where part is not null
)
, solution_11g as (
select item_id,part,listagg(reason,',') within group (order by reason) rolled_reason
from distinct_list
group by item_id,part
)
select *
from solution_11g
/
update itemexcption set
rolled_reason = (
select rolled_reason
from kev_item_part_temp
where kev_item_part_temp.item_id = itemexcption.item_id
and kev_item_part_temp.part_id = itemexcption.part_id
)
where subpart is null
/
create table new_itemexcption
nologging
as
select itemexcption.item_id
, itemexcption.part
, itemexcption.reason
, case when itemexcption.part is not null
then kev_item_part_temp.rolled_reason
else kev_item_temp.rolled_reason
end rolled_reason
from itemexcption
, kev_item_temp
, kev_item_part_temp
where itemexcption.item_id = kev_item_temp.item_id
and itemexcption.item_id = kev_item_part_temp.item_id(+)
and itemexcption.part = kev_item_part_temp.part_id(+)
and subpart is null
/[Updated on: Sun, 30 October 2011 01:33] by Moderator Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Thu Nov 28 06:12:24 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
