RE: Library Cache Lock
- From: John Kanagaraj <john.kanagaraj@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Jan 2004 12:50:01 -0800
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!
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
>-----Original Message-----
>From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx]
>Sent: Wednesday, January 28, 2004 12:13 PM
>To: oracle-l@xxxxxxxxxxxxx
>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@xxxxxxxxxxxxx
>put 'unsubscribe' in the subject line.
>--
>Archives are at //www.freelists.org/archives/oracle-l/
>FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
