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" <[EMAIL PROTECTED]> 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 <[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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babu Nagarajan 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).
