Re: Library Cache Lock

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 30 Jan 2004 08:44:43 -0000

Sai,

Thanks for the note.

I don't think that an ANALYZE was going on here,
but they were using materialized view logs.  So if
the refresh took out an exclusive library cache pin
on the base table, that might be sufficient for a new 
session with a new SQL statement to wait for a library 
cache lock on the table. (I don't think it's supposed to
behave like that, but that's the beauty of bugs).

I've passed your comments on to the client, to see
if it matches their activity.


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


----- Original Message ----- 
From: "Sai Selvaganesan" <ssaisundar@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, January 29, 2004 11:42 PM
Subject: Re: Library Cache Lock


hi jonathan

i have faced a similar issue and please  see whether
the below is what u are facing.
we used to analyze tables (estimate statistics) and
when snapshot refresh used to happen in the same time,
there was library cache locking in the database and no
dml will go thru.this happens until the snapshots are
done. for a few sundays(on whcih analyze was being
done)i used to think analyze was causing this issue
this because when u do check x$kgllk analzye session
used to show up in the top. but a system dump showed
otherwise.i think snapshot was doing a xclusive lock
of the master table instead of a shareable lock when
refresh was going thru(i may be wrong here) .probably
will be noticeable in a very highly concurrent oltp
with a number of snapshot sites. but i was able to
give oracle a test case in 8.1.7.4. 
when materilaized view refreshes go thru it does that
library cache lock which can be disabled setting an
event on the source database(db where the mv logs
are). i think this event is 32333 but there is a
downside to this which says query rewrite on mvs will
not work. this event disables some mlog$ verification.

hope this helps
sai


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