Is there any chance you are exceeding the allocated result cache space? Regards, mwf -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sayan Malakshinov Sent: Wednesday, August 01, 2012 12:35 PM To: oracle-l@xxxxxxxxxxxxx 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@xxxxxxxxx -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l