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 its results frequently invalidates 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;
2. deletion of unrelated rows from parent table 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"?

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;
/
-- 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!
-- 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.

Comments

Отправить комментарий