More on PHP and Oracle 11gR2 Improvements to Client Result Caching
- by christopher.jones
Oracle 11.2 brought several improvements to Client Result Caching. CRC is way for the results of queries to be cached in the database client process for reuse. In an Oracle OpenWorld presentation "Best Practices for Developing Performant Application" my colleague Luxi Chidambaran had a (non-PHP generated) graph for the Niles benchmark that shows a DB CPU reduction up to 600% and response times up to 22% faster when using CRC.
Sometimes CRC is called the "Consistent Client Cache" because Oracle automatically invalidates the cache if table data is changed. This makes it easy to use without needing application logic rewrites. There are a few simple database settings to turn on and tune CRC, so management is also easy.
PHP OCI8 as a "client" of the database can use CRC. The cache is per-process, so plan carefully before caching large data sets. Tables that are candidates for caching are look-up tables where the network transfer cost dominates.
CRC is really easy in 11.2 - I'll get to that in a moment. It was also pretty easy in Oracle 11.1 but it needed some tiny application changes. In PHP it was used like:
$s = oci_parse($c, "select /*+ result_cache */ * from employees");
oci_execute($s, OCI_NO_AUTO_COMMIT); // Use OCI_DEFAULT in OCI8 <= 1.3
oci_fetch_all($s, $res);
I blogged about this in the past. The query had to include a specific hint that you wanted the results cached, and you needed to turn off auto committing during execution either with the OCI_DEFAULT flag or its new, better-named alias OCI_NO_AUTO_COMMIT. The no-commit flag rule didn't seem reasonable to me because most people wouldn't be specific about the commit state for a query.
Now in Oracle 11.2, DBAs can now nominate tables for caching, either with CREATE TABLE or ALTER TABLE. That means you don't need the query hint anymore. As well, the no-commit flag requirement has been lifted. Your code can now look like:
$s = oci_parse($c, "select * from employees");
oci_execute($s);
oci_fetch_all($s, $res);
Since your code probably already looks like this, your DBA can find the top queries in the database and simply tune the system by turning on CRC in the database and issuing an ALTER TABLE statement for candidate tables. Voila.
Another CRC improvement in Oracle 11.2 is that it works with DRCP connection pooling.
There is some fine print about what is and isn't cached, check the Oracle manuals for details. If you're using 11.1 or non-DRCP "dedicated servers" then make sure you use oci_pconnect() persistent connections. Also in PHP don't bind strings in the query, although binding as SQLT_INT is OK.
