arraysize参数

这里先简单描述下这个客户端参数arrzysize。
 arraysize是sqlplus的一个特定参数，最大值为5000（java程序中叫做fetchsize）。表示读取一次buffer，最多返回给用户的行的数量。
 需要注意一点，当一个block的行数大于arraysize参数时，需要反复读取同一个buffer

场景模拟

接下来对这个场景进行模拟，分析下arraysize参数对于SQL执行的影响。

使用scott用户登录

SQL> connect scott/scott;
Connected.
SQL> show user;
USER is "SCOTT"

复制

查看当前的arraysize值

SQL> show arraysize
arraysize 15

复制

执行一个大型的查询语句

SQL> select * from t1;
86335 rows selected. 

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
---------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86335 | 8262K| 344 (1)| 00:00:05 |
| 1 | TABLE ACCESS FULL| T1 | 86335 | 8262K| 344 (1)| 00:00:05 
|
---------------------------------------------------------------
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       6907 consistent gets
       1233 physical reads
          0 redo size
    9926753 bytes sent via SQL*Net to client
      63828 bytes received via SQL*Net from client
       5757 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      86335 rows processed

复制

这里我们发现内存读的次数远远大于物理读的次数。

然后增大一下arraysize的值，再执行一遍

SQL> set arraysize 100;
SQL> /
 
86335 rows selected.

Execution Plan

----------------------------------------------------------
Plan hash value: 3617692013
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86335 | 8262K| 344 (1)| 00:00:05 |
| 1 | TABLE ACCESS FULL| T1 | 86335 | 8262K| 344 (1)| 00:00:05 
|
---------------------------------------------------------------
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       2089 consistent gets
       1233 physical reads
          0 redo size
    9016841 bytes sent via SQL*Net to client
      10016 bytes received via SQL*Net from client
        865 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      86335 rows processed

复制

发现内存读次数明显减少，开始接近物理读的次数

然后将arraysize设置为5000

SQL> set arraysize 5000;
SQL> /
 
86335 rows selected.
...
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       1253 consistent gets
       1233 physical reads
          0 redo size
    8859485 bytes sent via SQL*Net to client
        710 bytes received via SQL*Net from client
         19 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
      86335 rows processed

复制

内存读和物理读的次数变得十分接近，这样似乎会让我们看起来觉得是“正常”的了。这里我们先看另一个指标的变化，roundtrips。

   5757 SQL*Net roundtrips to/from client
    865 SQL*Net roundtrips to/from client
     19 SQL*Net roundtrips to/from client
复制

用户一次请求，服务器一次数据返回叫做一次roundtrip。
结合起来，很好解释这个现象了。arraysize的值远远小于block中rows，因此每次只能读取一个block（或者说buffer）的一部分数据，而对于这个全表查询，需要每个block的所有rows，那么同一个bolck就需要被反复读取，带来的结果就是，读取的次数就增加了。由于读取的最小单位就是block，因此我们发现在一个block需要反复读取的时候，一次consistent gets变成了n次。
 在不断增加arraysize大小的过程中，读取的次数逐渐减少，consistent gets与physical reads越来越接近。