Re: Cases when oracle invalidates result_cache results without any changes in objects?
From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Fri, 10 Aug 2012 09:20:19 +0400
Message-ID: <CAOVevU62Y-cugAimcza7XGTgYwjV19YO=r-dWJL+b81eM_0KJw_at_mail.gmail.com>
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_at_gmail.com> 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_at_rsiz.com> wrote:
> > Is there any chance you are exceeding the allocated result cache space?
> >
> > 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.
> > 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"?
> >
> >
>
>
Date: Fri, 10 Aug 2012 09:20:19 +0400
Message-ID: <CAOVevU62Y-cugAimcza7XGTgYwjV19YO=r-dWJL+b81eM_0KJw_at_mail.gmail.com>
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_at_gmail.com> 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_at_rsiz.com> wrote:
> > Is there any chance you are exceeding the allocated result cache space?
> >
> > 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.
> > 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-lReceived on Fri Aug 10 2012 - 00:20:19 CDT