RE: Shared pool wait for library cache pin

What a timely thread. I have been working on debugging a library cache lock issue as well. I am running 8.1.7 on HP-UX. When analyzing 1 certain table (estimate), I ALWAYS get a library cache lock. Then other sessions stack up waiting on this analyze statement. The analyze of any other table is fine. I know that analyzing a table should acquire an exclusive on the library cache, preventing any DDL changes, but DML should be okay.

Oracle has been working with me on this, but have not given any real concrete answers yet.

I feel that it may be related to my shared_pool being to large (100MB is free right now).

Also note the flushing the shared_pool does not help me.

Any other ideas?

Thanks.

John

john.fedock_at_kline.com

-----Original Message-----
Sent: Thursday, January 03, 2002 3:31 PM To: Multiple recipients of list ORACLE-L

> 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 <ORACLE-L_at_fatcity.com>
> <george_at_omniti cc:
> .com> Subject: Re: Shared pool wait
for library cache pin
> Sent by:
> root_at_fatcity.c
> 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" <ORACLE-L_at_fatcity.com>
> 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 <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: George Schlossnagle
> INET: george_at_omniti.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: george_at_omniti.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: Fedock, John (KAM.RHQ)
  INET: John.Fedock_at_KLINE.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).