CLIENT_PREFETCH_ROWS

Use CLIENT_PREFETCH_ROWS to enable clients to reduce the number of roundtrips required while fetching rows of a query result set.

Property	Description
Parameter type	Integer
Default value	0 (only client-side settings apply)
Modifiable	ALTER SESSION, ALTER SYSTEM
Modifiable in a PDB	Yes
Range of values	0 to UB4MAXVAL (usually 4294967295) UB4MAXVAL is defined in the oratypes.h header file, which is found in the public directory. Its value may vary according to the operating system you are using.
Basic	No

CLIENT_PREFETCH_ROWS specifies the number of rows to be prefetched by the Oracle client driver, without making any changes to the client application. The client driver buffers the prefetched rows after each successful query execution and for each subsequent fetch request sent to the database.

This parameter applies only to clients that use Oracle Call Interface (OCI) to connect to the database.

This parameter applies only with Oracle Instant Client/Oracle Database Client 19.17 (or later) and 21.8 (or later), for all platforms.

The CLIENT_PREFETCH_ROWS parameter can be set with ALTER SESSION or ALTER SYSTEM. If the parameter value changes using ALTER SESSION, the new value becomes effective for that specific session on subsequent resultset fetches. If the parameter value changes using ALTER SYSTEM, the new value takes effect for the statements that run on connections created after the ALTER SYSTEM command.

For example, if CLIENT_PREFETCH_ROWS is set to 100 and a client application asks to fetch 10 rows, a total of 110 rows are returned to the client driver. The first 10 rows out of the 110 rows are given to the application, and the client driver internally buffers the remaining 100 rows. The next 10 row fetches from the client application, each with 10 rows per fetch iteration can be fulfilled from the 100 rows that are internally buffered by the client driver. This process reduces the number of required network roundtrips to and from the database. In this example, on the 11th fetch, a new network roundtrip is incurred and the database returns the next batch of 110 rows, as long as the result set is not exhausted, and the cycle repeats.

Notes for setting CLIENT_PREFETCH_ROWS:

• When CLIENT_PREFETCH_ROWS is set to a non-zero value, its value takes precedence over the default OCI_ATTR_PREFETCH_ROWS value for prefetch row count.

• If the OCI_ATTR_PREFETCH_ROWS value is set to a non-default value, then the CLIENT_PREFETCH_ROWS value is ignored for the prefetch row count.

• Using CLIENT_PREFETCH_ROWS with OCIAttrSet():

  OCI_ATTR_PREFETCH_ROWS sets the number of top-level rows to be prefetched. The default value is 1 row. However, if CLIENT_PREFETCH_ROWS is set, the number of top-level rows to be prefetched is determined by the following precedence

  1. If you set the OCI_ATTR_PREFETCH_ROWS attribute using OCIAttrSet() function or oraaccess.xml as the value ‘1’, then the database initialization parameter CLIENT_PREFETCH_ROWS value takes precedence and determines the number of top-level rows to be prefetched.

  2. If you set the OCI_ATTR_PREFETCH_ROWS attribute using OCIAttrSet() function or oraaccess.xml as the value ‘x’ other than 1, then ‘x’ number of top-level rows will be prefetched, and the database initialization parameter CLIENT_PREFETCH_ROWS is ignored.

  3. If you do not set an OCI_ATTR_PREFETCH_ROWS value using OCIAttrSet() or oraaccess.xml, then the database initialization parameter CLIENT_PREFETCH_ROWS value takes precedence and determines the number of top-level rows to be prefetched.

• Using CLIENT_PREFETCH_ROWS with OCIAttrGet():

  The function OCIAttrGet() returns the effective prefetch row value set from OCI_ATTR_PREFETCH_ROWS, oraaccess.xml and the database initialization parameter CLIENT_PREFETCH_ROWS. If the OCI_ATTR_PREFETCH_MEMORY value is set, the value returned by OCIAttrGet() might not be the final prefetch rows value and may be restricted to the maximum number of rows allowed by the memory value specified by the OCI_ATTR_PREFETCH_MEMORY attribute.

Note: This parameter is available starting with Oracle AI Database 26ai.

See Also: Oracle Call Interface Developer’s Guide for more information about fetching results and setting the prefetch count, as well as the OCI_ATTR_PREFETCH_MEMORY and OCI_ATTR_PREFETCH_ROWS attributes, and the OCIAttrGet() and OCIAttrSet() functions