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@Lazydba. Com; [EMAIL PROTECTED]
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
>From the udump dir;
*** SESSION ID:(37.4) 2001-02-11 22:55:18.638
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<<
row cache enqueue: session: 8c184270, mode: N, request: X
row cache parent object: address=8ac94c10 type=8(dc_objects)
transaction=8c4b78e4 mode=X flags=002a
status=VALID/UPDATE/-/-/-/-/-/-
data=
...
waiting for 'library cache lock' blocking sess=0x0 seq=8105 wait_time=0
handle address=8b27869c, lock address=8c629710,
10*mode+namespace=15
---------
Metalink is pretty vague and not much help with this error (some say it is a
VMS enqlm problem, others say it was a bug in v7 and early 8.0). I am
running 8.1.6 on Solaris 2.6.
As always, any insight would be greatly appreciated.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steve Adams
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
