Re: intermittent library cache pin/lock problems

"NetComrade" <netcomradeNSPAM_at_bookexchange.net> wrote in message news:439dfd1b.267758328_at_localhost...
>
> From my understanding the parameter (which, btw, at some point Oracle
> recommended to set) should keep all the objects for any (all sessions)
> until that session exits. And you're implying that either during
> garbage collection of such a session, or a general memory exhaustion
> could be causing this problem.
>

    Use of the library cache:

        When a session is 'interested' in an object, it locks it with
        a memory structure shown in x$kgllk. v$open_cursor is
        effectively just a view of x$kgllk.

        When a session is actively using an object, it pins it with
        a memory structure shown in x$kglpn.

        So if you have a cursor open, but unused, you will have
        a x$kgllk on it; if you fetch from it, you temporarily acquire
        a x$kglpn on it.

        The view dba_kgllock (created in the SYS schema
        by script catblock.sql) displays these two x$.

    When you set cursor_space_for_time = true, you simply do     not release the x$kglpn structure after using an object. Consequently     you end up holding lots of structures in the SGA which might     otherwise be freeable (by virtue of being recreatable) when the SGA     comes under memory pressure. In particular, you are taking up a     lot of tiny chunks of memory which are YOUR x$kglpn structures.

> I think memory exhaustion should be ruled out, since there was plenty
> (~100M) of 'free' memory left over in the shared pool (and it's
> something we constantly monitor with scripts anyway).

        Volume of free memory is not necessarily the issue; there
        may be a fragmentation issue.  If you want a 16KB block
        of memory, and you have 100,000 chunks of 160 bytes,
        then you can still get a 4031 error.

>
> However, the
> garbage collection is interesting. How often do you think Oracle does
> that (I think you're implying on session disconnect), and could it be
> monitored what was affected during a clean up?
>

    Clarification: there is no 'garbage collection' in the sense of a     spare process wandering around from time to time cleaning     up and coalescing the memory that a session has discarded.     (unless you count pmon cleaning up a process after the process     has crashed). When a session deliberately finishes with a chunk     of memory, it puts it back onto the relevant shared pool free list.     In particular, when a session terminates, it puts all the memory     chunks it has allocated back on to the free list (probably doing     a coalesce as it goes - but at the moment I can't think how it     would do that efficiently).

> I also don't fully understand why the pin would even be needed for sql
> statements that appear to be frequently executed and use bind
> variables (i've seen a whole bunch of those for the sessions waiting
> for library cache pin/load lock)
>

    To execute an open cursor, you have to

        grab some memory to create a pin structure
        attach the structure to the pin list
        attach the pin to the object you want to execute
        execute
        detach the pin from the object
        detach the pin from the list
        return the memory to the shared pool.

Waits for pins suggest that the thing you want to pin is currently pinned exclusively by some other session - which often means that the object is being re-validated or is being reloaded. Since you are running with the parameter cursor_space_for_time = true, it seems unlikely that you are suffering from reloads (check v$librarycache), but it is possible that you are doing something which invalidates objects from time to time (e.g. truncating partitions, exchanging partitions, using and truncating global temporary tables).

> How does one determine if the pool (library cache) is fragmented, or
> look for other degradation pointers? I feel there is too much 'magic'
> surrounding the shared pool.

        Whatever you do, don't run the script that Oracle supplies
        on Metalink for reviewing the x$ksmsp - on a system like
        yours, it may jam the entire instance for a couple of minutes.

        The difficulty with analysing a problem in the library cache
        when it's big and busy is that you may find that your attempt
        to see what's happening causes a catastrophe which hides
        the problem. There are dumps, and x$ queries, that can reveal
        some useful information but they can have a massive impact
        on performance.

As a side thought - you may find that your shared_pool_reserved is too small. It needs to be about 25KB * maximum number of concurrent session as a minimum, with a few extra MB to allow for large objects due to pl/sql packages etc. If your problem is fragmentation, then you want the reserved pool to be large enough so that when the shared pool comes under pressure, large objects are placed in the reserved pool rather than causing a run of the library cache freelists.

> There isn't much description of
> 'internals' exactly what latch (children) are needed for what
> operation in the documentation.
> .......
> We use Oracle 8.1.7.4 and 9.2.0.6/7 on Solaris 2.7 and RH4
> remove NSPAM to email

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 29th Nov 2005