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沒有明顯的區別