11g Result Cache – a missed opportunity?

One of the new features of 11g that initially caught my attention, (apart of course from versioning, that never actually made the cut into the production release) was the server side result cache. I thought this could really, really help repeated queries fly which on the database I was working on, a large number of queries were repeated. Recently, at the DBMS sig, Julian Dyke gave a presentation, based partly on this that reawakend my interest.

Sadly though this feature is NOT as useful as I first imagined, the problem with the result cache is stated plainly enough in the Oracle documentation “The cached results stored become invalid when data in the dependent database objects is modified”, naively I assumed the cached results would only be invalidated if the rows they relied upon actually changed. At the worse you could have it if the block that contains the rows that are part of your results set changes then invalidate the result cache. But no, the result cache of your query is invalidated if ANY row of the tables that you are querying changes. Clearly this makes the result cache not all that useful in an OLTP environment, to me this smacks of a missed opportunity, and I wonder if there is room for this feature to be enhanced in future releases.

SQL> set autotrace traceonly stat
SQL> select /*+ result_cache */ owner from t where object_id=33333;

Statistics
———————————————————-
1 recursive calls
0 db block gets
814 consistent gets
808 physical reads
0 redo size
419 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> /

Statistics
———————————————————-
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
419 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

I love the look of the 0 consistent gets, but you won’t be seeing that much in an OLTP environment until the granularity of what invalidates the result cache improves, so definitely a missed opportunity.

Advertisements

2 thoughts on “11g Result Cache – a missed opportunity?

  1. Build an on-commit MV for the query and base the result cache on the MV rather than the underlying tables.
    Personally, I think the fact that it is an EE-only feature is more of a drawback.

  2. Hi Gary,

    Thanks for the tip! But am I correct in thinking this relies on the query continually being the same? My problem is that I get in a particular day Million queries using one value (of bind variable), another million using a different value etc (around 30M in total per day). The next day the queries will be slightly different, though highly repetitive. And of course the base tables for these queries are changing, though the actual rows that the queries are returning are quite static.
    I hope i’m understanding your point, but I’m not all that familiar with MV’s

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s