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
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;
/
[collapse]
Test case with 'select for update'
-- 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> /
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> 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!
[collapse]
Unindexed fk
-- 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
ID TYPE STATUS NAME NAMES CREATION_TIMESTAMP SCN DEPEND_COUNT SCAN_COUNT
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ---------- ------------ ----------
2 Dependency Published XTENDER.RC_TABLE 29.07.2012 01:31:35 40644109 1 0
0 Dependency Published XTENDER.F_RC 29.07.2012 01:31:35 40643967 1 0
3 Result Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1 PLSQL 29.07.2012 01:41:53 40644522 2 0
-- new result is valid
-- execute in another session: rollback;
DB11G/XTENDER> @test
F_RC(1)
----------
1
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 40644109 1 0
0 Dependency Published XTENDER.F_RC 29.07.2012 01:31:35 40643967 1 0
3 Result Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1 PLSQL 29.07.2012 01:41:53 40644522 2 1
-- again insert in another session, but now with commit:
-- insert into rc_parent values(-1,-1);
-- commit;
-- still valid:
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 40644109 1 0
0 Dependency Published XTENDER.F_RC 29.07.2012 01:31:35 40643967 1 0
3 Result Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1 PLSQL 29.07.2012 01:41:53 40644522 2 1
-- in another session: delete from rc_parent where id=-1; rollback;
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 40644109 1 0
0 Dependency Published XTENDER.F_RC 29.07.2012 01:31:35 40643967 1 0
3 Result Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1 PLSQL 29.07.2012 01:41:53 40644522 2 2
-- in another session: delete from rc_parent where id=-1; commit;
-- still valid:
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 40644109 1 0
0 Dependency Published XTENDER.F_RC 29.07.2012 01:31:35 40643967 1 0
3 Result Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1 PLSQL 29.07.2012 01:41:53 40644522 2 2
-- now without index:
DB11G/XTENDER> drop index ix_rc_table_parent;
Index dropped.
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 0 0
0 Dependency Published XTENDER.F_RC 29.07.2012 01:31:35 40643967 0 0
3 Result Invalid "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1 PLSQL 29.07.2012 01:41:53 40644522 2 3
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
-- again delete with commit:
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
ID TYPE STATUS NAME NAMES CREATION_TIMESTAMP SCN DEPEND_COUNT SCAN_COUNT
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ---------- ------------ ----------
2 Dependency Published XTENDER.RC_TABLE 29.07.2012 02:23:13 40654418 1 0
0 Dependency Published XTENDER.F_RC 29.07.2012 02:23:13 40654418 1 0
1 Result Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1 PLSQL 29.07.2012 02:23:13 40654418 2 0
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
ID TYPE STATUS NAME NAMES CREATION_TIMESTAMP SCN DEPEND_COUNT SCAN_COUNT
---------- ---------- --------- -------------------------------------------------- ----- ------------------- ---------- ------------ ----------
2 Dependency Published XTENDER.RC_TABLE 29.07.2012 02:23:13 40654418 1 0
0 Dependency Published XTENDER.F_RC 29.07.2012 02:23:13 40654418 1 0
1 Result Published "XTENDER"."F_RC"::8."F_RC"#fac892c7867b54c6 #1 PLSQL 29.07.2012 02:23:13 40654418 2 0
3 rows selected.
-- Result still is valid.
[collapse]