Oracle SQL | When oracle invalidates result_cache function results without any changes in objects on which depends

On our production servers we have simple function with result_cache, like this:

create or replace function f_rc(p_id number) return number result_cache
is
  ret number;
begin
  select t.val into ret from rc_table t where t.id=p_id;
  return ret;
exception 
  when no_data_found then 
     return null;
end;
/

And oracle invalidates its results very frequently without any changes in table or function. I found only 2 cases when oracle invalidates result_cache results without any changes in table:
1. “select for update” from this table with commit; (strictly speaking, select-for-update is DML and it changes blocks, so it’s ok)
2. deleting rows from parent table that has no child records if there is unindexed foreign key with “on delete cascade”.
I test it on 11.2.0.1, 11.2.0.3, on solaris x64 and windows. Test cases for this i will show below.
But none of them can be the cause of our situation: we have no unindexed fk, and even if i lock all rows with “select for update”, it still does not stop invalidating.
In what other cases this happens? Am I right that the oracle does not track any changes, but the captures of the locks and “commits”?

tables and function

[sourcecode language=”sql”]drop function f_rc;
drop table rc_table purge;
drop table rc_parent purge;

create table rc_parent(id primary key, val) as
select level,level from dual connect by level<=10;

create table rc_table(id primary key,parent,val) as
select level,level,level from dual connect by level<=10;

create index ix_rc_table_parent on rc_table(parent);
alter table rc_table add constraint fk_parent foreign key(parent) references rc_parent(id);

create or replace function f_rc(p_id number) return number result_cache
is
ret number;
begin
select t.val into ret from rc_table t where t.id=p_id;
dbms_output.put_line(‘fired’);
return ret;
exception when no_data_found then return null;
end;
/
[/sourcecode]

[collapse]

Test case with 'select for update'

[sourcecode language=”sql”]– first execution:
DB11G/XTENDER> select f_rc(1) from dual;

F_RC(1)
———-
1

1 row selected.

fired
— checking result_cache
DB11G/XTENDER> /

F_RC(1)
———-
1

1 row selected.
— execute in another session: select * from rc_table for update;
— and check again:
DB11G/XTENDER> /

F_RC(1)
———-
1

1 row selected.
— result valid:
DB11G/XTENDER> col name form a50
DB11G/XTENDER> select id,type,status,name,namespace,creation_timestamp,scn from v$result_cache_objects;

ID TYPE STATUS NAME NAMES CREATION_TIMESTAMP SCN
———- ———- ——— ————————————————– —– ——————- ———-
2 Dependency Published XTENDER.RC_TABLE 29.07.2012 01:31:35 40643967
0 Dependency Published XTENDER.F_RC 29.07.2012 01:31:35 40643967
1 Result Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1 PLSQL 29.07.2012 01:31:35 40643967

3 rows selected.
DB11G/XTENDER> save rc_objects
Created file rc_objects.sql
— rollback in another session after "select for update" and check again:
— result still valid:
DB11G/XTENDER> /

3 rows selected.
DB11G/XTENDER> select f_rc(1) from dual;

F_RC(1)
———-
1

1 row selected.

DB11G/XTENDER> save test replace
Wrote file test.sql
— again "select * from rc_table for update;" in another session but now with "commit;"
— check again:
DB11G/XTENDER> @rc_objects

ID TYPE STATUS NAME NAMES CREATION_TIMESTAMP SCN
———- ———- ——— ————————————————– —– ——————- ———-
2 Dependency Published XTENDER.RC_TABLE 29.07.2012 01:31:35 40644109
0 Dependency Published XTENDER.F_RC 29.07.2012 01:31:35 40643967
1 Result Invalid "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1 PLSQL 29.07.2012 01:31:35 40643967

3 rows selected.
— scn changed! and result is invalid now!
[/sourcecode]

[collapse]

Unindexed fk

[sourcecode language=”sql”]– getting new result
DB11G/XTENDER> @test

F_RC(1)
———-
1

1 row selected.

fired
— execute in another session: insert into rc_parent values(-1,-1);
DB11G/XTENDER> @rc_objects

— new result is valid
— execute in another session: rollback;
DB11G/XTENDER> @test

F_RC(1)
———-
1

DB11G/XTENDER> @rc_objects

— again insert in another session, but now with commit:
— insert into rc_parent values(-1,-1);
— commit;
— still valid:
DB11G/XTENDER> @rc_objects

— in another session: delete from rc_parent where id=-1; rollback;
DB11G/XTENDER> @rc_objects

— in another session: delete from rc_parent where id=-1; commit;
— still valid:
DB11G/XTENDER> @rc_objects

— now without index:
DB11G/XTENDER> drop index ix_rc_table_parent;

Index dropped.

DB11G/XTENDER> @rc_objects

4 rows selected.

DB11G/XTENDER> @test

F_RC(1)
———-
1

1 row selected.

fired
DB11G/XTENDER> @rc_objects

ID TYPE STATUS NAME NAMES CREATION_TIMESTAMP SCN DEPEND_COUNT SCAN_COUNT
———- ———- ——— ————————————————– —– ——————- ———- ———— ———-
2 Dependency Published XTENDER.RC_TABLE 29.07.2012 01:31:35 40644745 1 0
0 Dependency Published XTENDER.F_RC 29.07.2012 01:31:35 40643967 1 0
4 Result Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1 PLSQL 29.07.2012 01:51:27 40644759 2 0
3 Result Invalid "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1 PLSQL 29.07.2012 01:41:53 40644522 2 3
— again insert with commit in another session
DB11G/XTENDER> @rc_objects

ID TYPE STATUS NAME NAMES CREATION_TIMESTAMP SCN DEPEND_COUNT SCAN_COUNT
———- ———- ——— ————————————————– —– ——————- ———- ———— ———-
2 Dependency Published XTENDER.RC_TABLE 29.07.2012 01:31:35 40644745 1 0
0 Dependency Published XTENDER.F_RC 29.07.2012 01:31:35 40643967 1 0
4 Result Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1 PLSQL 29.07.2012 01:51:27 40644759 2 0
3 Result Invalid "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1 PLSQL 29.07.2012 01:41:53 40644522 2 3
— now with "on delete cascade":
DB11G/XTENDER> alter table rc_table drop constraint fk_parent;

Table altered.
DB11G/XTENDER> alter table rc_table add constraint fk_parent foreign key(parent) references rc_parent(id) on delete cascade;

Table altered.
DB11G/XTENDER> exec dbms_result_cache.Flush;

PL/SQL procedure successfully completed.
DB11G/XTENDER> @test

F_RC(1)
———-
1

1 row selected.

fired
DB11G/XTENDER> @rc_objects

ID TYPE STATUS NAME NAMES CREATION_TIMESTAMP SCN DEPEND_COUNT SCAN_COUNT
———- ———- ——— ————————————————– —– ——————- ———- ———— ———-
2 Dependency Published XTENDER.RC_TABLE 29.07.2012 01:58:53 40645010 1 0
0 Dependency Published XTENDER.F_RC 29.07.2012 01:58:53 40645010 1 0
1 Result Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1 PLSQL 29.07.2012 01:58:53 40645010 2 0
— again in another session: insert into rc_parent values(-1,-1);commit;delete from rc_parent where id=-1;commit;
— checking:
DB11G/XTENDER> @rc_objects

ID TYPE STATUS NAME NAMES CREATION_TIMESTAMP SCN DEPEND_COUNT SCAN_COUNT
———- ———- ——— ————————————————– —– ——————- ———- ———— ———-
2 Dependency Published XTENDER.RC_TABLE 29.07.2012 01:58:53 40645202 0 0
0 Dependency Published XTENDER.F_RC 29.07.2012 01:58:53 40645010 0 0
1 Result Invalid "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1 PLSQL 29.07.2012 01:58:53 40645010 2 0
— Result is invalid now!
DB11G/XTENDER> — now with index:
DB11G/XTENDER> create index ix_rc_table_parent on rc_table(parent);

Index created.

DB11G/XTENDER> exec dbms_result_cache.Flush;

PL/SQL procedure successfully completed.

DB11G/XTENDER> @test

F_RC(1)
———-
1

1 row selected.

fired
DB11G/XTENDER> @rc_objects

3 rows selected.
— again in another session: insert into rc_parent values(-1,-1);commit;delete from rc_parent where id=-1;commit;
DB11G/XTENDER> @rc_objects

3 rows selected.
— Result still is valid.
[/sourcecode]

[collapse]