Re: intermittent library cache pin/lock problems

>library cache load lock 3,225 2,895 37.78
>library cache pin 3,737 2,026 26.44

Have you tried pinning things into the library cache? Look at v$db_object_cache for larger objects (ie sharable_mem > 100000, type in PACKAGE, PACKAGE BODY, FUNCTION, PROCEDURE) and kept = 'NO', or the DBMS_SHARED_POOL.SIZES package.

What can happen is that the shared pool gets fragmented, so it's difficult to find contiguous memory for whatever needs to be loaded, so you wait on those pins and locks. Made much worse by cursor_space_for time because... well, read the doc!
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch132.htm#1015645

If you make your shared pool big enough for all packages and all cursors, there still might be memory leaks... I speculate you are seeing a periodic garbage cleanup.

jg

--
@home.com is bogus.
SEO http://msnbc.msn.com/id/10415455/site/newsweek/