Re: A Tale of Two SQLs

From: <solbeach_at_cox.net>
Date: Mon, 3 May 2004 16:29:42 -0400
Message-Id: <20040503202942.BAPG16848.fed1rmmtao08.cox.net@smtp.west.cox.net>

I had a similar issue a couple of months ago. My problem was that a few "popular" tables, which are regularly updated/accessed by most of the application had INITRANS too low & sessions were going into ENQUEUE wait state.

Run this simple SQL the next time a "slowdown" occurs...

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,

         id1, id2, lmode, request, type
    FROM V$LOCK
   WHERE (id1, id2, type) IN

(SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request
/

We had folks to started an UPDATE (got a lock) and then literally went to lunch to 90 minutes! Other sessions would hang quietly waiting for the resource.

HTH & YMMV!

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Mon May 03 2004 - 15:28:58 CDT