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.

Monday Mar 03, 2014

The Oracle Database Access Group is hiring

Over the years I've worked very closely with the Oracle Database Access Group and have a lot of respect for them. I'm happy to share that they are hiring C developers.

To apply, go to irecruitment.oracle.com and enter the code IRC2401606 or IRC2403582 (depending where you want to work) in the Keyword search box.

Here is an excerpt from the job posting:

The Database Access group at Oracle is responsible for providing functionally comprehensive, reliable, high performance, secure and highly available access to the Oracle Database from various client drivers, including proprietary, standards-based and open-source drivers. The group works on the high level drivers, the Oracle Call Interface (OCI) layer, the Oracle wire protocol (TTC) and highly scalable server side protocol handlers that together connect an application written in any language securely to the Oracle Database Server to provide full featured access to the Oracle Database.

Some of the listed requirements are:

  • Strong C programming experience.

  • Knowledge and experience with latest application development technologies including open source and web technologies (e.g. PHP, Ruby, Python, Node.js, HTML5, JavaScript)

I believe it when they also say "Work is non-routine and very complex, involving the application of advanced technical/business skills in area of specialization". Check out the postings for all the details. I look forward to working with you.

Addendum: IRC2401606 is also available.

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
Links: OTN Node.js Developer Center
OTN PHP Developer Center
Book: Free PHP Oracle book

Search

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