Friday Mar 14, 2014

Performance improvement for OCI_RETURN_LOBS in PHP OCI8 2.0.8

Reducing "round trips" to the database server is important for performance and ultimately for system scalability. A server round-trip is defined as the trip from PHP to the database server and back to PHP.

Reducing round trips makes everything more efficient: PHP is faster, there is less network overhead, the database doesn't have to do a context switch or do any processing for you. Applications have some control over round trips, for example by effectively using prefetching or by using an appropriate execute mode to minimize unneccessary rollbacks at the end of a script.

The bug filer of Bug 66875 noticed that PHP OCI8's implementation itself could reduce round trips if a particular LOB column meta data value was cached for the duration of a query instead of being re-requested for each row of the query result set.

So, now with OCI8 2.0.8, you should see a performance increase if you are doing multi-row queries involving LOB columns returned as OCI_RETURN_LOBS:

$s = oci_parse($c, "select mylobcol from mylobtab");
oci_execute($s);
while (($row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_LOBS)) !== false) {
    echo $row['MYLOBCOL'], "\n";
}

The bug filer tested the patch and added his performance improvement benchmark results to the bug report. The benefit in your environment will vary greatly with the network setup, schema, and how many LOB columns are queried. Test it out and let me know how the new version helps you.

There is no immediate change for LOBs fetched with OCI-Lob::read() and OCI-Lob::load(). This would require a more complex patch than I want to apply at this time. Queries that don't use LOBs are not affected in any way by the patch.

OCI8 2.0 is included in the forthcoming PHP 5.6 code base. For PHP 5.2 to PHP 5.5 you can install it from PECL. PHP 5.5 RPMs with PHP OCI8 2.0.8 are available from oss.oracle.com.

Finally, if your LOB queries return multiple rows, you might also like this tip to reduce PHP memory usage.

Friday Jul 10, 2009

Inserting and Updating Oracle XMLType columns in PHP

[Read More]
About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Book: Free PHP Oracle book
Download: PHP Linux RPMs with the OCI8 extension
Links: OTN PHP Developer Center

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today