Shot in the dark
Date: Mon, 23 Feb 2009 13:57:55 -0500
Message-ID: <21469B88E0EA11498818517F210335310C03C7F1_at_EPRI17P32001A.csfb.cs-group.com>
Oracle 9.2.0.8 on Solaris.
Application team implemented (against my advice) a refresh-on-commit materialized view. It joins four tables but doesn't use any aggregate functions.
Last night I got a call about Web site problems. I found a process that was holding a blocking JI (refresh) lock on the MV in question. It also held a CU (cursor bind) lock, along with several table row locks on tab$, the MV logs, etc. Another session was trying to get the JI lock on the same MV.
The interesting thing was that session 1, that held the lock, wasn't doing anything: over several iterations, queries showed no increase in logical reads, db block changes, CPU time, etc. Finally I killed that session and everything was good again. A manual refresh of the MV in question also completed successfully and quickly.
I've seen a few posts on M.O.S. and on the general Web that refer to "idle" sessions holding CU locks, but never an explanation. I though I'd post here to see if this scenario rings any bells.
Oh, and the alert log shows "Following on-commit snapshots not refreshed :" for the MV in question right around the time this all happened.
Any ideas welcomed! Thanks.
Paul Baumgartel
CREDIT SUISSE
Information Technology
Prime Services Databases Americas
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com
Please access the attached hyperlink for an important electronic communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Feb 23 2009 - 12:57:55 CST