RE: ERROR: WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=30
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).
Re: UPDATE: RE: ERROR: WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=30
Here's a quick excerpt while awaiting a fax number: (page 44) "If v$sysstat shows a significant number of enqueue waits, then a break- down of the resource types for which these waits have been sustained can be obtained from x$ksqst, or from the APT script enqueue_stats.sql. Unfortunately, x$ksqst does not contains (sic.) any indication of the duration of the waits, so care is needed when interpreting these figures. It is sometimes suggested that ENQUEUE_RESOURCES should be increased to combat enqueue waits. But please note that there is absolutely no substance to this suggestion. Oracle will return an ora-52 or Ora-53 error if it fails to find a free slot in the enqueue resources or enqueue locks fixed arrays respectively. Beyond that, the setting of the ENQUEUE_RESOURCES and _ENQUEUE_LOCKS parameters is unimportant. (page 45) The v$resource_limit view should be used to adjust your settings for the ENQUEUE_RESOURCES and _ENQUEUE_LOCKS parameters to ensure that you will not run out of slots in these arrays. You can afford to be generous, because slots in these arrays only take on the order of 72 bytes and 60 bytes respectively. I like to maintain headroom of at least 20% above the maxiumu utilization ever recorded." You probably want to go to the author's we site, which is http://www.ixora.com.au/ and send mail direct to the author, Steve Adams, at [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Monday, February 12, 2001 2:45 PM > I've continued to research this error and found several references on > various search engines. The most promising seems to point this book by > O'Reilly "Oracle8i Internal Services for Waits, Latches, Locks, and Memory". > There is a section (4.4, pg. 42) which talks about enqueue locks, but I > cannot see the text. > > If anyone has this book, could you give the above section a quick read and > let us know what it says about enqueue locks and possible resource > limits/settings for init params. > > THANKS! > > For Kenneth; My version is; Oracle 8.1.6.0.0 and sun Solaris 2.6, as stated > in my original post. > > > -Original Message- > > From: Glenn Travis [mailto:[EMAIL PROTECTED]] > > Sent: Monday, February 12, 2001 11:16 AM > > To: Oracledba@Lazydba. Com; [EMAIL PROTECTED] > > Subject: ERROR: WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=30 > > > > > > 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. > > > > > > > > Think you know someone who can answer the above question? Forward > > it to them! > > to unsubscribe, send a blank email to [EMAIL PROTECTED] > > to subscribe send a blank email to [EMAIL PROTECTED] > > Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Glenn Travis > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tom Pall 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 messa
Re: UPDATE: RE: ERROR: WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=30
Please send me ([EMAIL PROTECTED]) a fax number. Too much to type in, don't have ready access to a scanner. - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Monday, February 12, 2001 2:45 PM > I've continued to research this error and found several references on > various search engines. The most promising seems to point this book by > O'Reilly "Oracle8i Internal Services for Waits, Latches, Locks, and Memory". > There is a section (4.4, pg. 42) which talks about enqueue locks, but I > cannot see the text. > > If anyone has this book, could you give the above section a quick read and > let us know what it says about enqueue locks and possible resource > limits/settings for init params. > > THANKS! > > For Kenneth; My version is; Oracle 8.1.6.0.0 and sun Solaris 2.6, as stated > in my original post. > > > -Original Message- > > From: Glenn Travis [mailto:[EMAIL PROTECTED]] > > Sent: Monday, February 12, 2001 11:16 AM > > To: Oracledba@Lazydba. Com; [EMAIL PROTECTED] > > Subject: ERROR: WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=30 > > > > > > 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. > > > > > > > > Think you know someone who can answer the above question? Forward > > it to them! > > to unsubscribe, send a blank email to [EMAIL PROTECTED] > > to subscribe send a blank email to [EMAIL PROTECTED] > > Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Glenn Travis > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tom Pall 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).
RE: ERROR: WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=30
We have had a problem with hangs in 8.1.6. The symptom we see is an extremely high version_count value in many of the rows of the v$sqlarea table at the time of the hang. v$session_waits also has many sessions waiting on pins of the shared pool. Basically, in our case, the shared pool was full of sql that should have been sharable. I am not sure if this is the same problem, but it sounds similar. Try doing a search in MetaLink on timed_statistics and/or sqlexec_progression_cost if you think this could be your problem. > -Original Message- > From: Glenn Travis [SMTP:[EMAIL PROTECTED]] > Sent: Monday, February 12, 2001 10:16 AM > To: Multiple recipients of list ORACLE-L > Subject: ERROR: WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=30 > > 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: Glenn Travis > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Elliott, Patrick 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).
UPDATE: RE: ERROR: WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=30
I've continued to research this error and found several references on various search engines. The most promising seems to point this book by O'Reilly "Oracle8i Internal Services for Waits, Latches, Locks, and Memory". There is a section (4.4, pg. 42) which talks about enqueue locks, but I cannot see the text. If anyone has this book, could you give the above section a quick read and let us know what it says about enqueue locks and possible resource limits/settings for init params. THANKS! For Kenneth; My version is; Oracle 8.1.6.0.0 and sun Solaris 2.6, as stated in my original post. > -Original Message- > From: Glenn Travis [mailto:[EMAIL PROTECTED]] > Sent: Monday, February 12, 2001 11:16 AM > To: Oracledba@Lazydba. Com; [EMAIL PROTECTED] > Subject: ERROR: WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=30 > > > 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. > > > > Think you know someone who can answer the above question? Forward > it to them! > to unsubscribe, send a blank email to [EMAIL PROTECTED] > to subscribe send a blank email to [EMAIL PROTECTED] > Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis 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).
RE: ERROR: WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=30
What platform and oracle version are you on??? We had a similar problem where I work on a server running Solaris 2.6 and oracle 8.1.6.2. We were getting the same error "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK..." and the problem seemed to be caused by a fast refresh snapshot. The snapshot was on a large high activity table. The snapshot refreshed "fast refresh" each 10 min via rowid. Even though we do have oracle support it was too little too late and we eventually had to change the snapshots to full refresh daily to avoid the lockups that were occurring. The server in question is an Ultra-4 with 4 CPU's and 4 Gig of physical memory. Another symptom that I noticed is that the snapshot log was not being truncated as it should and would continue to grow and grow. Any of this sound familiar? I would be interested in hearing about any resolution you find. Ken. -Original Message- Sent: Monday, February 12, 2001 11:16 AM To: Multiple recipients of list ORACLE-L 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: Glenn Travis 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fowler, Kenneth R 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).
