RE: Snapshot row cache enqueue lock - Reviving 2001 Oracle-l post.

I am strugging with similar issues as well.

We have a process where updates to certain tables need to trigger refreshes of unrelated (as far as the database is concerned) materialized views. We have tables registered through change notification that calls a procedure that inserts records of materialized views that need be refreshed into a to_refresh table. There is then a scheduled job that wakes up every 5 minutes to see if new records have been added to to_refresh. If so, it calls dbms_mview.refresh.

We are seeing 2 problems.

The first is that every once in a while the refresh process causes a deadlock situation. Oracle identified this as bug#4201905. The workaround is to set some event. The workaround doesn't work for us.

The second is that the call to dbms_mview.refresh does not always work! For some reason the materialized views become invalidated. When that occurs dbms_mview.refresh doesn't actually work (at least not all the time). To attempt to work around this I issue an execute immediate 'alter materialized view xxx compile'; before I call dbms_mview.refresh. ...that doesn't work. I am attempting to set up a reproducible test case. I am struggling because it does not happen 100% of the time and I am unable to pin point what is causing the compile state to change.

Ugh.

chris

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hameed, Amir Sent: Thursday, November 02, 2006 1:45 PM To: oracle-l_at_timothyhopkins.net; steve.adams_at_ixora.com.au; oracle-l_at_freelists.org
Subject: RE: Snapshot row cache enqueue lock - Reviving 2001 Oracle-l post.

We recently ran into a similar issue with 9.2.0.6 64-bit/Solaris9 where while creating an MV, the system got hung with error "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!.." Oracle has identified it as a bug (bug# 3314850) and asked to apply patch to fix it. We are currently trying to reproduce this issue in our night copy of production.

Amir
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Hopkins Sent: Thursday, November 02, 2006 12:42 PM To: steve.adams_at_ixora.com.au; oracle-l_at_freelists.org Subject: Snapshot row cache enqueue lock - Reviving 2001 Oracle-l post.

Hi all,

        We still have a few production systems here on 8.1.7.0 and have been experiencing MV-refresh related deadlocks intermittently for some time.
I've just been asked to have a look at it and came across this oracle-l post from 2001 which sounds like the exact situation. The systemstate dump seems to agree with the first statement from Steve's post.

        The operation DBAs assure me that, whilst it is no longer set, the system was run with event 32333 (null refresh disable) set and the problems still occurred so that once again brings me back to the first statement.

        Hoping Steve with respond here: Did Oracle ever agree to log a bug and investigate?

        If so, was it the unpublished Bug 2664217 - "Undetected deadlock (dc_objects V library cache lock) invalidating dependencies" by any chance?

        Has anyone successfully applied patch 8.1.7.4.8 to avoid this problem?

Cheers,
Tim

RE: ERROR: WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=30 From: Steve Adams <steve.adams_at_ixora.com.au> Date: Mon, 12 Feb 2001 21:28:31 -0800
Message-ID: <F001.002B2285.20010212211127_at_fatcity.com>

Hi Glenn,

I got one of these last night.

The snapshot refresh process had an exclusive lock on the row cache enqueue for the snapshot and was waiting for a shared library cache lock on the base table. This is the WRONG locking order. Oracle is supposed to always take library cache locks before row cache enqueue locks. I am attempting to get Support to open a bug on it at the moment.

The other part of the problem is the null-refresh optimization introduced for 8i. The first DML on a snapshot master after a snapshot refresh actually changes the metadata for the base table to record the SCN of the DML operation in TAB$.SPARE3. Snapshot refresh operations record their SCN in SUM$.LASTREFRESHSCN. When a refresh is due, if the last refresh SCN is still higher than the SCN in TAB$, then no work is needed. However, maintaining these SCNs means reading and updating the metadata for DML operations, and that means taking X locks in the library cache and the dictionary cache, in that order. The X lock in the library cache has caused lot of problems, of which this is but another.

Oracle have "fixed" the null-refresh optimization in 9i by calling the dictionary cache primitives directly, so that the X lock in the library cache will not be needed any more. However, I suspect that the locking order for the snapshot refresh is a bug too. Let's see what Oracle say ...

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/
@   http://www.christianity.net.au/

-----Original Message-----
Sent: Tuesday, 13 February 2001 2:16
To: Oracledba_at_Lazydba. Com; ORACLE-L_at_fatcity.com

The system was not too busy. Processes running were materialized view refreshes (stored procs doing rollups, joins, etc...). The system was then locked up. Some queries could be run, others couldn't. Refreshes never completed. I had to alter system kill to release the hang.

Here are the errors:

>From the alert file;

    WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=30

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l



CONFIDENTIALITY NOTICE: 
	This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged,
confidential and exempt from disclosure under applicable law.  If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.  If you have received this
communication in error, please notify us immediately by email reply.
--
http://www.freelists.org/webpage/oracle-l