适当调大arraysize减少逻辑读

来源：这里教程网时间：2026-03-03 18:12:02 作者：

加大arraysize的值减少逻辑读提高sql返回速度首先介绍下arraysize这个参数，该参数只能在当前会话设置。不是 oracle 端设置的，而是sqlplus设置的，所以我们不能使用设置初始化参数alter system set来改变它，而是使用set命令，该参数的含义就是sqlplus里预抓取的行数，默认值是15行，也就是服务器进程每次从buffer cache中复制15行到PGA中，从PGA 在发送给客户端。与之相关的另一个参数就是SDU(session data unit)，预抓取在PGA所占的内存大小和SDU大小相关，默认是8K. SQL> show arraysize arraysize 15 SQL> create table jason as select * from dba_objects; Table created. SQL> SQL> SQL> select count(1) from dba_objects; COUNT(1) ———- 50042 SQL> SQL> SQL> set autot trace SQL> SQL> SQL> SQL> select * from jason; 50042 rows selected. Execution Plan ———————————————————- Plan hash value: 1258222954 ————————————————————————— | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ————————————————————————— | 0 | SELECT STATEMENT | | 44426 | 7679K| 155 (2)| 00:00:02 | | 1 | TABLE ACCESS FULL| JASON | 44426 | 7679K| 155 (2)| 00:00:02 | ————————————————————————— Note —– – dynamic sampling used for this statement Statistics ———————————————————- 0 recursive calls 0 db block gets 3992 consistent gets 0 physical reads 0 redo size 2544199 bytes sent via SQL*Net to client 37188 bytes received via SQL*Net from client 3338 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 50042 rows processed SQL> set arraysize 400 SQL> SQL> SQL> SQL> / SQL> / 50042 rows selected. Execution Plan ———————————————————- Plan hash value: 1258222954 ————————————————————————— | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ————————————————————————— | 0 | SELECT STATEMENT | | 44426 | 7679K| 155 (2)| 00:00:02 | | 1 | TABLE ACCESS FULL| JASON | 44426 | 7679K| 155 (2)| 00:00:02 | ————————————————————————— Note —– – dynamic sampling used for this statement Statistics ———————————————————- 0 recursive calls 0 db block gets 817 consistent gets 0 physical reads 0 redo size 1956586 bytes sent via SQL*Net to client 1867 bytes received via SQL*Net from client 127 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 50042 rows processed 可以看到当我们把arraysize从默认的15调整到400后consistent gets逻辑读从原来的3992下降到817，bytes received via SQL*Net from client相关的值也有明显的下降。 arraysize参数的设置对大数据的范围比如全表扫性能提高还是比较大的，所以在网络带宽允许的情况下适当调大arraysize和估算SDU的值，对减少逻辑读，减少网络交互性，从而介绍CPU的消耗帮助还是比较大的。