> George, > > Just checked again right now. Following is the result that I got: > > SQL> select version_count, count(*) from v$sqlarea group by version_count; > > VERSION_COUNT COUNT(*) > ------------- ---------- > 1 48241 > 2 9 > 4 2 > 8 1 > 27 1 >
What is the query with the high version count (I don't think it's the one with the literal sql (though you should consider setting cursor_sharing = force, to have oracle bind all your literal sql for you), I would imagine that's part of the slew of queries with 1 version). > Yesterday, after we flushed the shared pool, the highest value I got was > about 400. If after the flush it remained at 400 then there was an active session using that sql. Is there a period of time when no one is using your application (like in the middle of the night) when you can execute a shared pool flush? You may have better luck then. What is the history on this issue? How long was the app running fine before you started having problems? Were any changes at all made proximal to that time (for example, any changes to the underlying tables, or implementation of a snapshot with them asthe master table, or a change in backup method from cold-backup to hot-backup?) Anything at all that you can correlate with this becoming a noticeable issue? I agree with Jared that upgrading may be a good idea, but it would be nice to confirm what bug/feature you are hitting before you do an emergency upgrade. > > Need I mention that this app has a couple of statements that don't use bind > variables. > > Obviously, this looks fairly bad already. I'm trying to get Steve Adam's > whence_invalids script > to work but am having trouble with one of the views. I'm missing the view > and am trying to > create it. Not sure if it's available in this version: sys.x_$kglob Steve creates views on all the x$ tables. if you go into the scripts part of the site (http://www.ixora.com.au/scripts/prereq.htm) there's a script to generate them all (called create_xviews.sql or something similair). Alternatively, if you want to run the queries as sys, you can just change all the x_$'s to x$'s. > > We are also monitoring shared memory usage and saw a big jump when a > regular batch > job was run at 10:30am. A jump in shared memory usage? Oracle preallocates all of it's shared memory at startup. Are you talking about a jump in regular process memory usage? > We think one/some of the statements in that job is > causing the problem. > > Thanks for your reply, > > Cherie > > > > "George > Schlossnagle" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > <george@omniti cc: > .com> Subject: Re: Shared pool wait for library cache pin > Sent by: > [EMAIL PROTECTED] > om > > > 01/03/02 10:00 > AM > Please respond > to ORACLE-L > > > > > > > Hi Cherie, > > The version_count should be irrelevant of the number of users you have. I > doubt this is a shared memory leak, it sounds like simple latch conetention > based on the number of versions you have of certain queries. If flushing > your shared pool resets the version count for that query, then that is a > good workaround. If not, a bounce will clearly take care of it. How fast > are your version count/invalidations growing? > > George > > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, January 03, 2002 8:40 AM > > > > > > George, > > > > Not particularly based on the number of users that we have. > > > > See my other reply to Jared for follow-up. > > > > 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: 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: 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).
