Re: Library Cache Lock

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 30 Jan 2004 08:44:43 -0000
Message-ID: <016501c3e70d$544c8d40$6702a8c0@Primary>

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_at_sbcglobal.net> To: <oracle-l_at_freelists.org> 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_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 Fri Jan 30 2004 - 02:44:43 CST