2014-09-25 BaoXinjian

一、摘要

客户端Arraysize的设置实际上可以理解为设置数组的大小来保存sql调用的返回值。设置适当的arraysize有几个好处：

1.减少读data block的次数，也就是consistent gets的次数。

假设一个block有n行，如果每次取一行，则对该block的逻辑读次数就是n，尽管逻辑读是内存读，速度很快，但并发量很大时也会使数据库的压力很大。

如果每次取>n行，则该block只需读一次。因此合理设置arraysize能大大减轻数据库压力。

2.在PLSQL中减少plsql引擎和sql引擎的切换次数，提高效率

3.通过数组存放结果，减少客户端和数据库服务器的调用次数，减少往返时间

二、SQLPLUS设置数组访问的方法

1.  Sqlplus的arraysize  Sqlplus的arraysize参数表示数据库一次返回给客户端的行数，缺省是15。如果返回数据量大，可以调大此参数:

Set arraysize nnnn

该参数最大为5000

测试一：缺省arraysize  

select * from test_table where rownum <100000;

99999 rows selected.

lapsed: 00:00:06.12

Statistics  

----------------------------------------------------------

4573

recursive calls

测试二：SQL> set arraysize 1000  

select * from test_table where rownum <100000;

99999 rows selected.

三、PLSQL Bulk Collect设置数组访问的方法

测试一：不使用bulk collect,一次fetch一条记录

select a.name name, b.value 

from v$statname a, v$mystat b 

where a.statistic# = b.statistic# and (a.name) in( ‘consistent gets‘)

order by name;

100759

declare 

cursor c1 is select user_id from test_table where rownum<100000;

v_id varchar2(30);

begin 

open c1;

loop 

FETCH c1 INTO v_id;

exit when c1%notfound;

null;

end loop;

close c1;

end;
/

select a.name name, b.value  
from v$statname a, v$mystat b  
where a.statistic# = b.statistic# and (a.name) in( ‘consistent gets‘)
order by name;

测试二：设置bulk collect，一次取100条 select a.name name, b.value
  from v$statname a, v$mystat b
   where a.statistic# = b.statistic# and (a.name) in( ‘consistent gets‘)    order by name; 110129 declare
TYPE VarcharTab IS TABLE OF varchar2(30) INDEX BY BINARY_INTEGER; cursor c1 is select user_id from test_table where rownum<100000; v_id VarcharTab; begin
   open c1;    loop
   FETCH c1 BULK COLLECT INTO v_id LIMIT 100;    exit when c1%notfound;  
   null;    end loop;    close c1; end; /
Elapsed: 00:00:00.09
select a.name name, b.value
  from v$statname a, v$mystat b
   where a.statistic# = b.statistic# and (a.name) in( ‘consistent gets‘)    order by name; 113001

测试三：设置bulk collect，一次取5000条 declare
TYPE VarcharTab IS TABLE OF varchar2(30) INDEX BY BINARY_INTEGER; cursor c1 is select user_id from test_table where rownum<100000; v_id VarcharTab; begin
   open c1;    loop
   FETCH c1 BULK COLLECT INTO v_id LIMIT 5000;    exit when c1%notfound; 
   null;    end loop;    close c1; end; /

Elapsed: 00:00:00.08
select a.name name, b.value
  from v$statname a, v$mystat b
   where a.statistic# = b.statistic# and (a.name) in( ‘consistent gets‘)    order by name;  
 114905

********************作者：鲍新建********************

PLSQL_性能优化系列10_Oracle Array