Oracle 11g Result Caching and PHP OCI8

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 logic 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:


<?php

$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.

Comments:

Hi Chris, nice post. Just two questions: 1. Am I right to assume that the client query result cache is completely independent of the server result cache? Or do I have to use the latter in order to also benefit from the former? 2. In the paragraph before the last one...: "This shows that with OCI_DEFAULT the client query result cache is not used [...]" - Shouldn't that read "... withOUT OCI_DEFAULT..."? Regards, Manuel

Posted by Manuel Hossfeld on November 17, 2008 at 02:12 AM PST #

1. Correct, they are independent, but do share a query hint. 2. Thanks - I've corrected this.

Posted by Chris Jones on November 17, 2008 at 12:25 PM PST #

Could you perhabs provide some simple benchmark infos of your script? Print the time consumed by the for loop or something similar. And does it also work with PDO?

Posted by Sascha on December 03, 2008 at 07:36 AM PST #

It will work with PDO_OCI. The feature is implemented purely in Oracle's libraries. There is nothing specific in the OCI8 extension for it. The benchmark results are hard to show with such a simple example. There are two components (i) reduced time on the client (ii) reduced load on the DB server. I encourage you to test your own application.

Posted by Chris Jones on December 03, 2008 at 07:46 AM PST #

Nice post! Two (late) questions: assuming an apache prefork mode for php . is the client-side result cache a per-process thing, or will it be shared between the different php processes? . what if persistent connections are not in use: will the the client-side cache be filled in at every page request, just to be emptied at en of request, or is it automatically disabled? And, of course: does any if this change in a multithreaded php environment?

Posted by gggeek on August 28, 2010 at 11:17 PM PDT #

The CRC is per-process. It is still used for the life of the request with oci_connect() connections - this lets long running command-line scripts take advantage of the cache. With 11gR2 the cache can be controlled by the DBA without the application needing to modify the SQL to include the /*+ result_cache */ hint. See the "Table Annotations" section under http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/memory.htm#BGBBIACC

Posted by christopher.jones on August 30, 2010 at 07:55 AM PDT #

Post a Comment:
Comments are closed for this entry.
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
Book: Free PHP Oracle book
Download: PHP Linux RPMs with the OCI8 extension
Links: OTN PHP Developer Center

Search

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