By Franck Pachot

.
The result cache is very nice when base tables are static: get the result without any buffer get, without any join or sorts. But as soon as any DML occurs on one of the tables the result is based on, then result cache is invalidated. And cache misses are quite expensive. You can think of it as a materialized view in memory. But with materialized views, you can accept to see stale results in order to avoid frequent refreshes. Let’s see how we can control stale results with undocumented options.

Test case

I’m creating a basic table.

22:30:44 SQL> create table DEMO as select rownum id from xmltable('1 to 100000');
Table created.
 
22:30:44 SQL> exec if dbms_result_cache.flush then dbms_output.put_line('Flushed.'); end if;
PL/SQL procedure successfully completed.

RESULT_CACHE

On that table, I’ll do a simple select with the RESULT_CACHE hint.

22:30:44 SQL> set autotrace on
22:30:44 SQL> select /*+ result_cache */ count(*) from DEMO;
 
  COUNT(*)
----------
    100000
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2180342005
 
------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |    46   (0)| 00:00:01 |
|   1 |  RESULT CACHE       | 2x5f91pfn5p6882f6szxj50jwf |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| DEMO                       |   100K|    46   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 - column-count=1; dependencies=(DEMO.DEMO); attributes=(single-row); name="select /*+ result_cache */ count(*) from DEMO"
 
Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
        159  consistent gets
        153  physical reads
          0  redo size
        358  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

We have read the 150 blocks of the DEMO table. The result cache has been loaded. Here are the objects and dependency:

22:30:44 SQL> set autotrace off
22:30:44 SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO     CON_ID
---------- ---------- ---------- ----------
         1          0     112463          0
 
22:30:44 SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id;
 
 ID TYPE       STATUS    NAME                           CACHE_ID                     INV
--- ---------- --------- ------------------------------ --------------------------- ----
  0 Dependency Published DEMO.DEMO                      DEMO.DEMO                      0
  1 Result     Published select /*+ result_cache */ cou 2x5f91pfn5p6882f6szxj50jwf     0

We can read it as: the query result (id=1) depends on the table (id=0).

If I run it another time:

22:30:45 SQL> select /*+ result_cache */ count(*) from DEMO;
 
  COUNT(*)
----------
    100000
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2180342005
 
------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |    46   (0)| 00:00:01 |
|   1 |  RESULT CACHE       | 2x5f91pfn5p6882f6szxj50jwf |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| DEMO                       |   100K|    46   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 - column-count=1; dependencies=(DEMO.DEMO); attributes=(single-row); name="select /*+ result_cache */ count(*) from DEMO"
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        358  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

No block get at all. The result was in cache.

dependencies

If we do any kind of DML on the tables the result cache depends on:

22:30:45 SQL> delete from DEMO where null is not null;
0 rows deleted.
 
22:30:45 SQL> commit;
Commit complete.

Then the cache is invalidated:

22:30:45 SQL> select * from v$result_cache_dependency;
no rows selected
 
22:30:45 SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id;
 
 ID TYPE       STATUS    NAME                           CACHE_ID                     INV
--- ---------- --------- ------------------------------ --------------------------- ----
  0 Dependency Published DEMO.DEMO                      DEMO.DEMO                      1
  1 Result     Invalid   select /*+ result_cache */ cou 2x5f91pfn5p6882f6szxj50jwf     0

and another run will have to load the cache again:

22:30:45 SQL> set autotrace on
22:30:45 SQL> select /*+ result_cache */ count(*) from DEMO;
 
  COUNT(*)
----------
    100000
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2180342005
 
------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |    46   (0)| 00:00:01 |
|   1 |  RESULT CACHE       | 2x5f91pfn5p6882f6szxj50jwf |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| DEMO                       |   100K|    46   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 - column-count=1; dependencies=(DEMO.DEMO); attributes=(single-row); name="select /*+ result_cache */ count(*) from DEMO"
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        157  consistent gets
          0  physical reads
          0  redo size
        358  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
22:30:46 SQL> set autotrace off
22:30:46 SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO     CON_ID
---------- ---------- ---------- ----------
         2          0     112463          0
 
22:30:46 SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id;
 
 ID TYPE       STATUS    NAME                           CACHE_ID                     INV
--- ---------- --------- ------------------------------ --------------------------- ----
  0 Dependency Published DEMO.DEMO                      DEMO.DEMO                      1
  1 Result     Invalid   select /*+ result_cache */ cou 2x5f91pfn5p6882f6szxj50jwf     0
  2 Result     Published select /*+ result_cache */ cou 2x5f91pfn5p6882f6szxj50jwf     0

This is the only documented behaviour: the cache is invalidated if and only if there has been some DML on the tables it depends on.

RESULT_CACHE(SHELFLIFE=seconds)

I flush the cache and run the same with the undocumented result cache hint option: SHELFLIFE=10 seconds:

22:30:46 SQL> exec if dbms_result_cache.flush then dbms_output.put_line('Flushed.'); end if;
PL/SQL procedure successfully completed.
 
22:30:46 SQL> set autotrace on
22:30:46 SQL> select /*+ result_cache(shelflife=10) */ count(*) from DEMO;
 
  COUNT(*)
----------
    100000
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2180342005
 
------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |    46   (0)| 00:00:01 |
|   1 |  RESULT CACHE       | 2x5f91pfn5p6882f6szxj50jwf |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| DEMO                       |   100K|    46   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 - column-count=1; dependencies=(DEMO.DEMO); attributes=(single-row, shelflife=10); name="select /*+ result_cache(shelflife=10) */ count(*) from DEMO"

we see the ‘shelflife’ attribute but the dependency is the same as without the option:

22:30:46 SQL> set autotrace off
22:30:46 SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO     CON_ID
---------- ---------- ---------- ----------
         1          0     112463          0
 
22:30:46 SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id;
 
 ID TYPE       STATUS    NAME                           CACHE_ID                     INV
--- ---------- --------- ------------------------------ --------------------------- ----
  0 Dependency Published DEMO.DEMO                      DEMO.DEMO                      0
  1 Result     Published select /*+ result_cache(shelfl 2x5f91pfn5p6882f6szxj50jwf     0

I run the query again 5 seconds later:

22:30:51 SQL> set autotrace on
22:30:51 SQL> select /*+ result_cache(shelflife=10) */ count(*) from DEMO;
 
  COUNT(*)
----------
    100000
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2180342005
 
------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |    46   (0)| 00:00:01 |
|   1 |  RESULT CACHE       | 2x5f91pfn5p6882f6szxj50jwf |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| DEMO                       |   100K|    46   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 - column-count=1; dependencies=(DEMO.DEMO); attributes=(single-row, shelflife=10); name="select /*+ result_cache(shelflife=10) */ count(*) from DEMO"
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        358  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
22:30:51 SQL> set autotrace off
22:30:51 SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO     CON_ID
---------- ---------- ---------- ----------
         1          0     112463          0
 
22:30:51 SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id;
 
 ID TYPE       STATUS    NAME                           CACHE_ID                     INV
--- ---------- --------- ------------------------------ --------------------------- ----
  0 Dependency Published DEMO.DEMO                      DEMO.DEMO                      0
  1 Result     Published select /*+ result_cache(shelfl 2x5f91pfn5p6882f6szxj50jwf     0

Nothing special here. No DML occured so the result is still valid (‘published’)

expiration

But let’s wait 5 more seconds and run it again:

22:30:56 SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO     CON_ID
---------- ---------- ---------- ----------
         1          0     112463          0
 
22:30:56 SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id;
 
 ID TYPE       STATUS    NAME                           CACHE_ID                     INV
--- ---------- --------- ------------------------------ --------------------------- ----
  0 Dependency Published DEMO.DEMO                      DEMO.DEMO                      0
  1 Result     Expired   select /*+ result_cache(shelfl 2x5f91pfn5p6882f6szxj50jwf     0

The status has changed, it’s now EXPIRED because the 10 seconds shelflife has passed on since the cache was loaded.

22:30:56 SQL>
22:30:56 SQL> set autotrace on
22:30:56 SQL> select /*+ result_cache(shelflife=10) */ count(*) from DEMO;
 
  COUNT(*)
----------
    100000
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2180342005
 
------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |    46   (0)| 00:00:01 |
|   1 |  RESULT CACHE       | 2x5f91pfn5p6882f6szxj50jwf |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| DEMO                       |   100K|    46   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 - column-count=1; dependencies=(DEMO.DEMO); attributes=(single-row, shelflife=10); name="select /*+ result_cache(shelflife=10) */ count(*) from DEMO"
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        157  consistent gets
          0  physical reads
          0  redo size
        358  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

here I have a cache miss even if no DML occured on the tables it depends on.

22:30:56 SQL> select * from v$result_cache_dependency;
 
 RESULT_ID  DEPEND_ID  OBJECT_NO     CON_ID
---------- ---------- ---------- ----------
         2          0     112463          0
 
22:30:56 SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id;
 
 ID TYPE       STATUS    NAME                           CACHE_ID                     INV
--- ---------- --------- ------------------------------ --------------------------- ----
  0 Dependency Published DEMO.DEMO                      DEMO.DEMO                      0
  1 Result     Invalid   select /*+ result_cache(shelfl 2x5f91pfn5p6882f6szxj50jwf     0
  2 Result     Published select /*+ result_cache(shelfl 2x5f91pfn5p6882f6szxj50jwf     0

When I run a query using an expired result cache, that cache becomes invalid, the query is fully run (150 block gets) and new result cache populated.

Note that in addition to the shelflife expiration, the dependency on DML is still invalidating the result cache:

22:30:56 SQL> delete from DEMO where null is not null;
0 rows deleted.
 
22:30:56 SQL> commit;
Commit complete.
 
22:30:56 SQL> select * from v$result_cache_dependency;
no rows selected
 
22:30:56 SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id;
 
 ID TYPE       STATUS    NAME                           CACHE_ID                     INV
--- ---------- --------- ------------------------------ --------------------------- ----
  0 Dependency Published DEMO.DEMO                      DEMO.DEMO                      1
  1 Result     Invalid   select /*+ result_cache(shelfl 2x5f91pfn5p6882f6szxj50jwf     0
  2 Result     Invalid   select /*+ result_cache(shelfl 2x5f91pfn5p6882f6szxj50jwf     0

So with SHELFLIFE we have two reasons for invalidations: dependency and expiration.

RESULT_CACHE(SNAPSHOT=seconds)

There is another undocumented option for the result cache hint: SNAPSHOT which also taked a number of seconds. Let’s try it:

22:30:56 SQL> exec if dbms_result_cache.flush then dbms_output.put_line('Flushed.'); end if;
PL/SQL procedure successfully completed.
 
22:30:56 SQL> set autotrace on
22:30:56 SQL> select /*+ result_cache(snapshot=10) */ count(*) from DEMO;
 
  COUNT(*)
----------
    100000
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2180342005
 
------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |    46   (0)| 00:00:01 |
|   1 |  RESULT CACHE       | 2x5f91pfn5p6882f6szxj50jwf |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| DEMO                       |   100K|    46   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 - column-count=1; attributes=(single-row, snapshot=10); name="select /*+ result_cache(snapshot=10) */ count(*) from DEMO"
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        157  consistent gets
          0  physical reads
          0  redo size
        358  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
22:30:56 SQL> set autotrace off
22:30:56 SQL> select * from v$result_cache_dependency;
no rows selected
 
22:30:56 SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id;
 
 ID TYPE       STATUS    NAME                           CACHE_ID                     INV
--- ---------- --------- ------------------------------ --------------------------- ----
  0 Result     Published select /*+ result_cache(snapsh 2x5f91pfn5p6882f6szxj50jwf     0

The cache has been populated here, but there is no dependency. Let’s try DML on base table:

22:30:56 SQL> delete from DEMO where null is not null;
0 rows deleted.
 
22:30:56 SQL> commit;
Commit complete.
 
22:30:56 SQL> select * from v$result_cache_dependency;
no rows selected
 
22:30:56 SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id;
 
 ID TYPE       STATUS    NAME                           CACHE_ID                     INV
--- ---------- --------- ------------------------------ --------------------------- ----
  0 Result     Published select /*+ result_cache(snapsh 2x5f91pfn5p6882f6szxj50jwf     0

A SNAPSHOT result cache is not invalidated by DML on base tables.

But if we wait 10 seconds:

22:31:06 SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id;
 
 ID TYPE       STATUS    NAME                           CACHE_ID                     INV
--- ---------- --------- ------------------------------ --------------------------- ----
  0 Result     Expired   select /*+ result_cache(snapsh 2x5f91pfn5p6882f6szxj50jwf     0

The status is expired, as with the shelflife. Next execution will invalidate it.

Conclusion

There are two reasons to invalidate a result cache: DML on dependency, or expiration after a number of seconds. And we can use any combination of them with undocumented hints. Oracle itself uses them internally. SNAPSHOT is used by Active Dynamic Sampling: stale result are acceptable for one hour. SHELFLIFE is used on dictionary views bases on X$ tables where dependency cannot be tracked.

I hope it will get documented in future releases. There are some cases where we can accept stale results in order to get better performance. We already do that with materialized views, so why not do it with result cache?