Arraysize对consistent gets的影响
这是一个以前没有留意过的参数，
创建一个测试表
SQL> begin
  2    for i in 1..10000 loop
  3      insert into justin values(i,substr('justin',1,mod(i,6)));
  4    end loop;
  5    commit;
  6  end;
  7  /
 
PL/SQL procedure successfully completed
 
SQL> select count(*) from justin;
 
  COUNT(*)
----------
     10000
现在通过autotrace traceonly查看一下执行代价
SQL> select * from justin;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 863436603
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 10000 |   244K|     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| JUSTIN | 10000 |   244K|     7   (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        122  consistent gets
          0  physical reads
          0  redo size
     104228  bytes sent via SQL*Net to client
       1581  bytes received via SQL*Net from client
        101  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed
而现在的arraysize为100，更改为1000，再测试一下
SQL> set autotrace off
SQL> set arraysize 1000  
SQL> show arraysize
arraysize 1000
SQL> set autotrace traceonly
SQL> select * from justin;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 863436603
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 10000 |   244K|     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| JUSTIN | 10000 |   244K|     7   (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         33  consistent gets
          0  physical reads
          0  redo size
      91088  bytes sent via SQL*Net to client
        591  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed
可以看到在将arraysize调大为1000后,sql的各项指标都有所下降了，其中  SQL*Net roundtrips to/from client由原来的101下降为11，差不多有10倍的提升。
再尝试将arraysize调整到最大值5000,
SQL> show arraysize
arraysize 5000
SQL> select * from justin;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 863436603
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 10000 |   244K|     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| JUSTIN | 10000 |   244K|     7   (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         25  consistent gets
          0  physical reads
          0  redo size
     105600  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed
为了方便对比，列一个表格
arraysize consistent gets SQL*Net roundtrips to/from client bytes received via SQL*Net from client bytes sent via SQL*Net to client
10 1021 1001 11481 271268
50 222 201 2681 104228
100 122 101 1581 104228
1000 33 11 591 91088
5000 25 3 503 105600
注: 为节省篇幅，arraysize=5/50的测试内容没有贴出来
SQL*Net roundtrips to/from client差不多是随arraysize的增长成呈线性下降

查看一下arraysize的详细定义
http://oraforecast.com/orawiki/index.php/How_to_set_ARRAYSIZE_to_speed_up_Oracle_networking
What is ARRAYSIZE?
The ARRAYSIZE defines the number of records of a result set Oracle puts into one network package.
文章中还提到两个相关的概念,SDU/TDU
SDU(session data unit): specifies the size of the packets to send over the network
TDU(transfer data unit): the default packet size used within Oracle*Net
The default value for both SDU and TDU is 2,048 bytes and the maximum value is 32,767 bytes.  The TDU parameter should ideally be a multiple of the SDU parameter.
SDU最大值不能超过网卡中的MTU值，oracle推荐其余MTU等值；而TDU最好为SDU的倍数
现在修改listener.ora中的参数，将SDU/TDU调整为8192
    (SID_DESC =
      (ORACLE_HOME = /data/oracle/product/10.2/db1)
      (SID_NAME = justin)
      (SDU = 8192)
      (TDU = 8192)
)
重启listener之后，重新测试，发现并没有变化
因为现在网卡的MTU为1500，而SDU受MTU影响，设置超过MTU之后便没有效果
[oracle@justin admin]$  ifconfig -a
eth0      Link encap:Ethernet  HWaddr 18:A9:05:6E:19:78 
          inet addr:192.168.110.239  Bcast:192.168.110.255  Mask:255.255.255.0
          inet6 addr: fe80::1aa9:5ff:fe6e:1978/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:29374637 errors:0 dropped:0 overruns:0 frame.:0
          TX packets:30383657 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:100
          RX bytes:5285580141 (4.9 GiB)  TX bytes:16506713289 (15.3 GiB)
          Memory:fbe60000-fbe80000

现在将SDU/TDU调小为256
    (SID_DESC =
      (ORACLE_HOME = /data/oracle/product/10.2/db1)
      (SID_NAME = justin)
      (SDU = 256)
      (TDU = 256)
)
重启监听器，
测试结果还是没有任何改变，不知道是测试方法不当造成的。

从以上测试结果来看
arraysize的设置对sql的consistent gets影响是比较明显的，可是也不能因此一味的扩大arraysize.
因为行从buffer cache中读取出会先缓存在PGA中，经PGA返回给客户端；如果arraysize过大，会消耗更多的PGA，同时对网络传输也有一定的影响。
但并未看到SDU/TDU对consistent gets的影响