Oracle Database 11g introduced "server-side"and "client-side" result caches.
The database cache is enabled with the RESULT_CACHE_MODE database parameter, which has several modes. With the default mode, queries for which you want results to be cached need a hint added:
$s = oci_parse($c, "select /*+ result_cache */ * from employee");
No PHP changes are required.
The client result cache (i.e. in PHP OCI8) is ideal for small queries from infrequently modified tables, such as lookup tables. It can reduce PHP statement processing time and significantly reduce database CPU usage, allowing any database to handle more PHP processes and users. The client-side cache is per PHP process.
A key feature of the cache is that Oracle automatically handles cache entry invalidation when a database change invalidates the stored results. Oracle will check the cache entries each time any round trip to the database occurs. If no round trip has happened with a configurable "lag" time, the cache is assumed stale.
The Oracle® Call Interface Programmer's Guide, 11g Release 1 (11.1) contains the best description of the feature and has more about when to use it and how to manage it.
To demonstrate client caching in PHP, the database parameter CLIENT_RESULT_CACHE_SIZE can be set to a non zero value and the Oracle 11g database restarted:
$ sqlplus / as sysdba
SQL> alter system set client_result_cache_size=64M scope=spfile;
SQL> startup force
In PHP, the key to using the client-cache is to pass OCI_DEFAULT to oci_execute() as shown in crc.php. The query hint is also needed:
$c = oci_pconnect('hr', 'hrpwd', '//localhost/orcl');
for ($i = 0; $i < 1000; $i++) {
$s = oci_parse($c,
"select /*+ result_cache */ * from employees where rownum < 2");
oci_execute($s, OCI_DEFAULT);
oci_fetch_all($s, $res);
}
?>
Before executing the PHP script, run this query in the SQL*Plus session:
SQL> select parse_calls, executions, sql_text
2 from v$sql
3 where sql_text like '%employees%';
PARSE_CALLS EXECUTIONS SQL_TEXT
----------- ---------- ---------------------------------------------------
1 1 select parse_calls, executions, sql_text from v$sql
where sql_text like '%employees%'
This shows the database being accessed when the query is executed. Initially it shows just the monitoring query itself.
In another terminal window, run crc.php from the command line or run it in a browser – it doesn't display any results.
$ php crc.php
Re-running the monitoring query shows that during the 1000 loop iterations, the database executed the PHP query just twice, once for the initial execution and the second time by a subsequent cache validation check:
PARSE_CALLS EXECUTIONS SQL_TEXT
----------- ---------- ---------------------------------------------------
2 2 select /*+ result_cache */ * from employees where
rownum < 2
2 2 select parse_calls, executions, sql_text from v$sql
where sql_text like '%employees%'
So, for 998 executions, the database wasn't involved. Instead, the client result cache was used for the query results.
Now edit crc.php and remove OCI_DEFAULT from the execute call:
oci_execute($s);
Re-run the script:
$ php crc.php
The monitoring query now shows the modified query was executed once per loop iteration:
PARSE_CALLS EXECUTIONS SQL_TEXT
----------- ---------- ---------------------------------------------------
4 1002 select /*+ result_cache */ * from employees where
rownum < 2
3 3 select parse_calls, executions, sql_text from v$sql
where sql_text like '%employees%'
This shows that without OCI_DEFAULT the client query result cache is not used and each iteration has to be processed in the database. The reason is that the implicit commit is treated as an event that possibly invalidates the cache. (I was testing with 11.1.0.6 I imagine this is something that could be considered for optimization in a future release.)
A dedicated view CLIENT_RESULT_CACHE_STATS$ is periodically updated with statistics on client caching. For short tests like this example where the process quickly runs and terminates, it may not give meaningful results and V$SQL can be more useful.
