Hello Tom,

I know rows prefetching defined on the client is very important because it could impact the roundtrips client/server number and the logicals I/O number.

But I would to know what are the impacts if the rows prefecthing is set with a very large value.

In my following exemple, I do not see any impact directly when I setting arraysize (in sqlplus) with 5 or 5000 to read my table contains 5 rows. If there is not impact, we can set the rows prefetching directly with the max value every time (I hightly doubt of this because every action has a cost necessarily).

SQL> create table T as select * from dba_tables where rownum<=5;

Table created.

SQL> select count(*) from T;

COUNT(*)
----------
5

SQL> set autotrace traceonly stat

SQL> set arraysize 5
SQL> select * from T;


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
4877 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed

SQL> set arraysize 5000
SQL> select * from T;


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
4877 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed

Thanks in advance.