oracle的forall与for的区别,forall_for loop性能对比测试_plsql

SQL> show user

USER is "SCOTT"

SQL> create table blktest(num number(20),name varchar2(50));

Table created.

set time on timing on

create or replace procedure p_compare

is

type numtab is table of blktest.num%type index by binary_integer;

type nametab is table of blktest.name%type index by binary_integer;

r_num numtab;

r_name nametab;

t1 number;

t2 number;

t3 number;

begin

for i in 1..1000000 loop

r_num(i):=i;

r_name(i):='rec no '||to_char(i);

end loop;

select dbms_utility.get_time into t1 from dual;

for i in 1..1000000 loop

insert into blktest values(r_num(i),r_name(i));

if mod(i,10000)=0 then

commit;

end if;

end loop;

select dbms_utility.get_time into t2 from dual;

forall i in 1..1000000

insert into blktest values(r_num(i),r_name(i));

commit;

select dbms_utility.get_time into t3 from dual;

dbms_output.put_line('execution time(hsecs)');

dbms_output.put_line('for loop:'||to_char(t2 - t1));

dbms_output.put_line('forall:'||to_char(t3 -t2));

end;

/

Procedure created.

Elapsed: 00:00:00.48

22:49:36 SQL>

22:49:47 SQL>

22:49:47 SQL> set serveroutput on

22:49:53 SQL> exec p_compare;

execution time(hsecs)

for loop:6437

forall:7675

PL/SQL procedure successfully completed.

### tail -f alert_orcl.log 日志频繁出现如下涂红信息

Sat Aug  7 11:22:50 2010

Thread 1 cannot allocate new log, sequence 16

Checkpoint not complete

Current log# 2 seq# 15 mem# 0: /oracle/oradata/orcl/redo02.log

Thread 1 advanced to log sequence 16

Current log# 3 seq# 16 mem# 0: /oracle/oradata/orcl/redo03.log

Sat Aug  7 11:27:04 2010

Thread 1 cannot allocate new log, sequence 17

Checkpoint not complete

Current log# 3 seq# 16 mem# 0: /oracle/oradata/orcl/redo03.log

Thread 1 advanced to log sequence 17

Current log# 1 seq# 17 mem# 0: /oracle/oradata/orcl/redo01.log

Sat Aug  7 11:27:17 2010

Thread 1 cannot allocate new log, sequence 18

Checkpoint not complete

Current log# 1 seq# 17 mem# 0: /oracle/oradata/orcl/redo01.log

Thread 1 advanced to log sequence 18

Current log# 2 seq# 18 mem# 0: /oracle/oradata/orcl/redo02.log

Sat Aug  7 11:27:46 2010

ORACLE Instance orcl - Can not allocate log, archival required

Sat Aug  7 11:27:46 2010

Thread 1 cannot allocate new log, sequence 19

All online logs needed archiving

解决方法：

1.增大redo log的大小

2.增加redo log group的数目.

3.加快dbwr写数据文件速度.

我把log日志文件大小加大到200m，这下日志switch切得慢了，没有以上提示了

不过性能测试还是forall与for loop没有明显的区别