Statement caching bug fix to improve performance of OCI8 extension

I need some feedback on a short PHP OCI8 extension statement caching bug fix. The diff is in Email me any comments. My address is in About box on the right.

As part of some testing we were doing, we found that PHP's OCI8 extension had a bug in the way it used Oracle's statement caching. The fix is already in CVS and will be generally available in PHP 5.2.4. After a short period for you to send me comments, Antony Dovgal will also build a new PECL snapshot of OCI8.

Statement caching is of most benefit to users of oci_pconnect() with a heavily loaded DB server.

In Oracle, when a SQL statement is first executed, the database does a "hard parse" on it. This scans the statement text, works out things like the execution path and security domain, and stores this information in a database cache. The next time the statement is executed, Oracle only needs to do a "soft parse" (like a hash for the cache lookup). Of course, this actually happens in reverse - only when the soft parse fails to find a cache match is the full, "hard" parse done. This is server side caching and happens automatically.

The Oracle client side statement caching used by PHP takes it further. It eliminates the need for the server to do the soft parse. It also removes the need to send the statement text from PHP to the database, reducing network traffic and CPU context switches.

In PHP's OCI8 extension, statement caching is enabled by default with a size of 20. The size can be changed in php.ini with oci8.statement_cache_size.

The cache is LRU.  If you take advantage of it, the guideline is that the cache size should be as large as the working set of statements executed by your application.


There is one thing to note here. In earlier versions, I think 8i and earlier, Oracle did a generic execution plan, since it was generated at prepare time. Since 9i the data included in the first execute for the placeholders is taken into account as well. This means that your execution plan will be optimized for the set of data in your very first execution. As a result if you have very much varying selectivity on your data you use on the same statement, you could run into performance problems. @Chris: Is there some control over the caching? Does Oracle have some magic to prevent the above scenario that I am not aware of?

Posted by Lukas on June 11, 2007 at 05:46 PM PDT #

I think you are referring to "bind peeking", a feature of the "hard parse" stage from 9i, which is useful for OLTP systems. There are various things, including using stored outlines to set the execution plan. The best execution plan can be determined during tuning, edited (if desired), and set in place. Outlines can be moved between databases. Hints are a related tool. Then there is a feature called SQL Profiling which increases the data available to the optimizer to improve its decisions. Also consider not binding values where the data is skewed or when the statement is used infrequently. This will cause the cached values not to be reused (and expired from the cache). To the specific question, there is no magic flag to disable or flush a cache entry. It has been requested so maybe we'll see it in a future version, but the tools above are more than useful in this situation. None of this relates to PHP use of client side statement cache, which will continue to offer performance benefits.

Posted by Christopher Jones on June 13, 2007 at 02:50 PM PDT #

Post a Comment:
Comments are closed for this entry.

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Links: OTN Node.js Developer Center
OTN PHP Developer Center
Book: Free PHP Oracle book


« February 2015