测试SQLPLUS的ARRAYSIZE对性能的影响

arraysize定义了一次返回到SQLPLUS客户端的行数，当扫描了arraysize 行后，停止扫描，返回数据，然后继续扫描。

这个过程就是统计信息中的SQL*Net roundtrips to/from client。

因为arraysize 默认是15行，那么就有一个问题，因为我们一个block中的记录数一般都会超过15行，所以如果按照15行扫描一次，那么每次扫描要多扫描一个数据块，一个数据块也可能就会重复扫描多次。

重复的扫描会增加consistent gets 和 physical reads。增加physical reads，这个很好理解，扫描的越多，物理读的可能性就越大。

consistent gets，这个是从undo里读的数量，Oracle 为了保证数据的一致性，当一个查询很长，在查询之后，数据块被修改，还未提交，再次查询时候，Oracle根据Undo 来构建CR块，这个CR块，可以理解成数据块在之前某个时间的状态。这样通过查询出来的数据就是一致的。

那么如果重复扫描的块越多，需要构建的CR块就会越多，这样读Undo 的机会就会越多，consistent gets 就会越多。

如果数据每次传到客户端有中断，那么这些数据会重新扫描，这样也就增加逻辑读，所以调整arraysize可以减少传的次数，减少逻辑读。

所以通过上面的说明，arraysize 参数如果过低，会影响如physical reads，consistent gets 还有SQL*Net roundtrips to/from client次数。 ---本段引自DAVE博客。

实验结论：实验用表是由dba_objects;创建。通过设置arraysize为1、15、200，可以通过最后的汇总表格得出将arraysize设置为200，可以得到更好的查询性能。

具体表现在：SQL语句执行时间大幅减少，通过Oracle Net从客户端收到的字节总数大幅减少，SQL * Net发送和从客户端接收的字节总数大幅减少--减幅比例接近arraysize尺寸的比例。

所以在使用SQLPLUS客户端查取大数据、SPOOL输出时，可以考虑将arraysize设置的大一点，提高性能。永久设置此参数可以在$ORACLE_HOME/sqlplus/admin/glogin.sql中写入set arraysize 15 这样。

1，使用SQLPLUS的ARRAYSIZE默认值15来进行测试

BYS@bys1>create tabele test2 as select * from dba_objects;
BYS@bys1>alter system flush shared_pool;
System altered.
BYS@bys1>alter system flush buffer_cache;
System altered.

BYS@bys1>set arraysize 15 因为我已经更改过，所以手动再改为默认的15

此设置只在当前SESSION中有用，如果需要永久设置，可以在$ORACLE_HOME/sqlplus/admin/glogin.sql中写入set arraysize 15 这样。

BYS@bys1>set autotrace traceonly stat
BYS@bys1>select * from test2;
72465 rows selected.
Elapsed: 00:00:02.12
Statistics
----------------------------------------------------------
        606  recursive calls
          0  db block gets
       5882  consistent gets
       1052  physical reads
          0  redo size
    8036433  bytes sent via SQL*Net to client
      53549  bytes received via SQL*Net from client
       4832  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      72465  rows processed
汇总：执行时间：02.12秒，606次递归调用，5882 一致读，1052 物理读，8036433 bytes发送，53549 bytes接收，4832次往返
2.将SQLPLUS的ARRAYSIZE值设置为200
BYS@bys1>alter system flush buffer_cache;
System altered.
BYS@bys1>alter system flush shared_pool;
System altered.
BYS@bys1>set arraysize 200
BYS@bys1>set autotrace traceonly stat   只显示统计信息不显示输出结果
BYS@bys1>select * from test2;  
72465 rows selected.
Elapsed: 00:00:00.62
Statistics
----------------------------------------------------------
        606  recursive calls
          0  db block gets
       1491  consistent gets
       1049  physical reads
          0  redo size
    7455593  bytes sent via SQL*Net to client
       4401  bytes received via SQL*Net from client
        364  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      72465  rows processed                    汇总：执行时间：00.62秒，606次递归调用，1491一致读，1049物理读，7455593 bytes发送，4401 bytes接收，364次往返
3.将SQLPLUS的ARRAYSIZE值设置为1
BYS@bys1>alter system flush buffer_cache;
System altered.
BYS@bys1>alter system flush shared_pool;
System altered.
BYS@bys1>set autotrace traceonly stat
BYS@bys1>set arraysize 1
BYS@bys1>select * from test2;
72465 rows selected.
Elapsed: 00:00:08.71
Statistics
----------------------------------------------------------
        606  recursive calls
          0  db block gets
      36830  consistent gets
       1049  physical reads
          0  redo size
   12118659  bytes sent via SQL*Net to client
     398971  bytes received via SQL*Net from client
      36234  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      72465  rows processed

汇总：执行时间：08.71秒，606次递归调用，36830一致读，1049物理读，12118659 bytes发送，398971bytes接收，36234次往返

三种参数的性能汇总表格如下：

ARRAYSIZE 执行时间/秒递归调用一致读物理读客户端接收客户端发送 Oracle网络消息发送和从客户端接收总数

1 8.17 606 36830 1049 12118659 398971 36234

15 2.12 606 5882 1052 8036433 53549 4832

200 0.62 606 1491 1049 7455593 4401 364

倍数

15与1=15各项性能对比（倍） 8.17/2.12=3.85 36830/5882=6.2 1.5 7.4 7.5

15与200=13各项性能对比-倍 3.4 3.9 1.07 12.6 13.2