result_cache invalidated within the same transaction

I just stumbled across one sentence about Server Result Cache in Christian Antogninis Troubleshooting Oracle Performance: " ... To guarantee the consistency of the results ... every time that something changes on the objects reference by a query, the cache entries dependent on it are invalidated ..." (p484).

This made me a little bit curious, as other caches can deal with different versions of cached objects.

so here are some tests:

(2 connections to the same instance, no RAC, 11.1.0.6):

Default isolation level:

Session 1:

preparations:

alter session set timed_statistics = true;
   alter session set statistics_level=all;
   alter session set "_rowsource_execution_statistics" = true;
   set serveroutput on
   BEGIN
     IF dbms_result_cache.flush 
  THEN 
    dbms_output.put_line('Flush Successful');
  ELSE 
    dbms_output.put_line('Flush Failure');
  END IF;
  END;
  /
  set serveroutput off
  drop table dob;
  create table dob as select * from dba_objects;  

test:

select /*+ result_cache */ max(OBJECT_ID) from dob;    
 MAX(OBJECT_ID)
 --------------
 74959
 
 SQL_ID  36tj9136q6pxx, child number 1
 -------------------------------------
 select /*+ result_cache */ max(OBJECT_ID) from dob
 Plan hash value: 648181113
 ---------------------------------------------------------------------------------------------------------------------
 | Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
 ---------------------------------------------------------------------------------------------------------------------
 |   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.12 |     976 |    209 |
 |   2 |   SORT AGGREGATE    |                            |      1 |      1 |      1 |00:00:00.12 |     976 |    209 |
 |   3 |    TABLE ACCESS FULL| DOB                        |      1 |  69871 |  65761 |00:00:00.07 |     976 |    209 |
 ---------------------------------------------------------------------------------------------------------------------
 
 select /*+ result_cache */ max(OBJECT_ID) from dob;
 MAX(OBJECT_ID)
 --------------
 74959
 
 SQL_ID 36tj9136q6pxx, child number 1
 -------------------------------------
 select /*+ result_cache */ max(OBJECT_ID) from dob
 Plan hash value: 648181113
 --------------------------------------------------------------------------------------------------
 | Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   |
 --------------------------------------------------------------------------------------------------
 |   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.01 |
 |   2 |   SORT AGGREGATE    |                            |      0 |      1 |      0 |00:00:00.01 |
 |   3 |    TABLE ACCESS FULL| DOB                        |      0 |  69871 |      0 |00:00:00.01 |
 --------------------------------------------------------------------------------------------------

Session 2:

update dob set object_id=74999 where object_id=74959;
1 row updated.

Session 1:

select /*+ result_cache */ max(OBJECT_ID) from dob;
MAX(OBJECT_ID)
--------------
74959

SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob
Plan hash value: 648181113
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.01 |
|   2 |   SORT AGGREGATE    |                            |      0 |      1 |      0 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| DOB                        |      0 |  69871 |      0 |00:00:00.01 |
-------------------------------------------------------------------------------------------------- 

Session 2:

commit;
Commit complete.

Session 1:

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

MAX(OBJECT_ID)
--------------
74999

SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob

Plan hash value: 648181113

------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.06 |     976 |
|   2 |   SORT AGGREGATE    |                            |      1 |      1 |      1 |00:00:00.06 |     976 |
|   3 |    TABLE ACCESS FULL| DOB                        |      1 |  69871 |  65761 |00:00:00.01 |     976 |
------------------------------------------------------------------------------------------------------------

as expected - so far.

SET TRANSACTION ISOLATION LEVEL serializable;

flush result cache

Session 1:

select /*+ result_cache */ max(OBJECT_ID) from dob;

MAX(OBJECT_ID)
--------------
75003

SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob

Plan hash value: 648181113

------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.06 |     976 |
|   2 |   SORT AGGREGATE    |                            |      1 |      1 |      1 |00:00:00.06 |     976 |
|   3 |    TABLE ACCESS FULL| DOB                        |      1 |  69871 |  65761 |00:00:00.01 |     976 |
------------------------------------------------------------------------------------------------------------

select /*+ result_cache */ max(OBJECT_ID) from dob;

MAX(OBJECT_ID)
--------------
75003

SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob

Plan hash value: 648181113

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.01 |
|   2 |   SORT AGGREGATE    |                            |      0 |      1 |      0 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| DOB                        |      0 |  69871 |      0 |00:00:00.01 |
--------------------------------------------------------------------------------------------------

Session 2:

update dob set object_id=75004 where object_id=75003;

Session 1:

select /*+ result_cache */ max(OBJECT_ID) from dob;

MAX(OBJECT_ID)
--------------
75003

SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob

Plan hash value: 648181113

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.01 |
|   2 |   SORT AGGREGATE    |                            |      0 |      1 |      0 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| DOB                        |      0 |  69871 |      0 |00:00:00.01 |
--------------------------------------------------------------------------------------------------

Session 2:

commit;Commit complete.

Session 1:

select /*+ result_cache */ max(OBJECT_ID) from dob;

MAX(OBJECT_ID)
--------------
75003
SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob

Plan hash value: 648181113

------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.06 |     976 |
|   2 |   SORT AGGREGATE    |                            |      1 |      1 |      1 |00:00:00.06 |     976 |
|   3 |    TABLE ACCESS FULL| DOB                        |      1 |  69871 |  65761 |00:00:00.01 |     976 |
------------------------------------------------------------------------------------------------------------

select cache_id, CACHE_KEY, NAME, status, creation_timestamp, scn, build_time, row_count, scan_count from v$result_cache_objects;

CACHE_ID                       CACHE_KEY                      NAME                                               STATUS     CREATION_TIMEST        SCN BUILD_TIME  ROW_COUNT SCAN_COUNT

bskcp8b45qj3q5ju5uwg5fuscg     d8g29yuwtjtn9d2bcvaykm08fc     select /*+ result_cache */ max(OBJECT_ID) from dob Invalid    08-OCT-08          6102319          6          1          2

Ouch. Even the ISOLATION LEVEL serializable works fine for the Result itselve (75003 all the time), and the SCN of the result is stored somewhere, the result is global invalidated. I guess this is an area for improvement for Oracle.

Update: I got the same results with 11.1.0.7