使用BULK COLLECT和FORALL来提升性能——《Oracle高效设计》学习笔记

来源：互联网发布：java项目打jar 编辑：程序博客网时间：2024/05/16 03:19

使用PL/SQL中的bulk collect语句，相当于在sql*plus设置araysize，如果使用的恰当，对提高性能会有帮助。

先看一个简单例子：

exec runstats_pkg.rs_start;

begin

for i in 1 .. 5000

loop

for x in ( select ename, empno, hiredate from emp )

loop

null;

end loop;

end;

exec runstats_pkg.rs_middle;

declare

l_ename dbms_sql.varchar2_table;

l_empno dbms_sql.number_table;

l_hiredate dbms_sql.date_table;

begin

for i in 1 .. 5000

loop

select ename, empno, hiredate

bulk collect into l_ename, l_empno, l_hiredate

from emp;

end loop;

end;

exec runstats_pkg.rs_stop(500);

执行结果如下：

Run1 ran in 5149 hsecs

Run2 ran in 3164 hsecs

run 1 ran in 162.74% of the time

Name Run1 Run2 Diff

LATCH.session allocation 416 1,654 1,238

STAT...recursive cpu usage 4,304 3,055 -1,249

STAT...DB time 5,155 3,169 -1,986

STAT...CPU used by this sessio 5,123 3,130 -1,993

STAT...Elapsed Time 5,161 3,165 -1,996

STAT...CPU used when call star 5,128 3,132 -1,996

LATCH.shared pool 563 5,571 5,008

LATCH.simulator hash latch 65,069 35,050 -30,019

LATCH.simulator lru latch 65,069 35,050 -30,019

STAT...session uga memory 0 65,464 65,464

STAT...session pga memory -65,536 65,536 131,072

STAT...session uga memory max 254,380 0 -254,380

STAT...session pga memory max 262,144 0 -262,144

STAT...table scan blocks gotte 835,000 340,000 -495,000

STAT...no work - consistent re 835,140 340,057 -495,083

STAT...consistent gets from ca 850,322 355,113 -495,209

STAT...consistent gets 850,322 355,113 -495,209

STAT...session logical reads 850,360 355,142 -495,218

STAT...recursive calls 500,644 5,227 -495,417

LATCH.cache buffers chains 1,701,754 711,257 -990,497

STAT...table scan rows gotten 122,805,000 49,995,000 -72,810,000

Run1 latches total versus runs -- difference and pct

Run1 Run2 Diff Pct

1,862,480 815,420 -1,047,060 228.41%

从测试结果看，使用了buck collect后，效率有明显的改进，这和设置arraysize是一个道理。查询一下emp表的记录数，

select count(*) from emp;

COUNT(*)

----------

9999

记录有点多。可以通过使用limit语句来控制一次buck collect的记录数。

declare

cursor c is select ename, empno, hiredate from emp;

l_ename dbms_sql.varchar2_table;

l_empno dbms_sql.number_table;

l_hiredate dbms_sql.date_table;

begin

for i in 1 .. 5000

loop

open c;

loop

fetch c bulk collect into l_ename, l_empno, l_hiredate limit 500;

exit when c%notfound;

end loop;

close c;

end loop;

end;

对比测试结果：

Run1 ran in 5135 hsecs

Run2 ran in 3158 hsecs

run 1 ran in 162.6% of the time

Name Run1 Run2 Diff

STAT...recursive cpu usage 4,262 3,000 -1,262

STAT...Elapsed Time 5,136 3,161 -1,975

STAT...CPU used by this sessio 5,116 3,097 -2,019

STAT...CPU used when call star 5,121 3,097 -2,024

STAT...DB time 5,133 3,099 -2,034

LATCH.simulator hash latch 65,039 45,012 -20,027

LATCH.simulator lru latch 65,039 45,012 -20,027

STAT...recursive calls 500,003 105,005 -394,998

STAT...session logical reads 850,026 450,035 -399,991

STAT...consistent gets 850,009 450,012 -399,997

STAT...consistent gets from ca 850,009 450,012 -399,997

STAT...no work - consistent re 835,000 435,000 -400,000

STAT...table scan blocks gotte 835,000 435,000 -400,000

STAT...session pga memory -524,288 131,072 655,360

LATCH.cache buffers chains 1,700,349 900,190 -800,159

STAT...table scan rows gotten 122,805,000 63,990,000 -58,815,000

Run1 latches total versus runs -- difference and pct

Run1 Run2 Diff Pct

1,855,919 1,013,744 -842,175 183.08%

运行时间和一次性bulk collect差不多，闩的使用要多一些，好处就是节约了一些内存开销。

Bulk collect主要用于批量获取数据的查询结果集，而forall主要用于数据的批量插入或者删除等操作。

有时需要把bulk collect和forall结合起来，比如某些ETL操作，如果这些操作不能使用直接路径加载，可以考虑使用bulk collect与forall结合的办法。

create table t1

select *

from dba_objects

where 1=0;

create table t2

select *

from dba_objects

where 1=0;

需要赋予访问dba_objects的权限：

conn sys/oracle as sysdba

grant select on dba_objects to system;

conn system/oracle

创建两个存储过程：

create or replace procedure row_at_a_time

begin

for x in ( select * from dba_objects )

loop

insert into t1 values X;

end loop;

end;

create or replace procedure nrows_at_a_time( p_array_size in number )

type array is table of dba_objects%rowtype;

l_data array;

cursor c is select * from dba_objects;

begin

open c;

loop

fetch c bulk collect into l_data LIMIT p_array_size;

forall i in 1 .. l_data.count

insert into t2 values l_data(i);

exit when c%notfound;

end loop;

close c;

end;

做一下对比测试：

exec runstats_pkg.rs_start

exec row_at_a_time

exec runstats_pkg.rs_middle

exec nrows_at_a_time(100)

exec runstats_pkg.rs_stop(500)

测试结果如下：

Run1 ran in 264 hsecs

Run2 ran in 70 hsecs

run 1 ran in 377.14% of the time

Name Run1 Run2 Diff

STAT...active txn count during 635 90 -545

STAT...cleanout - number of kt 635 90 -545

STAT...consistent gets - exami 3,054 2,509 -545

LATCH.undo global data 792 244 -548

LATCH.object queue header oper 2,203 1,438 -765

LATCH.simulator lru latch 4,154 969 -3,185

LATCH.simulator hash latch 4,367 1,101 -3,266

STAT...redo entries 52,656 4,137 -48,519

STAT...session logical reads 60,610 11,451 -49,159

STAT...db block gets from cach 53,843 4,558 -49,285

STAT...db block gets 53,843 4,558 -49,285

STAT...recursive calls 51,554 1,867 -49,687

STAT...execute count 50,256 568 -49,688

STAT...db block changes 104,251 6,827 -97,424

LATCH.library cache pin 100,672 1,296 -99,376

LATCH.library cache 100,726 1,347 -99,379

LATCH.cache buffers chains 275,993 32,220 -243,773

STAT...undo change vector size 3,222,392 222,440 -2,999,952

STAT...redo size 18,155,684 5,761,480 -12,394,204

Run1 latches total versus runs -- difference and pct

Run1 Run2 Diff Pct

494,365 43,154 -451,211 1,145.58%

从测试结果来看，效率有了很大改进。

对于有些必须使用动态sql的情况，如果能够使用forall，进行批量操作，性能会有较大改进。

create or replace type vcArray as table of varchar2(5)

create or replace package dyn_insert2

procedure exec_imd_method1( p_tname in varchar2,

p_value in varchar2 );

procedure exec_imd_method2( p_tname in varchar2,

p_value in vcArray );

end;

create or replace package body dyn_insert2

procedure exec_imd_method1( p_tname in varchar2,

p_value in varchar2 )

begin

execute immediate

'insert into ' || p_tname || '(x) values (:x)'

using p_value;

end;

procedure exec_imd_method2( p_tname in varchar2,

p_value in vcArray )

begin

forall i in 1 .. p_value.count

execute immediate 'insert into ' || p_tname || '(x) values( :x )'

using p_value(i);

end;

exec runstats_pkg.rs_start

declare

l_array vcArray := vcArray();

begin

for i in 1 .. 5000

loop

l_array.extend;

l_array(l_array.count) := i;

if ( mod(l_array.count,1000) = 0 or i = 5000 )

then

dyn_insert2.exec_imd_method2('T', l_array );

l_array := vcArray();

end if;

end loop;

end;

exec runstats_pkg.rs_middle

begin

for i in 1 .. 5000

loop

dyn_insert2.exec_imd_method1('T',i );

end loop;

end;

exec runstats_pkg.rs_stop(500)

测试结果如下：

Run1 ran in 27 hsecs

Run2 ran in 56 hsecs

run 1 ran in 48.21% of the time

Name Run1 Run2 Diff

LATCH.simulator lru latch 28 680 652

LATCH.simulator hash latch 28 689 661

LATCH.session allocation 1,041 227 -814

STAT...recursive calls 946 5,158 4,212

STAT...session logical reads 524 5,259 4,735

STAT...execute count 85 5,021 4,936

STAT...redo entries 85 5,059 4,974

STAT...db block gets 133 5,193 5,060

STAT...db block gets from cach 133 5,193 5,060

LATCH.shared pool 851 10,176 9,325

STAT...db block changes 172 10,152 9,980

STAT...physical read bytes 16,384 0 -16,384

STAT...physical read total byt 16,384 0 -16,384

LATCH.library cache 1,042 20,439 19,397

LATCH.library cache pin 441 20,200 19,759

LATCH.cache buffers chains 1,184 25,764 24,580

STAT...session uga memory 65,464 0 -65,464

STAT...session pga memory 131,072 0 -131,072

STAT...session pga memory max 262,144 131,072 -131,072

STAT...session uga memory max 261,964 65,464 -196,500

STAT...undo change vector size 14,412 322,904 308,492

STAT...redo size 83,016 1,191,100 1,108,084

Run1 latches total versus runs -- difference and pct

Run1 Run2 Diff Pct

5,957 79,170 73,213 7.52%

效率还是有了明显改进，特别是大幅度减少了闩的使用。