ocisetprefetch

add a note User Contributed Notes

Herman
20-Jul-2004 10: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
05-Jan-2003 07: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
31-Aug-2002 06: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 11:32


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 11: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.

add a note

ocisetprefetch

说明