Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Library Cache Lock

RE: Library Cache Lock

From: John Kanagaraj <>
Date: Thu, 29 Jan 2004 12:50:01 -0800
Message-ID: <>


We have had many similar situations where we had lib cache locks/pins on a _table_ - did you notice that all the waiters were queueing up behind another session that was 'hanging' on a 'row cache lock'? (for this happened consistently in our siutation). This is a fairly large Oracle Apps 11.5.7 installation - this occurred in a schema that has a number of partitioned tables and MVs. It is possible that there were partial refreshes of the MVs occurring at the time of the problem.

In any case, we had a large reduction in the occurrences when we upgraded from to, and obtained another reduction when we re-arranged and serialized some background jobs (Apps concurrent managers). A TAR with Oracle was useless - we were sent on a runaround and passed from one to another until we dropped the issue (since the pain went away). I did take some system dumps, but they might have been purged (I can dig around).

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is inevitable, but Discouragement is optional!

>-----Original Message-----
>From: Jonathan Lewis []
>Sent: Wednesday, January 28, 2004 12:13 PM
>Subject: Library Cache Lock
>Windows 2000 Server
>ca.200 sessions, about 30 active.
>About a dozen sessions go into waits
>for Library Cache Lock for the best
>part of 300 seconds. (At which they
>probably all get ORA-04021 and free
>themselves, but I couldn't tell, the problem
>happened a few minutes before I was due to
>leave the site, and the end-users were
>on the other side of the Atlantic). The
>odd session might get a library cache pin
>The object they were waiting for (tracked
>via x$kglob.kglhdadr = v$session_wait.p1raw)
>was a table. No-one has done ANYTHING
>untoward to the table, such as truncate, add column,
>index, analyze, for at least 3 days. The table is
>a normal heap table with half a dozen indexes.
>The best bet I could come up with was a
>metalink item that mentioned conflicts relating
>to materialized views - and there is a materialized
>view log on this table.
>The only other relevant detail I could come up
>with is that sessions create a session, work hard
>for a few seconds, then drop the session (maintaining
>the connection) so the system may be exercising some
>part of the code that creates x$kgllk items rather more
>heavily than is normal.
>Any thoughts ? Any similar experiences ?
>Jonathan Lewis
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>Next public appearances:
> Jan 29th 2004 UKOUG Unix SIG - v$ and x$
> March 2004 Hotsos Symposium - The Burden of Proof
> March 2004 Charlotte NC OUG - CBO Tutorial
> April 2004 Iceland
>One-day tutorials:
>Three-day seminar:
>The Co-operative Oracle Users' FAQ
>Please see the official ORACLE-L FAQ:
>To unsubscribe send email to:
>put 'unsubscribe' in the subject line.
>Archives are at
>FAQ is at

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Thu Jan 29 2004 - 14:50:01 CST

Original text of this message