oci_set_prefetch
Sets the number of rows to be buffered by the Oracle Client libraries after a successful query call to oci_execute and for each subsequent internal fetch request to the database. For queries returning a large number of rows, performance can be significantly improved by increasing the prefetch count above the default oci8.default_prefetch value.
Prefetching is Oracle's efficient way of returning more than one data row from the database in each network request. This can result in better network and CPU utilization. The buffering of rows is internal to OCI8 and the behavior of OCI8 fetching functions is unchanged regardless of the prefetch count. For example, oci_fetch_row will always return one row. The prefetch buffer is per-statement and is not used by re-executed statements or by other connections.
Call oci_set_prefetch before calling oci_execute.
A tuning goal is to set the prefetch value to a reasonable size for the network and database to handle. For queries returning a very large number of rows, overall system efficiency might be better if rows are retrieved from the database in several chunks (i.e set the prefetch value smaller than the number of rows). This allows the database to handle other users' statements while the PHP script is processing the current set of rows.
Query prefetching was introduced in Oracle 8i. REF CURSOR prefetching was introduced in Oracle 11gR2 and occurs when PHP is linked with Oracle 11gR2 (or later) Client libraries. Nested cursor prefetching was introduced in Oracle 11gR2 and requires both the Oracle Client libraries and the database to be version 11gR2 or greater.
Prefetching is not supported when queries contain LONG or LOB columns. The prefetch value is ignored and single-row fetches will be used in all the situations when prefetching is not supported.
When using Oracle Database 12c, the prefetch value set by PHP can be overridden by Oracle's client oraaccess.xml configuration file. Refer to Oracle documentation for more detail.
Parameters
- statement
-
A valid OCI8 statement identifier created by oci_parse and executed by oci_execute, or a REF CURSOR statement identifier.
- rows
-
The number of rows to be prefetched, >= 0
Return Values
Returns true on success or false on failure.
Changelog
| Version | Description |
| PECL OCI8 1.4 | Before this release, rows must be >= 1. |
| PECL OCI8 1.3.4 | Before this release, prefetching was limited to the lesser of rows rows and 1024 * rows bytes. The byte size restriction has now been removed. |