Oracle SQL
  • LICENSE

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

Posted on July 30, 2012 by Sayan Malakshinov Posted in documentation, oracle, result_cache

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]

invalidation oracle undocumented behaviour result_cache
« An interesting question from Valentin Nikotin
Link to the online unwrapper in the official Oracle documentation »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL