Table of Contents

Oracle Database - The effect of ArraySize on Logical I/O

About

ARRAYSIZE is the number of rows Oracle returns to a client when they ask for the next row. The client will then buffer these rows and use them before asking the database for the next set of rows.

Affect

The ARRAYSIZE may have a very material affect on the logical I/O performed by a query, resulting from the fact that if you have to access the same block over and over again across calls (across fetch calls specifically in this case) to the database, Oracle must retrieve that block again from the buffer cache.

Therefore :

Implementation

Every professional programming language can interact with Oracle implements this concept of array fetching.

In PL/SQL, you may use :

In Java/JDBC, there is a prefetch method on a connect or statement object.

Oracle call interface (OCI, a C API) allow you to programmatically set the prefetch size.

Reference