Library Cache Lock

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 28 Jan 2004 20:12:33 -0000
Message-ID: <003b01c3e5db$2362c560$6702a8c0@Primary>

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
-----------------------------------------------------------------

Received on Wed Jan 28 2004 - 14:12:33 CST