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

на 23:25
Ярлыки: , ,

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.

Spread The Love, Share Our Article

  • Delicious
  • Digg
  • NewsVine
  • RSS
  • StumbleUpon
  • Technorati
  • Twitter

Comments

Tim... комментирует...

Hi.

Did you ever find out any other situations where the invalidations happen? I'm having a similar issue myself and I'm at a loss for the possible cause.

Cheers

Tim...

10 ноября 2012 г. в 14:20
Sayan Malakshinov комментирует...

Tim,

I am glad that you read this post. Yes, on our production servers we have strange invalidations. We wrote SR about 3 months ago and oracle support registered the bug (14516012) about 2 months ago, but it is still have "Bug Screening/Triage" status. I will describe our situation - мaybe your situation is similar to ours:

We have simple function with one query. "result_cache" results has only 2 dependencies: the function itself and small table(t1). Every second is a lot of invalidations. In v$result_cache_objects we see that cause of invalidations is this table. "Select for update" all rows from this table don't stop invaliations. We haven't unindexed foreign keys.

So often invalidations could cause only one partitioned table(t2), which has foreign key(with local index) referenced to the table, from which depend function. T2 partitioned by range of field from FK. But i cannot reproduce this situation on test databases.

Do you have partitioned table with fk to the problematic table?

Best regards,
Sayan

11 ноября 2012 г. в 00:28
Tim... комментирует...

Hi.

Not a partitioned table. Lots of little tables being accessed in the function though. In a way it's good to know it is happening to someone else though. At least now I don't think I'm going mad. :)

I wrote a summary of simple caching methods when calling functions from SQL.

http://www.oracle-base.com/articles/misc/efficient-function-calls-from-sql.php

For this issue I've had to ignore the result cache for the moment. :(

Thanks for your post. It was very helpful!

Cheers

Tim...

12 ноября 2012 г. в 12:38

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

Подписаться на: Комментарии к сообщению (Atom)