Library Cache Lock 2004-01-30 - By Jonathan Lewis
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@(protected) >
To: <oracle-l@(protected) >
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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
