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

Regards,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sayan Malakshinov
Sent: Wednesday, August 01, 2012 12:35 PM To: oracle-l_at_freelists.org
Subject: Cases when oracle invalidates result_cache results without any changes in objects?

Hi all!

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

--

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