PHP: Hypertext Preprocessor (original) (raw)

oci_set_prefetch

(PHP 5, PHP 7, PHP 8, PECL OCI8 >= 1.1.0)

oci_set_prefetch — Sets number of rows to be prefetched by queries

Description

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 8_i_. REF CURSOR prefetching was introduced in Oracle 11_g_R2 and occurs when PHP is linked with Oracle 11_g_R2 (or later) Client libraries. Nested cursor prefetching was introduced in Oracle 11_g_R2 and requires both the Oracle Client libraries and the database to be version 11_g_R2 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 12_c_, 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](reserved.constants.php#constant.true) on success or [false](reserved.constants.php#constant.false) on failure.

Examples

Example #1 Changing the default prefetch value for a query

`<?php

$conn

= oci_connect('hr', 'welcome', 'localhost/XE');$stid = oci_parse($conn, 'SELECT * FROM myverybigtable');
oci_set_prefetch($stid, 300); // Set before calling oci_execute()
oci_execute($stid);

echo

"

\n";
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
echo "\n";
foreach ($row as $item) {
echo " \n";
}
echo "\n";
}
echo "
".($item !== null ? htmlentities($item, ENT_QUOTES) : " ")."
\n";oci_free_statement($stid);
oci_close($conn);?>`

Example #2 Changing the default prefetch for a REF CURSOR fetch

`<?php
/*
Create the PL/SQL stored procedure as:

CREATE OR REPLACE PROCEDURE myproc(p1 OUT SYS_REFCURSOR) AS
BEGIN
OPEN p1 FOR SELECT * FROM all_objects WHERE ROWNUM < 5000;
END;
*/ conn=ociconnect(′hr′,′welcome′,′localhost/XE′);conn = oci_connect('hr', 'welcome', 'localhost/XE');conn=ocic​onnect(′hr′,′welcome′,′localhost/XE′);stid = oci_parse($conn, 'BEGIN myproc(:rc); END;'); refcur=ocinewcursor(refcur = oci_new_cursor(refcur=ocin​ewc​ursor(conn);
oci_bind_by_name($stid, ':rc', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);// Change the prefetch before executing the cursor.
// REF CURSOR prefetching works when PHP is linked with Oracle 11gR2 or later Client libraries
oci_set_prefetch($refcur, 200);
oci_execute($refcur);

echo

"

\n";
while ($row = oci_fetch_array($refcur, OCI_ASSOC+OCI_RETURN_NULLS)) {
echo "\n";
foreach ($row as $item) {
echo " \n";
}
echo "\n";
}
echo "
".($item !== null ? htmlentities($item, ENT_QUOTES) : " ")."
\n";oci_free_statement($refcur);
oci_free_statement($stid);
oci_close($conn);?>`

If PHP OCI8 fetches from a REF CURSOR and then passes the REF CURSOR back to a second PL/SQL procedure for further processing, then set the REF CURSOR prefetch count to 0 to avoid rows being "lost" from the result set. The prefetch value is the number of extra rows fetched in each OCI8 internal request to the database, so setting it to 0 means only fetch one row at a time.

Example #3 Setting the prefetch value when passing a REF CURSOR back to Oracle

`<?php

$conn

= oci_connect('hr', 'welcome', 'localhost/orcl');// get the REF CURSOR stid=ociparse(stid = oci_parse(stid=ocip​arse(conn, 'BEGIN myproc(:rc_out); END;'); refcur=ocinewcursor(refcur = oci_new_cursor(refcur=ocin​ewc​ursor(conn);
oci_bind_by_name($stid, ':rc_out', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);// Display two rows, but don't prefetch any extra rows otherwise
// those extra rows would not be passed back to myproc_use_rc().
oci_set_prefetch($refcur, 0);
oci_execute($refcur); row=ocifetcharray(row = oci_fetch_array(row=ocif​etcha​rray(refcur);
var_dump($row); row=ocifetcharray(row = oci_fetch_array(row=ocif​etcha​rray(refcur);
var_dump($row);// pass the REF CURSOR to myproc_use_rc() to do more data processing
// with the result set stid=ociparse(stid = oci_parse(stid=ocip​arse(conn, 'begin myproc_use_rc(:rc_in); end;');
oci_bind_by_name($stid, ':rc_in', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);?>`

Found A Problem?

bmichael at goldparrot dot com

22 years ago

`If your are using Oracle's OCI libraries, on any project, which PHP does, you can use this limit.

I have done network level testing on the effect of this parameter. It does improved efficiency. Big Time.

Oracle uses SQL*Net as the transport mechanism for data between your connection and the database. That is why you must setup Oracle properly.

This parameter tells SQLNET to Buffer more results. When SQLNET (at the server) gets a request for data, it bundles up X rows (1,2,3,1000, etc) for transport. It sends the appropriate SQLNET headers back to the client, Waits for an ACK then begins sending data in MTU sized chunks (ethernet is something like 1500 bytes and ATM's WANS are around 1000 bytes). The chunk size can also be tuned in SQLNET, but with much less improvements.

TCP/IP then takes the data across the wire, breaking it up into multiple TCP/IP packets.

Once the exchange is done, the SQLNET client sends an
ACK back to the SQLNET Listener (the Oracle Server) and the transaction is complete.

Each round trip, SQL*NET looks up inside the server memory (UGA - user global area) to find the query results. It then grabs the rows necessary to send. If it is one row, versus 1000 rows. The process is the same.

There is much I could tell you on how the Database itself reacts. If you can significantly lessen the amount of round trips you are making... WOW.

For more info on Oracle OCI go to http://otn.oracle.com

`