Oracle 10g Performance Analysis with Doug Burns in Vienna

Doug Burns hold a 2 day class in Vienna. The topic was "Oracle 10g Performance Analysis with Doug Burns".
I had the pleasure to attend this class and saw (and of course listen to) Doug.
The course had 3 major parts (from my point of view):

1. General introduction to Tuning like:
   
   • It's all about time (not costs, not ratios, not beauty, ...)
   • the customer is the only who can tell what's good (and must do this, before tuning begins)
   • beware Compulsive tuning disorder
2. the history of the tools:
   
   • bstat/estat
   • wait interface
   • statspack
3. current tools (10g/11g) and new features (11g) - (or why it's all the same, just better)
   
   • ASH
   • AWR
   • ADDM
   • improvements in 11g

That's all I remember (high level) - so if I miss anything substantial, sorry Doug, I must have drifted off (which I have to account my little daughters growing teeth much more than Dough) .

It was the first time to see Dough and it was great. It was full of good examples (many of them known if you follow his blog or others, but that's good!), but he never lost the red line through all his slides.
As he started with some history, he picked me up in a well known and familiar area and guided me to the brave new world, their strenghts and pitfalls.

During all the coffee breaks (and there where enough, even for the smokers) quite refreshing debates arose. Very convenient.

At the end, Doug just finished before time, but I had no feeling he missed something during the past 2 days. So I was happy to use the time for some more  general, not so technical chat.

At all, these 2 days where the perfect recreation from ordinary labour work as a DBA for a geek like me!

dynamic SQL with DMLs

Once again, Christian Antognini is the reason for this entry (beside my curiosity of course).
In Chapter 8 - Parsing of his book Troubleshooting Oracle Performance he showed the advantage of explicit cursor handling. The package dbms_sql shows the most flexibility. Unfortunately he only showed SELECT statements, no DML (which I'm interrested for some reasons). So I had to do the tests myselve:

CREATE TABLE dyn_sql (id number, val varchar2(32));

DECLARE   v_cur_hdl         INTEGER;
  v_stmt_str        VARCHAR2(200);
  v_rows_processed  BINARY_INTEGER;
  gid               INTEGER;
  gval              VARCHAR2(32);
BEGIN   v_stmt_str := 'INSERT INTO dyn_sql VALUES (:gid, :gval)';
  v_cur_hdl := DBMS_SQL.OPEN_CURSOR(2); -- to avoid ORA-29471 in 11g  
  DBMS_SQL.PARSE(v_cur_hdl, v_stmt_str, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  1);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V01C01');
  
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  2);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V02C01');
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  COMMIT;

  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  3);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V03C02');

  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  4);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V04C02');
  
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  ROLLBACK;
  
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  5);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V05C03');
  
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  
  DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END;
 /

SQL> select * from dyn_sql;
        ID VAL
---------- ------
         1 V01C01
         2 V02C01
         5 V05C03

blubb
what are my findings?

1. DBMS_SQL works fine also for DMLs.
2. commit/rollback within an open cursor works fine.
3. closing a cursor implicite commits (direct sql-trace sequence: EXEC(insert) - CLOSE(insert) - EXEC(pl/sql) - CLOSE (pl/sql)Deleted after Chris commentar

There is an explicite warning in the documentation: Native Dynamic SQL is faster than DBMS_SQL. I did not test this. But be warned. (and test it yourselve, If you fear performance!)

minimal init.ora

my minimal init.ora has only 4 lines:

*.compatible='11.1.0.0.0'
*.control_files='/appl/oracle/oradata/BERX2/control01.ctl','/appl/oracle/oradata/BERX2/control02.ctl','/appl/oracle/oradata/BERX2/control03.ctl'
*.db_block_size=8192
*.db_name='BERX2'

where I have to check if I can omit the compatible. So it would be down to 3 lines. nice!
Without setting compatible, I get

ORA-00201: control file version 11.1.0.0.0 incompatible with ORACLE version
11.0.0.0.0
ORA-00202: control file: '/appl/oracle/oradata/BERX2/control01.ctl'

and a show parameter compatible gives

compatible                           string      11.0.0

strange, as the instance identifies itselve as

SQL> select version from v$instance;

VERSION
-----------------
11.1.0.7.0

So it seems the automatic value for compatible is derived only from the first Version number of v$instance.

sysstat_sample.sql

Based on Tanel Poders sample.sql and ses.sql I was interrested to get some informations about the System at all.
There are various discussions wether or not it is useful to have a look on the system when hunting a problem within a session. Nevertheless I decided I need such a tool. On the one hand to generate a 'good' baseline, on the other to have a quick, zentralised view. It's also of some use to compare the system-statistics to the values of one dedicated sessions to see if this session is the major reason for any changes or submit only a small amount, but might suffer from others in the system.

So here is the code:

select name,
  min(value)               "MIN",
  max(value)               "MAX",
  max(value) - min(value)  "overall_delta",
  max(delta)               "max_single_delta",
  sum(decode(delta,0,0,1)) "#changes"   -- count distinct 
from
  (with c as
     (select &1 counter from dual)
  select     rn,
 name,
 value,
     decode(rn,1,0,value-prev) delta  
from    ( select /*+ ordered use_nl(t) */
         r.rn,
 NAME,
 Value,
         LAG(VALUE) OVER (Partition BY NAME order by r.rn) prev
      from
        (select /*+ no_unnest */
 rownum rn,
 1 sample_dummy
          from dual
            connect by level <= (select counter from c)        ) r,        v$sysstat t      order by name, r.rn     )  ) syss
where   value     > 0
  and delta > 0
group by name
order by name
/

This piece of code samples through v$sysstat and generates for every entry (where the value > 0, to reduce the lines) a line with the minimal value, the maximal value, the delta between them (this could also be done by DBMS_LOCK.SLEEP) but also the greatest single step between 2 sequent samples.
There might also be other statistical functions of some interrest, but I have too little knowledge on statistics and how to use them (comments/suggestions welcome!).

The script can also be found here, where I will keep the most recent version all the time.

Composite Bitmap Indexes

In hist Book Troubleshoot Oracle Performance Christian Antognini wrote also about Composite Bitmap Indexes. (p400)
Unfortunately he only proclaims

Composite bitmap indexes are rarely created. This is because several indexes can be combined efficiently in order to apply a restriction. To see how powerful bitmap indexes are, let’s look at several queries. (p400)

without givin gany evidence. So I grabbed his scripts (thank you for providing them) and run some testcases (on 11.1.0.6):
I just created this additional bitmap index:

CREATE BITMAP INDEX bx_i_n456 on t (n4, n5, n6);

bitmap AND:

index_combine:

SELECT /*+ index_combine(t i_n4 i_n5 i_n6) */ *
FROM t
WHERE n4 = 6 AND n5 = 42 AND n6 = 11

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |      1 |      1 |00:00:00.01 |       7 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |      1 |00:00:00.01 |       6 |
|   3 |    BITMAP AND                |      |      1 |        |      1 |00:00:00.01 |       6 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   4 - access("N5"=42)
   5 - access("N6"=11)
   6 - access("N4"=6)

Cost: 3

composite bitmap index(CIB):

SELECT *
FROM t
WHERE n4 = 6 AND n5 = 42 AND n6 = 11

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T         |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |      1 |        |      1 |00:00:00.01 |       3 |
|*  3 |    BITMAP INDEX SINGLE VALUE | BX_I_N456 |      1 |        |      1 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------

   3 - access("N4"=6 AND "N5"=42 AND "N6"=11)

Cost:1

In this case the CBI wins.

bitmap OR:

index_combine:

SELECT /*+ index_combine(t i_n4 i_n5 i_n6) */ *
FROM t
WHERE n4 = 6 OR n5 = 42 OR n6 = 11

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |    797 |    767 |00:00:00.01 |     419 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |    767 |00:00:00.01 |       7 |
|   3 |    BITMAP OR                 |      |      1 |        |      1 |00:00:00.01 |       7 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       3 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   4 - access("N4"=6)
   5 - access("N6"=11)
   6 - access("N5"=42)

 Cost: 135

no hints:

SELECT *
FROM t
WHERE n4 = 6 OR n5 = 42 OR n6 = 11

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |    797 |    767 |00:00:00.01 |     419 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |    767 |00:00:00.01 |       7 |
|   3 |    BITMAP OR                 |      |      1 |        |      1 |00:00:00.01 |       7 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       3 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   4 - access("N4"=6)
   5 - access("N6"=11)
   6 - access("N5"=42)

Cost:135

index_combine with BX_I_N456: 

SELECT /*+ index_combine(t BX_I_N456 i_n5 i_n6) */ *
FROM t
WHERE n4 = 6 OR n5 = 42 OR n6 = 11   

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T         |      1 |    797 |    767 |00:00:00.04 |     420 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |      1 |        |    767 |00:00:00.03 |       8 |
|   3 |    BITMAP OR                 |           |      1 |        |      1 |00:00:00.03 |       8 |
|   4 |     BITMAP MERGE             |           |      1 |        |      1 |00:00:00.03 |       4 |
|*  5 |      BITMAP INDEX RANGE SCAN | BX_I_N456 |      1 |        |    527 |00:00:00.01 |       4 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N6      |      1 |        |      1 |00:00:00.01 |       2 |
|*  7 |     BITMAP INDEX SINGLE VALUE| I_N5      |      1 |        |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------

   5 - access("N4"=6)
       filter("N4"=6)
   6 - access("N6"=11)
   7 - access("N5"=42)

Cost: 138

index_combine with BX_I_N456 on 2nd place: 

SELECT /*+ index_combine(t i_n4 BX_I_N456 i_n6) */ *
FROM t
WHERE n4 = 6 OR n5 = 42 OR n6 = 11   

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T         |      1 |    797 |    767 |00:00:00.01 |     420 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |      1 |        |    767 |00:00:00.01 |       8 |
|   3 |    BITMAP OR                 |           |      1 |        |      1 |00:00:00.01 |       8 |
|   4 |     BITMAP MERGE             |           |      1 |        |      1 |00:00:00.01 |       4 |
|*  5 |      BITMAP INDEX RANGE SCAN | BX_I_N456 |      1 |        |    527 |00:00:00.01 |       4 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N6      |      1 |        |      1 |00:00:00.01 |       2 |
|*  7 |     BITMAP INDEX SINGLE VALUE| I_N5      |      1 |        |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("N4"=6)
       filter("N4"=6)
   6 - access("N6"=11)
   7 - access("N5"=42)

Costs: 138

in this case, the 3 seperated bitmap indices wins.

NOT EQUAL AND:

index_combine:

SELECT /*+ index_combine(t i_n4 i_n5 i_n6) */ *
FROM t
WHERE n4 != 6 AND n5 = 42 AND n6 = 11   

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |      1 |      1 |00:00:00.01 |       6 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |      2 |00:00:00.01 |       4 |
|   3 |    BITMAP AND                |      |      1 |        |      1 |00:00:00.01 |       4 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   1 - filter("N4"<>6)
   4 - access("N5"=42)
   5 - access("N6"=11)

Costs: 2

a different execution plan from Chris' Book, there a BITMAP MINUS was shown in the execution plan.
Maybe a question for a seperated session, where the BITMAP MINUS disappeared.

for some reason, this hint generated the BITMAP MINUS

SELECT /*+ index(t  BX_I_N456) */ *
FROM t
WHERE n4 != 6 AND n5 = 42 AND n6 = 11   

------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID   | T         |      1 |      1 |      1 |00:00:00.01 |      13 |
|   2 |   BITMAP CONVERSION TO ROWIDS  |           |      1 |        |      1 |00:00:00.01 |      12 |
|   3 |    BITMAP MINUS                |           |      1 |        |      1 |00:00:00.01 |      12 |
|   4 |     BITMAP MINUS               |           |      1 |        |      1 |00:00:00.01 |       8 |
|   5 |      BITMAP AND                |           |      1 |        |      1 |00:00:00.01 |       4 |
|*  6 |       BITMAP INDEX SINGLE VALUE| I_N5      |      1 |        |      1 |00:00:00.01 |       2 |
|*  7 |       BITMAP INDEX SINGLE VALUE| I_N6      |      1 |        |      1 |00:00:00.01 |       2 |
|   8 |      BITMAP MERGE              |           |      1 |        |      1 |00:00:00.01 |       4 |
|*  9 |       BITMAP INDEX RANGE SCAN  | BX_I_N456 |      1 |        |    526 |00:00:00.01 |       4 |
|  10 |     BITMAP MERGE               |           |      1 |        |      1 |00:00:00.01 |       4 |
|* 11 |      BITMAP INDEX RANGE SCAN   | BX_I_N456 |      1 |        |    526 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------------------------

   6 - access("N5"=42)
   7 - access("N6"=11)
   9 - access("N4"=6)
  11 - access("N4" IS NULL)

Cost: 4

So I decided to do a slightly different testcase:

index_combine:

SELECT /*+ index_combine(t i_n4 i_n5 i_n6) */ * 
FROM t 
WHERE n4 = 6 and n5 != 42 and n6 = 11

-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID   | T    |      1 |      8 |      7 |00:00:00.01 |      15 |
|   2 |   BITMAP CONVERSION TO ROWIDS  |      |      1 |        |      7 |00:00:00.01 |       8 |
|   3 |    BITMAP MINUS                |      |      1 |        |      1 |00:00:00.01 |       8 |
|   4 |     BITMAP MINUS               |      |      1 |        |      1 |00:00:00.01 |       6 |
|   5 |      BITMAP AND                |      |      1 |        |      1 |00:00:00.01 |       4 |
|*  6 |       BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  7 |       BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       2 |
|*  8 |      BITMAP INDEX SINGLE VALUE | I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
|*  9 |     BITMAP INDEX SINGLE VALUE  | I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------

   6 - access("N6"=11)
   7 - access("N4"=6)
   8 - access("N5"=42)
   9 - access("N5" IS NULL)

Costs: 6

CIB:

SELECT /*+ index(t BX_I_N456) */ * 
FROM t 
WHERE n4 =6 and n5 != 42 and n6 = 11

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T         |      1 |      8 |      7 |00:00:00.01 |      12 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |      1 |        |      7 |00:00:00.01 |       5 |
|*  3 |    BITMAP INDEX RANGE SCAN   | BX_I_N456 |      1 |        |      7 |00:00:00.01 |       5 |
----------------------------------------------------------------------------------------------------

   3 - access("N4"=6)
       filter(("N6"=11 AND "N5"<>42 AND "N4"=6))

Costs: 107

no hints:

SELECT  * 
FROM t 
WHERE n4 =6 and n5 != 42 and n6 = 11    
    
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |      8 |      7 |00:00:00.01 |      12 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |      8 |00:00:00.01 |       4 |
|   3 |    BITMAP AND                |      |      1 |        |      1 |00:00:00.01 |       4 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   1 - filter("N5"<>42)
   4 - access("N6"=11)
   5 - access("N4"=6) 

Costs: 4

My a little bit more explicit view of composite bitmap indexes is:

• They can be useful in AND statements:
• even not optimal for OR statements, they can replace the bitmap index which is created only on the first column without high cost increasement
• in NOT EQUAL AND statements they really kill the performance if enfoced.
  

syntax highlighter

I'm bored of playing around with html-tags just to get the code formatted a little bot better. So I'm switching to syntaxhighlighter from alex.gorbatchev As I'm a lazy boy, I used this documentation.

from now on, code should look lihe this:

select sysdate from dual;select sysdate+1 from dual;select sysdate+2 from dual;

Maybe I will rewrite some of my older posts to use this for better readability. - Maybe ;-)


UPDATE1: there is something going wrong, I don't know where the <br> come from. But I will check.
UPDATE2: solution: I must NOT put a newline at the beginning and end of the statements.
UPDATE3: I have to switch to draft blogger for editiong to make it work. strange thing. but the code formatting is worth the change.
UPDATE4: A solution is posted in syntaxhighlighters wiki.

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