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 http://news.php.net/php.cvs/44754. Email me any comments. My address is under the About link 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.
