Thanks for the question, Raghu.

Asked: September 15, 2002 - 8:30 pm UTC

Last updated: December 24, 2003 - 10:00 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,
I was trying to see if using FORALL would be faster (than FOR..LOOP) for updates and did not find
any significant difference. Could you please tell me
1) If the example I am using below is the correct way to test such a scenario?
2) How can I prove to myself that FORALL is indeed faster?

This test case was done against a database that had no users other than me.

Thanks for your time.
Raghu

SQL> drop TABLE part_temp_tab;

Table dropped.

SQL> CREATE TABLE part_temp_tab(
2 part_no VARCHAR2(35),
3 contract VARCHAR2(5),
4 latest_price NUMBER);

Table created.

SQL> INSERT INTO part_temp_tab SELECT object_name, 'ABC', rownum FROM
all_objects;

22722 rows created.

SQL> INSERT INTO part_temp_tab SELECT object_name||'_A', 'ABC', rownum FROM
all_objects;

22722 rows created.

SQL> INSERT INTO part_temp_tab SELECT object_name||'_B', 'ABC', rownum FROM
all_objects;

22722 rows created.

SQL> commit;

Commit complete.

SQL> SET SERVEROUT ON SIZE 1000000;
SQL> DECLARE
2 CURSOR GetRec IS
3 SELECT *
4 FROM part_temp_tab;
5 time_1_ NUMBER;
6 BEGIN
7
8 time_1_ := DBMS_UTILITY.GET_TIME;
9 FOR rec_ in GetRec LOOP
10 UPDATE part_temp_tab
11 SET latest_price = latest_price * 2
12 WHERE part_no = rec_.part_no;
13 END LOOP;
14 COMMIT;
15 DBMS_OUTPUT.PUT_LINE('Time (in Seconds) taken to UPDATE 68,169 records
using FOR..LOOP: '||
(DBMS_UTILITY.GET_TIME - time_1_)/100);
16 END;
17 /
Time (in Seconds) taken to UPDATE 68,169 records using FOR..LOOP: 1750.85

PL/SQL procedure successfully completed.

SQL> SET SERVEROUT ON SIZE 1000000;
SQL> DECLARE
2 TYPE Parts IS TABLE OF part_temp_tab.part_no%TYPE;
3 TYPE Contracts IS TABLE OF part_temp_tab.contract%TYPE;
4 TYPE Lprice IS TABLE OF part_temp_tab.latest_price%TYPE;
5
6 Part Parts;
7 Contract Contracts;
8 Price Lprice;
9
10 time_1_ NUMBER;
11 BEGIN
12
13 time_1_ := DBMS_UTILITY.GET_TIME;
14 SELECT part_no,
15 contract,
16 latest_price BULK COLLECT
17 INTO Part,
18 Contract,
19 Price
20 FROM part_temp_tab;
21
22 FORALL rec_ in Part.FIRST..Part.LAST
23 UPDATE part_temp_tab
24 SET latest_price = latest_price * 2
25 WHERE part_no = Part(rec_);
26
27 COMMIT;
28 DBMS_OUTPUT.PUT_LINE('Time (in Seconds) taken to UPDATE 68,169 records
using FORALL: '|| (DB
MS_UTILITY.GET_TIME - time_1_)/100);
29 END;
30 /
Time (in Seconds) taken to UPDATE 68,169 records using FORALL: 1756.13

PL/SQL procedure successfully completed.

and Tom said...

Well, first, you are not comparing apples to apples AND you are so totally skewing the numbers with a FULL SCAN per row to be updated (no index on part_no) that the cost of doing the full scan 68,169 times overshadows the entire test!

So, allow me to rewrite this test as follows, using my simple test harness
</code> http://asktom.oracle.com/~tkyte/runstats.html <code>

ops$tkyte@ORA817DEV.US.ORACLE.COM> CREATE TABLE part_temp_tab(
2 part_no VARCHAR2(35) PRIMARY KEY,
3 contract VARCHAR2(5),
4 latest_price NUMBER);

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> INSERT /*+ APPEND */ INTO part_temp_tab
2 SELECT object_name||to_char(rownum,'fm00009'), 'ABC', rownum
3 FROM all_objects;

23643 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_start number;
3 l_run1 number;
4 l_run2 number;
5
6 TYPE Parts IS TABLE OF part_temp_tab.part_no%TYPE;
7 Part Parts;
8 begin
9 SELECT part_no BULK COLLECT
10 INTO Part
11 FROM part_temp_tab;
12
13 insert into run_stats select 'before', stats.* from stats;
14
15 l_start := dbms_utility.get_time;
16 FOR rec_ in 1 .. part.count
17 loop
18 UPDATE part_temp_tab for_loop
19 SET latest_price = 1
20 WHERE part_no = Part(rec_);
21 end loop;
22 commit;
23 l_run1 := (dbms_utility.get_time-l_start);
24 dbms_output.put_line( l_run1 || ' hsecs' );
25
26 insert into run_stats select 'after 1', stats.* from stats;
27 l_start := dbms_utility.get_time;
28 FORALL rec_ in Part.FIRST..Part.LAST
29 UPDATE part_temp_tab forall
30 SET latest_price = 2
31 WHERE part_no = Part(rec_);
32 commit;
33 l_run2 := (dbms_utility.get_time-l_start);
34 dbms_output.put_line( l_run2 || ' hsecs' );
35 dbms_output.put_line
36 ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
37
38 insert into run_stats select 'after 2', stats.* from stats;
39 end;
40 /
1027 hsecs
911 hsecs
run 1 ran in 112.73% of the time

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
2 ( (c.value-b.value)-(b.value-a.value)) diff
3 from run_stats a, run_stats b, run_stats c
4 where a.name = b.name
5 and b.name = c.name
6 and a.runid = 'before'
7 and b.runid = 'after 1'
8 and c.runid = 'after 2'
9 and (c.value-a.value) > 0
10 and (c.value-b.value) <> (b.value-a.value)
11 order by abs( (c.value-b.value)-(b.value-a.value))
12 /

NAME RUN1 RUN2 DIFF
------------------------------ ---------- ---------- ----------
...
LATCH.checkpoint queue latch 613 428 -185
STAT...change write time 203 646 443
STAT...recursive cpu usage 781 327 -454
STAT...redo size 5807516 5816480 8964
LATCH.cache buffers chains 167131 143521 -23610
STAT...buffer is pinned count 0 23641 23641
STAT...consistent gets 47298 23657 -23641
STAT...execute count 23648 6 -23642
STAT...recursive calls 23675 33 -23642
STAT...session logical reads 71516 47870 -23646
LATCH.library cache 47685 355 -47330
STAT...session pga memory max 17024 186348 169324
STAT...session pga memory 5808 197564 191756

48 rows selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM>

So, while it is interesting to see the wall clock differences -- what is crucial is the LATCHing that is going on.

Latches = Locks = Serialization Device = Scalability Inhibitor = Reduced concurrency.

The forall does signifcantly LESS work in that area.

Look at other things like the session logical reads -- very impressive that they are reduced by about 1/3 with array processing.

It is not all about speed in a single user situation (although with reasonable array sizes, like 100 or so you'll see more benefit there, array processing of thousands of rows just isn't as good) -- it is about building scalable solutions and the array processing example is definitely much more scalable (and faster to boot)

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent

Raghu, September 16, 2002 - 10:41 am UTC

Most informative. I was on a single track (SPEED) and overlooked other crucial factors.Thanks.

One more test

Marcio, September 23, 2003 - 6:17 pm UTC

Tom, I would like have as much performance as possible, will use this fix once and no more, so scalability is no relevant here.
The situation is:

X Y ID
---------- ---------- ----------
A 101 1
102 2
103 3
104 4
105 5
106 6
107 7
108 8
109 9
/10744837_ 110 10
111 11
112 12
113 13
114 14
115 15
116 16
.
.
.

I have to put the previous one into x when x is null. Like this:
X Y ID
---------- ---------- ----------
A 101 1
A 102 2 <-
A 103 3 <-
A 104 4 <-
A 105 5 <-
.
.
.

I can't see this in sql statement so I put this in stored procedure. I coded bulk collect and when I use forall seems to me the same for x in ...
Could be true? The profit here is scalability?

The test:

ops$t_mp00@MRP9I1> @teste_harness
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> create table t as
2 select decode( mod(rownum, 10), 0, substr(object_name,1,10), null) x,
3 rownum+100 y,
4 rownum id
5 from all_objects
6 where rownum <= 2000
7 /

Table created.

ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> update t set x = 'A' where rownum = 1;

1 row updated.

ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> alter table t add constraint t_pk primary key ( id );

Table altered.

ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> create or replace procedure fix_a ( p_array_size in number default 5)
2 as
3 type trec is record
4 ( id dbms_sql.number_table,
5 x dbms_sql.varchar2_table );
6
7 data trec;
8 errors number;
9 l_cnt number default 1;
10 l_x varchar2(30) default null;
11 cursor c is select id, x from t;
12 begin
13 open c;
14 loop
15 dbms_application_info.set_client_info
16 ( 'processing fix_a ' || l_cnt || ' thru ' || (l_cnt+p_array_size-1) );
17
18 fetch c bulk collect into data.id, data.x limit p_array_size;
19
20 if l_x is null then l_x := data.x(1);
21 end if;
22
23 begin
24 for j in 1 .. data.id.count
25 loop
26
27 debug.f( 'Antes de quebrar -> l_x: %s , data.x: %s e j: %s', l_x, data.x(j), j );
28 if l_x <> nvl(data.x(j),l_x) then
29 l_x := data.x(j);
30 end if;
31
32 debug.f( 'Depois de quebrar -> l_x: %s , data.x: %s e j: %s', l_x, data.x(j), j );
33 if data.x(j) is null then
34 data.x(j) := l_x;
35 end if;
36
37 debug.f( 'Depois de comparar -> l_x: %s , data.x: %s e j: %s', l_x, data.x(j), j );
38 update t
39 set x = data.x(j)
40 where id = data.id(j);
41 end loop;
42 end;
43 exit when c%notfound;
44 l_cnt := l_cnt + p_array_size;
45 end loop;
46 close c;
47 end;
48 /

Procedure created.

ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> create or replace procedure fix_b ( p_array_size in number default 5)
2 as
3 type trec is record
4 ( id dbms_sql.number_table,
5 x dbms_sql.varchar2_table );
6
7 data trec;
8 errors number;
9 l_cnt number default 1;
10 l_x varchar2(30) default null;
11 cursor c is select id, x from t;
12 begin
13 open c;
14 loop
15 dbms_application_info.set_client_info
16 ( 'processing ' || l_cnt || ' thru ' || (l_cnt+p_array_size-1) );
17
18 fetch c bulk collect into data.id, data.x limit p_array_size;
19
20 if l_x is null then l_x := data.x(1);
21 end if;
22
23 begin
24 for j in 1 .. data.id.count
25 loop
26
27 debug.f( 'Antes de quebrar -> l_x: %s , data.x: %s e j: %s', l_x, data.x(j), j );
28 if l_x <> nvl(data.x(j),l_x) then
29 l_x := data.x(j);
30 end if;
31
32 debug.f( 'Depois de quebrar -> l_x: %s , data.x: %s e j: %s', l_x, data.x(j), j );
33 if data.x(j) is null then
34 data.x(j) := l_x;
35 end if;
36
37 debug.f( 'Depois de comparar -> l_x: %s , data.x: %s e j: %s', l_x, data.x(j), j );
38 end loop;
39 forall i in 1 .. data.id.count
40 update t forall
41 set x = data.x(i)
42 where id = data.id(i);
43 end;
44 exit when c%notfound;
45 l_cnt := l_cnt + p_array_size;
46 end loop;
47 close c;
48 end;
49 /

Procedure created.

ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> begin
2
3 runstats_pkg.rs_start;
4
5 fix_a(100);
6
7 runstats_pkg.rs_middle;
8
9 fix_b(100);
10
11 runstats_pkg.rs_stop;
12
13 end;
14 /
Run1 ran in 481 hsecs
Run2 ran in 384 hsecs
run 1 ran in 125,26% of the time

Name Run1 Run2 Diff
LATCH.active checkpoint queue 1 2 1
LATCH.session timer 1 2 1
STAT...calls to kcmgas 12 13 1
STAT...commit cleanouts succes 1 0 -1
STAT...messages sent 4 3 -1
STAT...parse count (hard) 2 1 -1
STAT...cursor authentications 1 0 -1
STAT...commit cleanouts 1 0 -1
LATCH.Consistent RBA 5 3 -2
STAT...deferred (CURRENT) bloc 2 0 -2
STAT...table fetch by rowid 2 0 -2
STAT...parse time elapsed 2 0 -2
STAT...parse time cpu 2 0 -2
STAT...index fetch by key 2,002 2,000 -2
LATCH.channel operations paren 4 2 -2
STAT...cluster key scans 2 0 -2
LATCH.dml lock allocation 9 12 3
LATCH.lgwr LWN SCN 5 2 -3
LATCH.mostly latch-free SCN 5 2 -3
LATCH.simulator lru latch 5 2 -3
LATCH.session idle bit 24 21 -3
STAT...sorts (memory) 7 4 -3
STAT...cluster key scan block 4 0 -4
STAT...physical reads 4 0 -4
LATCH.library cache load lock 6 0 -6
LATCH.session allocation 6 0 -6
STAT...redo ordering marks 6 0 -6
STAT...index scans kdiixs1 6,006 6,000 -6
LATCH.child cursor hash table 20 12 -8
STAT...CR blocks created 5 13 8
STAT...enqueue releases 9 1 -8
STAT...parse count (total) 10 2 -8
STAT...rollbacks only - consis 5 13 8
STAT...opened cursors cumulati 10 2 -8
STAT...change write time 15 7 -8
STAT...calls to get snapshot s 8,014 8,005 -9
STAT...enqueue requests 10 1 -9
STAT...shared hash latch upgra 6,010 6,000 -10
STAT...table scan blocks gotte 24 34 10
LATCH.redo writing 22 10 -12
LATCH.enqueue hash chains 31 16 -15
STAT...no work - consistent re 26 41 15
LATCH.checkpoint queue latch 64 80 16
LATCH.enqueues 24 4 -20
LATCH.messages 43 22 -21
STAT...buffer is not pinned co 33 54 21
LATCH.row cache enqueue latch 32 10 -22
LATCH.process queue reference 129 99 -30
LATCH.row cache objects 40 10 -30
STAT...free buffer requested 67 30 -37
LATCH.library cache pin alloca 57 19 -38
LATCH.cache buffers lru chain 67 28 -39
LATCH.SQL memory manager worka 134 67 -67
STAT...recursive cpu usage 297 212 -85
LATCH.simulator hash latch 269 516 247
STAT...consistent changes 676 1,295 619
STAT...data blocks consistent 183 802 619
LATCH.undo global data 1,108 17 -1,091
STAT...active txn count during 1,098 4 -1,094
STAT...cleanout - number of kt 1,098 4 -1,094
STAT...calls to kcmgcs 1,099 4 -1,095
STAT...db block gets 6,988 5,812 -1,176
STAT...buffer is pinned count 0 1,960 1,960
STAT...execute count 8,010 6,022 -1,988
LATCH.shared pool 8,085 6,059 -2,026
STAT...recursive calls 8,104 6,045 -2,059
STAT...redo entries 4,704 2,487 -2,217
LATCH.redo allocation 4,715 2,491 -2,224
STAT...consistent gets 11,327 8,883 -2,444
STAT...consistent gets - exami 5,288 2,826 -2,462
STAT...session logical reads 18,315 14,695 -3,620
LATCH.library cache pin 16,105 12,084 -4,021
LATCH.library cache 16,174 12,113 -4,061
STAT...db block changes 9,437 5,008 -4,429
LATCH.cache buffers chains 46,990 35,694 -11,296
STAT...redo size 1,041,048 565,964 -475,084

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
94,180 69,399 -24,781 135.71%

PL/SQL procedure successfully completed.

ops$t_mp00@MRP9I1>
ops$t_mp00@MRP9I1> set echo off
ops$t_mp00@MRP9I1>

September 23, 2003 - 8:12 pm UTC

that it ran faster doesn't count either?

it ran faster
it used less latches

seems to be a bonus all around?

sql statement

Marcio, September 24, 2003 - 12:53 am UTC

yes for sure. But i was thinking about any analitic function could do it without procedure code.

x y
--- ---
a 1
2
3
b 4
5
6
.
.
.

To be...
x y
--- ---
a 1
a 2
a 3
b 4
b 5
b 6
.
.
.

I am sure sql statement will be so faster, but I couldn't see any possibility. Is there one?

Thks,
Marcio

September 24, 2003 - 9:30 am UTC

oh, ok here is sql, it was not clear what you were after.  it is a trick, we can encode the max value using row_number to "carry forward":

ops$tkyte@ORA920LAP> drop table t;

Table dropped.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create table t
  2  as
  3  select decode( mod(rownum,10), 1, rownum, null ) a, rownum b
  4    from all_users;

Table created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select a, b, substr(max( to_char(rn,'fm0000000000') || a ) over (order by b),11) a_carried
  2    from (
  3  select a, b, decode( a, null, to_number(null), row_number() over (order by b)) rn
  4    from t
  5         )
  6  /

         A          B A_CARRIED
---------- ---------- -----------------------------------------
         1          1 1
                    2 1
                    3 1
                    4 1
                    5 1
                    6 1
                    7 1
                    8 1
                    9 1
                   10 1
        11         11 11
                   12 11
                   13 11
                   14 11
                   15 11
                   16 11
                   17 11
                   18 11
                   19 11
                   20 11
        21         21 21

* * * * * plus *

Marcio, September 24, 2003 - 3:41 pm UTC

* * * * * plus * Like The creme de la creme!
Thank you so much!

Look that:
=================================
Run1 ran in 832 hsecs
Run2 ran in 263 hsecs
run 1 ran in 316,35% of the time
---------------------------------

about 2000 rows, but will run in 70.000.000

Nice

Mary, December 24, 2003 - 12:29 am UTC

Dear Sir,
Do you have a loop structure for the output?
1
1 2
1 2 3
1 2 3 4
Thanks in advance.

December 24, 2003 - 10:00 am UTC

huh?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Excellent

One more test

sql statement

* * * * * plus *

Nice

More to Explore

PL/SQL demos

PL/SQL docs

UPDATES using FORALL and FOR..LOOP

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Excellent

One more test

sql statement

* * * * * plus *

Nice

More to Explore

PL/SQL demos

PL/SQL docs