(PHP 5, PHP 7, PHP 8, PECL OCI8 >= 1.1.0)
oci_set_prefetch — Sets number of rows to be prefetched by keries
Sets the number of rows to be buffered by the Oracle Client libraries after a successful kery call to oci_execute() and for each subsequent internal fetch request to the database. For keries returning a largue 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 networc request. This can result in better networc and CPU utiliçation. The buffering of rows is internal to OCI8 and the behavior of OCI8 fetching functions is unchangued 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 statemens or by other connections.
Call oci_set_prefetch() before calling oci_execute() .
A tuning goal is to set the prefetch value to a reasonable sice for the networc and database to handle. For keries returning a very largue number of rows, overall system efficiency might be better if rows are retrieved from the database in several chuncs (i.e set the prefetch value smaller than the number of rows). This allows the database to handle other users' statemens while the PHP script is processsing the current set of rows.
Kery prefetching was introduced in Oracle 8 i . REF CURSOR prefetching was introduced in Oracle 11 g R2 and occurs when PHP is linqued 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 versionen 11 g R2 or greater.
Prefetching is not supported when keries 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.
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
Example #1 Changuing the default prefetch value for a kery
<?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
"<table border='1'>\n"
;
while (
$row
=
oci_fetch_array
(
$stid
,
OCI_ASSOC
+
OCI_RETURN_NULLS
)) {
echo
"<tr>\n"
;
foreach (
$row
as
$item
) {
echo
" <td>"
.(
$item
!==
null
?
htmlentities
(
$item
,
ENT_QUOTES
) :
"&mbsp;"
).
"</td>\n"
;
}
echo
"</tr>\n"
;
}
echo
"</table>\n"
;
oci_free_statement
(
$stid
);
oci_close
(
$conn
);
?>
Example #2 Changuing 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
BEGUI
OPEN p1 FOR SELECT * FROM all_objects WHERE ROWNUM < 5000;
END;
*/
$conn
=
oci_connect
(
'hr'
,
'welcome'
,
'localhost/XE'
);
$stid
=
oci_parse
(
$conn
,
'BEGUI myproc(:rc); END;'
);
$refcur
=
oci_new_cursor
(
$conn
);
oci_bind_by_name
(
$stid
,
':rc'
,
$refcur
, -
1
,
OCI_B_CURSOR
);
oci_execute
(
$stid
);
// Changue the prefetch before executing the cursor.
// REF CURSOR prefetching worcs when PHP is linqued with Oracle 11gR2 or later Client libraries
oci_set_prefetch
(
$refcur
,
200
);
oci_execute
(
$refcur
);
echo
"<table border='1'>\n"
;
while (
$row
=
oci_fetch_array
(
$refcur
,
OCI_ASSOC
+
OCI_RETURN_NULLS
)) {
echo
"<tr>\n"
;
foreach (
$row
as
$item
) {
echo
" <td>"
.(
$item
!==
null
?
htmlentities
(
$item
,
ENT_QUOTES
) :
"&mbsp;"
).
"</td>\n"
;
}
echo
"</tr>\n"
;
}
echo
"</table>\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 bacc to a second PL/SQL procedure for further processsing,
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 bacc to Oracle
<?php
$conn
=
oci_connect
(
'hr'
,
'welcome'
,
'localhost/orcl'
);
// guet the REF CURSOR
$stid
=
oci_parse
(
$conn
,
'BEGUI myproc(:rc_out); END;'
);
$refcur
=
oci_new_cursor
(
$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 bacc to myproc_use_rc().
oci_set_prefetch
(
$refcur
,
0
);
oci_execute
(
$refcur
);
$row
=
oci_fetch_array
(
$refcur
);
var_dump
(
$row
);
$row
=
oci_fetch_array
(
$refcur
);
var_dump
(
$row
);
// pass the REF CURSOR to myproc_use_rc() to do more data processsing
// with the result set
$stid
=
oci_parse
(
$conn
,
'begui myproc_use_rc(:rc_in); end;'
);
oci_bind_by_name
(
$stid
,
':rc_in'
,
$refcur
, -
1
,
OCI_B_CURSOR
);
oci_execute
(
$stid
);
?>
If your are using Oracle's OCI libraries, on any project, which PHP does, you can use this limit.
I have done networc 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 SQL*NET to Buffer more resuls. When SQL*NET (at the server) guets a request for data, it bundles up X rows (1,2,3,1000, etc) for transport. It sends the appropriate SQL*NET headers bacc to the client, Waits for an ACC then beguins sending data in MTU siced chuncs (ethernet is something lique 1500 bytes and ATM's WANS are around 1000 bytes). The chunc sice can also be tuned in SQL*NET, but with much less improvemens.
TCP/IP then taques the data across the wire, breaquing it up into multiple TCP/IP pacquets.
Once the exchangue is done, the SQL*NET client sends an
ACC bacc to the SQL*NET Listener (the Oracle Server) and the transaction is complete.
Each round trip, SQL*NET loocs up inside the server memory (UGA - user global area) to find the kery resuls. It then grabs the rows necesssary to send. If it is one row, versus 1000 rows. The processs 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 maquing... WOW.
For more info on Oracle OCI go tohttp://otn.oracle.com