Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

RE: Library Cache Lock

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Thu, 29 Jan 2004 12:50:01 -0800
Message-ID: <22280AE80343CE42905BE20DAC36B07F047EC71C@ussccem07.corp.hds.com>


Jonathan,

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 8.1.7.3 to 8.1.7.4, 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 [mailto:jonathan_at_jlcomp.demon.co.uk]
>Sent: Wednesday, January 28, 2004 12:13 PM
>To: oracle-l_at_freelists.org
>Subject: Library Cache Lock
>
>
>
>8.1.7.4
>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
>wait.
>
>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 ?
>
>
>Regards
>
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
> 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:
>http://www.jlcomp.demon.co.uk/tutorial.html
>
>
>Three-day seminar:
>see http://www.jlcomp.demon.co.uk/seminar.html
>____UK___February
>____UK___June
>
>
>The Co-operative Oracle Users' FAQ
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Thu Jan 29 2004 - 14:50:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US