Re: Shared pool wait for library cache pin

Jared,

We were seeing from 2 or 3 up to a dozen shared pool waits at a time for about 200 users.

We normally haven't been seeing shared pool waits at all on this application. It was an unusual one to investigate.

Our developer was extremely concerned about this issue and brought it to our attention. Eventually the problem peaked at mid-day and performance for our end-users was twice as slow for people using the web-based app.

We finally flushed the shared pool and all the waits cleared right up. Performance on an overnight batch job that had been creeping up longer and longer was six times faster.

Must be some sort of shared memory leak or open cursor problem. We're looking at the SQL with a lot of open cursors but after flushing the shared pool, there didn't seem to be an inordinent amount of open cursors for the number of users out there.

We also bounced the database last night during our weekly maintenance window in order to clean everything our.

Today we are tasked with trying to figure out how/why this leak happened and how we can prevent it from happening again.

This is a web-based application with Java Applets. Unix Sun Solaris 2.6 back-end with 8.1.5 database. We also have a Checkpoint firewall between the backend and the front end but I doubt that has anything to do with it.

Thanks for your interest,

Cherie

                                                                                       
                       
                    Jared.Still_at_ra                                                     
                       
                    disys.com            To:     ORACLE-L_at_fatcity.com                  
                       
                                         cc:     Cherie_Machler_at_gelco.com              
                       
                    01/02/02 03:35       Subject:     Re: Shared pool wait for library 
cache pin              
                    PM                                                                 
                       
                                                                                       
                       
                                                                                       
                       

Cherie,

What constitutes 'a lot of shared pool waits (for libary cache pin)'?

Does it represent a significant percentage of total waits, or is it just crossing some predetermined threshold?

Is it causing a performance problem?
( as seen by users, not by a monitoring tool )

Jared

                    Cherie_Machler

                    @gelco.com           To:     Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
                    Sent by:             cc:

                    root_at_fatcity.c       Subject:     Shared pool wait for
library cache pin
                    om



                    01/02/02 12:20

                    PM

                    Please respond

                    to ORACLE-L

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: 
  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).