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

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <xt.and.r@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 1 Aug 2012 17:13:08 -0400

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


Other related posts: