Re: Cases when oracle invalidates result_cache results without any changes in objects?
- From: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Fri, 10 Aug 2012 09:20:19 +0400
May be anybody knows how to trace when rc invalidates and why?
Event 43905 didn't help because it for result cache in sql namespace. And i
don't know how to use event 43906.
We created SR, but i don't think, that it will help shortly.
02.08.2012 1:25 "Sayan Malakshinov" <xt.and.r@xxxxxxxxx> wrote:
>
> No, when resuls exceeding space, they are flushed out from cache, but
> not invalidated.
>
> On Thu, Aug 2, 2012 at 1:13 AM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
> > 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.
> > 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
Other related posts: