| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Shared pool wait for library cache pin
George,
Thanks for this tremendously helpful email. Just what the doctor ordered.
Cherie
"George
Schlossnagle" To: <ORACLE-L_at_fatcity.com>,
<Cherie_Machler_at_gelco.com>
<schlossnagle_at_py cc:
thian.com> Subject: Re: Shared pool wait for
library cache pin
01/03/02 09:06
AM
Hi,
There are a number of 8.1.5 bugs that can cause these symptoms. There is
the null-refresh snapshot bug (1348501), and a number of aditional bugs of
whihc may or may not be published (but that are referenced in other notes
on
metalink), including, but not limited to
1000866: HIGH V$SQLAREA.VERSION_COUNT/LOADED_VERSIONS FOR INSERT USING BIND
VARIABLES
1522724: HIGH VERSION_COUNT & LOADED_VERSIONS IN V$SQLAREA FOR SQL USING
BIND VARIABLES
1210242: Cursors not shared if both TIMED_STATISTICS and SQL_TRACE are
enabled
1318267: INSERT AS SELECT may not share SQL when it should
If your high version_count seems to be related to a high number of
invalidations (on the same query of course - also in v$sqlarea), then it
may
not be a bug per se, it may just be due to changed dependecies. If so, you
may want to try this script off of Steve Adams' site to see where your
invalidations are likely stemming from:
http://www.ixora.com.au/scripts/sql/whence_invalidations.sql
George
>
> George/Arun,
>
> I see that bug
>
>
>
>
> 1640583
>
> is present
in 8.1.6.3 but I'm
> having
trouble confirming that
> it is
present in 8.1.5.
> How can I
positively confirm
> that?
>
> This bug
text does not mention
> the
timed_statistics issue.
> Is there
some place where
> this
connection is documented.
> I need
something I can show
> the
application owner
> that has
more details than the
> text of
this bug has. The
> bug text
itself is not very
>
enlightening.
>
> I don't
see
anything else on
> Metalink
that has more
> details.
>
> Any
further
advice is greatly
>
appreciated.
>
> Cherie
Machler
> Oracle DBA
> Gelco
Information Network
>
>
>
>
>
>
>
> 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).Received on Thu Jan 03 2002 - 10:44:24 CST
 |
 |