oci_set_module_name
With Oracle 11gR2, prefetching also works when fetching from REF
CURSORS and nested queries.
To use a non default prefetch count, use oci_set_prefetch. For
example, on the REF CURSOR statement handle:
<?php
$stid = oci_parse($c, "call myproc(:rc)");
$refcur = oci_new_cursor($c);
oci_bind_by_name($stid, ':rc', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);
oci_set_prefetch($refcur, 200);
oci_execute($refcur);
oci_fetch_all($refcur, $res);
var_dump($res);
?>
REF CURSOR prefetching can be used when PHP is linked with Oracle
11gR2 libraries and connected to previous database versions of the
database.
Nested cursor prefetching requires both the Oracle client libraries
linked with PHP and the database to be Oracle Database 11gR2
From PHP 5.3.2 (PHP OCI8 >= 1.4) the prefetch value may be set to 0.
Previously the lower bound was 1.
If PHP OCI8 fetches from a REF CURSOR and then passes the REF CURSOR
back to a second PL/SQL procedure for further processing, set the REF
CURSOR prefetch count to 0 to avoid rows being "lost" from the result
set.
<?php
// get the REF CURSOR
$stid = oci_parse($c, "call myproc_get_rc(:rc_out)");
$refcur = oci_new_cursor($c);
oci_bind_by_name($stid, ':rc_out', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);
// Fetch one row and display it
oci_set_prefetch($refcur, 0); // don't prefetch rows that aren't displayed
oci_execute($refcur);
$row = oci_fetch_array($refcur);
var_dump($res);
// pass the REF CURSOR to myproc_use_rc() to do more data processing
$stid = oci_parse($c, "call myproc_use_rc(:rc_in)");
oci_bind_by_name($stid, ':rc_in', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);
// ...
?>
oci_set_prefetch
(PHP 5, PECL oci8 >= 1.1.0)
oci_set_prefetch — Sets number of rows to be prefetched
Descrierea
bool oci_set_prefetch
( resource $statement
, int $rows
)
Sets the number of rows to be prefetched after successful call to oci_execute().
Parametri
- statement
-
A valid OCI statement identifier.
- rows
-
The number of rows to be prefetched.
Valorile întroarse
Întoarce valoarea TRUE în cazul succesului sau FALSE în cazul eşecului.
Note
Notă: In PHP versions before 5.0.0 you must use ocisetprefetch() instead. This name still can be used, it was left as alias of oci_set_prefetch() for downwards compatability. This, however, is deprecated and not recommended.
add a note
User Contributed Notesoci_set_prefetch
sixd at php dot net
07-Oct-2009 01:13
07-Oct-2009 01:13
Herman
20-Jul-2004 02:01
20-Jul-2004 02:01
I noticed an amazing improvement when setting up this parameter. I was querying 300 rows, which took 3 seconds to loop through the fetches.
When I set the prefetch to 500 I could do 5000 rows in 0.7 seconds.
SQL Net protocol is horribly inefficient doing a request/confirm for each line if you don't use this.
bmichael at goldparrot dot com
04-Jan-2003 11:00
04-Jan-2003 11:00
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 SQL*NET to Buffer more results. When SQL*NET (at the server) gets a request for data, it bundles up X rows (1,2,3,1000, etc) for transport. It sends the appropriate SQL*NET 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 SQL*NET, 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 SQL*NET client sends an
ACK back to the SQL*NET 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
mlong-php at mlong dot us
30-Aug-2002 10:09
30-Aug-2002 10:09
After playing around with this function more it really does make a difference. Use it after the parse and before the execute. By setting this to 5000 for a 10000 line query it took only 1/3 of the time to complete than without it. You may want to try different values to see what works best for your query.
mlong-php at mlong dot us
27-Aug-2002 03:32
27-Aug-2002 03:32
[Editor's note: if you set it to 2, PHP fetches 3 rows because the prefetch count is the number of extra rows fetched]
If you set this to 2, then PHP would fetch 2 rows from the database at a time and only need to make half as many calls to the database. If you set it to 3 then 1/3 as many calls, etc. Basically this tells how many rows to retrieve at a time from the server (they will be cached so you'll still only get one at a time in your program). The drawback would be as one person already stated...the higher the number the more memory will be used. You would call this right after your parse, and before any fetch or execute. In practice this should improve performance but I haven't noticed any difference regardless of what values I put in though I am using a SQLNet interface to RDB so that may be it (for example, prefetch will not work with Oracle 7). You may have better luck than I. If you want further info try searching in google for OCI_ATTR_PREFETCH_ROWS
swany at hour13 dot com
20-Jun-2002 03:24
20-Jun-2002 03:24
By default the OCI fetch calls retrieve one row at a time from the database. This is fine for small result sets, but you incur A LOT of sql*net/database overhead if you are returning a large result set.
The solution is to use OCISetPrefetch to grab more than one row at a time from the database into the internal PHP buffer that holds the rows. The fetches that you perform against the database will still return one row, just as they did before. You don't have to code any differently. The next time you do a fetch, PHP doesn't have to ask the database for it, it is simply retrieved from the local buffer.
Your goal is to set this value to approximately the same size as your average result set. Setting it too high results in a waste of memory, and setting it to low results in too much database activity.
By setting this value properly you can achieve dramatic speedups on data rich pages. Experiment with the values a bit and I am sure you will find the call to be one of the most important OCI functions you will use.