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 <[EMAIL PROTECTED]>
<schlossnagle@py cc:
thian.com> Subject: Re: Shared pool wait for
library cache pin
Sent by:
[EMAIL PROTECTED]
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 -- [EMAIL PROTECTED] -- 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, [EMAIL PROTECTED]
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 <[EMAIL PROTECTED]>
> <schlossnagle@py cc:
> thian.com> Subject: Re: Shared pool
> wait for library cache pin
> Sent by:
> [EMAIL PROTECTED]
>
>
> 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" <[EMAIL PROTECTED]>
> 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: [EMAIL PROTECTED]
>>
>> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
