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

> Database > Oracle Database

1 - 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.

3 - 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 :

  • if you ask for 100 rows from the database in a single call, Oracle might be able to fully process a database block and not need to retrieve that block again.
  • if you ask for 15 rows at a time, Oracle might well have to get the same block over and over again to retrieve the same set of rows.
Advertising

4 - Implementation

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

In PL/SQL, you may use :

  • or rely on the implicit array fetch of 100 that is performed for implicit cursor for loops.

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.

5 - Reference