Shot in the dark

From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Mon, 23 Feb 2009 13:57:55 -0500
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@xxxxxxxxxxxxxxxxx
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
==============================================================================
Shot in the dark

Other related posts:
