Cases when oracle invalidates result_cache results without any changes in objects?

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: http://www.xt-r.com/2012/07/when-oracle-invalidates-resultcache.html

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"?

--

Best regards,
Sayan Malakshinov
Oracle perfomance tuning engineer
PromSvyazBank
malakshinovss_at_psbank.ru
--

http://www.freelists.org/webpage/oracle-l Received on Wed Aug 01 2012 - 11:35:13 CDT