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 breakdown  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 steve.adams_at_ixora.com.au.

• Original Message ----- To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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:Glenn.Travis_at_wcom.com]
> > Sent: Monday, February 12, 2001 11:16 AM
> > To: Oracledba_at_Lazydba. Com; ORACLE-L_at_fatcity.com
> > 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 oracledba-unsubscribe_at_LAZYDBA.com
> > to subscribe send a blank email to oracledba-subscribe_at_LAZYDBA.com
> > 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: Glenn.Travis_at_wcom.com
>
> 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: ListGuru_at_fatcity.com (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: tom_at_cdproc.com

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: ListGuru_at_fatcity.com (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).