Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)

Sometimes you may use SQLPLUS spool table data. How can you speed up the spool? SQLPLUS has a row prefetch Option

In SQLPLUS, the default arraysize is 15.

SQL> show arraysize
Arraysize 15

It indicates that only 15 rows of records are transferred from the Oracle server to the client (SQLPLUS) at a time. Of course, JDBC and WEBLOGIC also have row prefetch.

For example:

SQL> select * from test where owner = 'adwu _ OPTIMA_AP11 ';

773 rows selected.

Elapsed: 00:00:30. 95

Execution Plan
----------------------------------------------------------
Plan hash value: 217508114

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 593 | 134K | 882 (3) | 00:00:03 |
| * 1 | table access full | TEST | 593 | 134K | 882 (3) | 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------

1-filter ("OWNER" = 'adwu _ OPTIMA_AP11 ')

Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
2976 consistent gets
0 physical reads
0 redo size
50484 bytes sent via SQL * Net to client
597 bytes encoded ed via SQL * Net from client
53 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
773 rows processed

SQL> set arraysize 5000
SQL> select * from test where owner = 'adwu _ OPTIMA_AP11 ';

773 rows selected.

Elapsed: 00:00:16. 06

Execution Plan
----------------------------------------------------------
Plan hash value: 217508114

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 593 | 134K | 882 (3) | 00:00:03 |
| * 1 | table access full | TEST | 593 | 134K | 882 (3) | 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------

1-filter ("OWNER" = 'adwu _ OPTIMA_AP11 ')

Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
2927 consistent gets
0 physical reads
0 redo size
47800 bytes sent via SQL * Net to client
241 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
773 rows processed

After setting the arraysize, the number of interactions between the SQLPLUS client and the database Server is significantly reduced, which is why 773 rows of data are returned twice faster than the first time. You can also see that, the second logical read is lower than the first logical read, which means setting row prefetch will affect logical read.