pl/sql中的forall简单测试-白红宇

之前写过一篇bulk collect的博文，只是对于bulk collect做了简单的实例。http://blog.itpub.net/23718752/viewspace-1289696/

其实不光是bulk collect,forall对于pl/sql的性能的提升也是相当大的。

可以参见下面的两个图，可以看到其实在pl/sql中，可能很多时候我们所写的pl/sql代码会在sql引擎和plsql引擎建进行上下文的切换，这个过程还是很耗费时间的。

而forall却是相反，是提供一次上下文切换，会在forall的过程中进行数据的包装处理。一次发送给sql执行器去处理，大大减少了上下文切换时间。

对于此，可以想象，如果cursor中的结果集很庞大，就很可能进行大量的上下文切换，导致执行速度骤降。

我们来做一个简单的实例来说明一下。

我们创建一个表test_data，里面大概有7万多的数据量。

n1@TEST11G> create table test_data as select *from all_objects;

Table created.

n1@TEST11G> select count(*)from test_data;

COUNT(*)

----------

71659

1 row selected

n1@TEST11G> create unique index inx_test_data_pk on test_data(object_id);

Index created.

Elapsed: 00:00:00.48

然后就开始执行存储过程

[ora11g@oel1 plsql]$ cat a.sql

create or replace procedure test_proc as

cursor test_cur is select *from test_data;

i number;

begin

i:=1;

for cur in test_cur

loop

update test_data set object_name=cur.object_name

where object_id=cur.object_id;

dbms_output.put_line('this is a test');

i:=i+1;

end loop;

end;

exec test_proc;

执行的过程中会看到进程占用了大量的cpu资源。可见进行了大量的上下文切换。其实一个主要的信息点就是可以看到输出了大量的日志内容，最后还因为缓存的原因退出了。

......

this is a test

BEGIN test_proc; END;

ERROR at line 1:

ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes

ORA-06512: at "SYS.DBMS_OUTPUT", line 32

ORA-06512: at "SYS.DBMS_OUTPUT", line 97

ORA-06512: at "SYS.DBMS_OUTPUT", line 112

ORA-06512: at "N1.TEST_PROC", line 10

ORA-06512: at line 1

Elapsed: 00:00:13.73

同样的要求，如果使用forall的形式，使用的代码如下。

[ora11g@oel1 plsql]$ cat b.sql

create or replace procedure test_proc as

cursor test_cur is select *from test_data;

type rec_type is table of test_cur%rowtype index by binary_integer;

recs rec_type;

begin

open test_cur;

fetch test_cur bulk collect into recs;

close test_cur;

forall i in 1..recs.COUNT

update test_data set object_name=recs(i).object_name

where object_id=recs(i).object_id;

dbms_output.put_line('this is a test');

end;

这种效果就好得多，可以看到日志中只输出了一次日志信息，意味着只进行了一次上下文切换，这种方法明显要好很多。

n1@TEST11G> exec test_proc;

this is a test

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.67

对于大批量的数据处理还是很值得推荐的。后续会使用dbms_profiler来对此测试一下，可以看出在一些实现点中还是存在着很大的不同。