Re: Shared pool wait for library cache pin

there is a bug with 815 with timed_statistics = true. it will cause your sql not to be reused from the shared pool. search in metalink for more details..

try setting it to false...

babu

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, January 03, 2002 8:55 AM

>
> George/Arun,
>
> Thanks for your helpful replies. This information may help me
> explain the performance problems that peaked yesterday.
>
> I do have timed_statistics set to true and my version is 8.1.5.
>
> How do you define whether a version count is "high" ? Is there
> some threshould that you cross when it becomes "high". Is it
> a percentage of total statements or users or what?
>
> This problem seems to have creeped up on us over time.
> After I flushed the shared pool yesterday, we seem to be
> o.k. right now. Our application owner is concerned that this
> problem may recur.
>
> If we do indeed have this bug, then our only recourse seems
> to be either to upgrade to 8.1.7.x or to use the work-around of
> flushing the shared pool, right?
>
> Thanks for taking time to reply.
>
> Cherie
>
>
>
> George
> Schlossnagle To: Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
> <schlossnagle_at_py cc:
> thian.com> Subject: Re: Shared pool
wait for library cache pin
> Sent by:
> root_at_fatcity.com
>
>
> 01/03/02 12:50
> AM
> Please respond
> to ORACLE-L
>
>
>
>
>
>
> Hi,
>
> Queries with high version counts can cause library cache latch
> contention, as the query must hold that latch during soft parse and has
> to go through the entire list of query versions in the data dictionary.
> High version_count's are often caused by a high number of invalidations
> on the query. Invalidations of a query can be caused by a number of
> different things (one is the bug with timed_statistics, another is a bug
> present up to 8.1.6.3 in some instances when materialized views are
> used), other causes of invalidations are analyzing a dependent table or
> index, or truncating a dependent table.
>
> If this is causing you a problem, the next step is to track down what
> the cause of your high version_counts is, and work to correct it or
> workaround it (depending on the frequency that the query is executed, a
> shared pool flush may remove all versions (or none)). If it's not
> causing you a service problem currently, I would still keep an eye on
> it, as the version_count for queries rises, the chances of getting
> severe contention on the library cache latch increases.
>
> George
>
> // George Schlossnagle
> // www.pythian.com -- schlossnagle_at_pythian.com -- 877-PYTHIAN
> // Smarter than adding another team member, Pythian has new services
> // for supplementing DBAs: get our help with monitoring, 24x7 on-call,
> // daily verifications, storage management, performance and more.
>
>
> On Wednesday, January 2, 2002, at 04:35 PM, Cherie_Machler_at_gelco.com
> wrote:
>
> >
> > Yes, there are a handful with more than 400.
> > I'm not sure what high is?
> >
> > Cherie
> >
> >
> >
> > "George
> > Schlossnagle" To: Multiple recipients
> > of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > <schlossnagle_at_py cc:
> > thian.com> Subject: Re: Shared pool
> > wait for library cache pin
> > Sent by:
> > root_at_fatcity.com
> >
> >
> > 01/02/02 02:41
> > PM
> > Please respond
> > to ORACLE-L
> >
> >
> >
> >
> >
> >
> > Do any of your queries have a high version_count (visible through
> > v$sqlarea)?
> >
> > George
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Wednesday, January 02, 2002 3:20 PM
> >
> >
> >>
> >> We are seeing a lot of shared pool waits (for libary cache pin) on
> >> our 8.1.5 web-based application. We are seeing this via
> >> Precise/Indepth
> >> SQL monitoring tool.
> >>
> >> I haven't been able to find much documentation on shared pool waits or
> >> library cache pins.
> >>
> >> Can anyone tell me what might be causing this problem?
> >>
> >> Thanks,
> >>
> >> Cherie
> >>
> >> --
> >> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >> --
> >> Author:
> >> INET: Cherie_Machler_at_gelco.com
> >>
> >> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> >> San Diego, California -- Public Internet access / Mailing Lists
> >> --------------------------------------------------------------------
> >> To REMOVE yourself from this mailing list, send an E-Mail message
> >> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> >> the message BODY, include a line containing: UNSUB ORACLE-L
> >> (or the name of mailing list you want to be removed from). You may
> >> also send the HELP command for other information (like subscribing).
> >>
> >>
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: George Schlossnagle
> > INET: schlossnagle_at_pythian.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author:
> > INET: Cherie_Machler_at_gelco.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> >
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Cherie_Machler_at_gelco.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Babu Nagarajan
  INET: orclbabu_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).