arraysize 对性能的影响

         arraysize 和 fetch size 参数都是客户段的一个参数，需要在客户段来设置，arraysize 是在sqlplus 中设置的，如果通过程序去连数据库，那么这个参数就是fetch size 。Arraysize specifies how manyrows SQL*Plus will fetch in a  call. The number n can be between 1 and 5000。arraysize定义了一次返回到客户端的行数，当扫描了arraysize 行后，停止扫描，返回数据，然后继续扫描。arraysize 默认是15行，如果按照15行扫描一次，那么每次扫描要多次扫描一个数据块，一个数据块也可能就会重复扫描多次。增加arraysize可以减少逻辑读。

SQL> createtable test(a1 number(10));

SQL> begin

  2   for i in 1 .. 1000 loop

  3     insert into test values(i);

  4   end loop;

  5   commit;

  6   end;

  7  /

SQL> execdbms_stats.gather_table_stats(user,'test');

SQL> showarraysize

arraysize 15

SQL> setautotrace trace stat

SQL> select *from test;

已选择1000行。

统计信息

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

          1 recursive calls

          0 db block gets

         74 consistent gets

          0 physical reads

          0 redo size

      17903 bytes sent via SQL*Net to client

       1111 bytes received via SQL*Net from client

         68 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

       1000 rows processed

SQL> setarraysize 100

SQL> select *from test;

已选择1000行。

统计信息

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

          0 recursive calls

          0 db block gets

         17 consistent gets

          0 physical reads

          0 redo size

      10493 bytes sent via SQL*Net to client

        484 bytes received via SQL*Net from client

         11 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

       1000 rows processed

 

SQL> setarraysize 500

SQL> select *from test;

已选择1000行。

统计信息

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

          0 recursive calls

          0 db block gets

          9 consistent gets

          0 physical reads

          0 redo size

       9453 bytes sent via SQL*Net to client

        396 bytes received via SQL*Net from client

          3 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

       1000 rows processed

SQL> setarraysize 1000

SQL> select *from test;

已选择1000行。

 统计信息

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

          0 recursive calls

          0 db block gets

          8 consistent gets

          0 physical reads

          0 redo size

       9323 bytes sent via SQL*Net to client

        385 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

       1000 rows processed