Oracle sqlplus的arraysize

场景描述

 开始我觉得这是个神奇的事情。u1用户的t1表共有大约1230块，但是对该表进行全表扫描的时候（select *），竟需要consistent gets接近7000次。
 因为从正常的逻辑来理解，block从物理设备读取到内存，然后cpu从内存读取数据进行计算/过滤，这是一个完整的过程。如果内存中有缓存的block，那么就可以不用经历物理设备读取block的过程，这么看来，consistent gets应该是不大于physical reads的才对。

arraysize参数

这里先简单描述下这个客户端参数arrzysize。

SQL> show arraysize;
arraysize 15

 arraysize是sqlplus的一个特定参数，最大值为5000（java程序中叫做fetchsize）。表示读取一次buffer，最多返回给用户的行的数量。
 呃，那么需要注意一点，当一个block的行数大于arraysize参数时，需要反复读取同一个buffer

场景模拟

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

使用u1用户登录

SQL> connect u1/u1;
Connected.
SQL> show user;
USER is "U1"

查看当前的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越来越接近。