|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
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:
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.