René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

set arraysize [SQL*Plus]

set arraysize n
Arraysize specifies how many rows SQL*Plus will fetch in a call. The number n can be between 1 and 5000.
This will be demonstrated here. A table with 20 rows is created: 
create table t_20_rows
  as select object_name, object_id
from
  all_objects
where
  rownum < 21;
Now, make SQL*Plus display statistics about SQL statements: 
set autotrace on
Setting the arraysize to five: 
set arraysize 5
Selecting all rows from the table: 
select * from t_20_rows;
Here's what autotrace reports: 
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1566  bytes sent via SQL*Net to client
        532  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed
We're particularyly interested in SQL*Net roundtrips to/from client. It records 5. This makes sense: there are 20 rows in the table, we get 5 (as specified with arraysize) at a time, so there are 4 roundtrips to get the rows and a 5th roundtrip that initiates the query.
Now, let's see what happens if we decrease the arraysize down to 3: 
set arraysize 3
We'd expect 8 roundtrips: 7 to get the row plus one to initiate the query: 
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
       1930  bytes sent via SQL*Net to client
        565  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed

Storing settings across sessions

Settings (such as arraysize) can be stored accross sessions with the glogin.sql and/or login.sql file.