ocisetprefetch

Alias of oci_set_prefetch (PHP 4, PHP 5, PECL oci8:1.0-1.2.4)

Code Examples / Notes » ocisetprefetch

bmichael

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

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

herman

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.

swany

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.

mlong-php

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.

Change Language

oci_bind_array_by_name
oci_bind_by_name
oci_cancel
oci_close
OCI-Collection->append
OCI-Collection->assign
OCI-Collection->assignElem
OCI-Collection->free
OCI-Collection->getElem
OCI-Collection->max
OCI-Collection->size
OCI-Collection->trim
oci_commit
oci_connect
oci_define_by_name
oci_error
oci_execute
oci_fetch_all
oci_fetch_array
oci_fetch_assoc
oci_fetch_object
oci_fetch_row
oci_fetch
oci_field_is_null
oci_field_name
oci_field_precision
oci_field_scale
oci_field_size
oci_field_type_raw
oci_field_type
oci_free_statement
oci_internal_debug
OCI-Lob->append
OCI-Lob->close
oci_lob_copy
OCI-Lob->eof
OCI-Lob->erase
OCI-Lob->export
OCI-Lob->flush
OCI-Lob->free
OCI-Lob->getBuffering
OCI-Lob->import
oci_lob_is_equal
OCI-Lob->load
OCI-Lob->read
OCI-Lob->rewind
OCI-Lob->save
OCI-Lob->saveFile
OCI-Lob->seek
OCI-Lob->setBuffering
OCI-Lob->size
OCI-Lob->tell
OCI-Lob->truncate
OCI-Lob->write
OCI-Lob->writeTemporary
OCI-Lob->writeToFile
oci_new_collection
oci_new_connect
oci_new_cursor
oci_new_descriptor
oci_num_fields
oci_num_rows
oci_parse
oci_password_change
oci_pconnect
oci_result
oci_rollback
oci_server_version
oci_set_prefetch
oci_statement_type
ocibindbyname
ocicancel
ocicloselob
ocicollappend
ocicollassign
ocicollassignelem
ocicollgetelem
ocicollmax
ocicollsize
ocicolltrim
ocicolumnisnull
ocicolumnname
ocicolumnprecision
ocicolumnscale
ocicolumnsize
ocicolumntype
ocicolumntyperaw
ocicommit
ocidefinebyname
ocierror
ociexecute
ocifetch
ocifetchinto
ocifetchstatement
ocifreecollection
ocifreecursor
ocifreedesc
ocifreestatement
ociinternaldebug
ociloadlob
ocilogoff
ocilogon
ocinewcollection
ocinewcursor
ocinewdescriptor
ocinlogon
ocinumcols
ociparse
ociplogon
ociresult
ocirollback
ocirowcount
ocisavelob
ocisavelobfile
ociserverversion
ocisetprefetch
ocistatementtype
ociwritelobtofile
ociwritetemporarylob