Re: Shared pool wait for library cache pin

> 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.
and sql_trace? There are some workarounds posted in the bug report for the timed statistics error.

>
> 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?
It should be completely unrelated to both those variables. Basically (disclaimer: this is purely speculation based off observation and experience), the loaded versiosn of a query are kept as a linked list. When a session soft parses the query, it has to go through all the versions to find the current valid one. it has to hold the latch as it searches the list. I would worry about any version counts over 100.

>
> 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 the query is not active at the time of the flush, it should be eligible for removal. If it's frequently executed, you may not be able to flush it out with a shared pool flush. If the count increases slowly, this may be a valid workaround for you.

>
> 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?

Be aware that invalidations (which new versions can be attributed to) can also be cause by changing dependencies. Analyzing your tables or truncating dependent objects will also cause this. There are a number of distinct, unrelated causes of high version counts in 8.1.5, I would try to nail down which it is and attack the problem from there. Again, if the counts grow slowly and a flush resets them, then you have a great workaround to hold you until you come up with a robust solution.

George

• http://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.

>
> 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: 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).