Array Size Effects

The array size is the number of rows fetched (or sent, in the case of inserts, updates, and deletes) by the server at a time. It can have a dramatic effect on performance.
SQL> drop table t;

Table dropped.

SQL> create table t as select * from all_objects;

Table created.

SQL> select count(*) from t;
COUNT (*)
----------
29120
SQL> set autotrace traceonly statistics;
SQL> set arraysize 2
SQL> select * from t;

29120 rows selected.

Statistics
----------------------------------------------------------
14779 consistent gets

Note how one half of 29120 (rows fetched) is very close to 14779 , the number of consistent gets. Every row we fetched from the server actually caused it to send two rows back. So, for every two rows of data, we needed to do a logical I/O to get the data. Oracle got a block, took two rows from it, and sent it to SQL*Plus. Then SQL*Plus asked for the next two rows, and Oracle got thatblock again or got the next block, if we had already fetched the data, and returned the next two rows, and so on.
Next, let’s increase the array size:
SQL> set arraysize 5
SQL> select * from t;

29120 rows selected.

Statistics
----------------------
6152 consistent gets

Now, 29120 divided by 5 is about 5824, and that would be the least amount of consistent gets we would be able to achieve (the actual observed number of consistent gets is slightly higher).

All that means is sometimes in order to get two rows, we needed to get two blocks: we got the last row from one block and the first row from the next block.
Let’s increase the array size again:

SQL> set arraysize 10
SQL> select * from t;

29120 rows selected.

Statistics
----------------------
3271 consistent gets

SQL> set arraysize 25
SQL> select * from t;

29120 rows selected.

Statistics
----------------------
1551 consistent gets

SQL> set arraysize 100
SQL> select * from t;

29120 rows selected.


Statistics
----------------------
688 consistent gets

SQL> set arraysize 500
SQL> select * from t;

29120 rows selected.


Statistics
------------------
460 consistent gets
............
As you can see, as the array size goes up, the number of consistent gets goes down. So, does that mean you should set your array size to 5,000, as in this last test? Absolutely not. If you notice, the overall number of consistent gets has not dropped dramatically between array sizes of 100 and 5,000.

It would be better to have more of a stream of information flowing: Ask for 100 rows, get 100 rows, ask for 100, process 100, and so on. That way, both the client and server are more or less continuously processing data, rather than the processing occurring in small bursts.