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