RE: root blocker

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Mon, 19 May 2003 14:52:34 -0800
Message-ID: <F001.0059D3FD.20030519145234@fatcity.com>

Govind,

I get pretty good results (within a few seconds) on a 300Gb, 400+ user Apps database using the SQL below. I had to add the RULE Hint btw, as otherwise it was *BAAAD*.

select /*+ RULE */ lpad(' ',decode(a.request,0,0,3))||a.sid ssid,a.id1

      ,a.lmode,a.request,c.name
 from sys.obj$ c,v$lock b,v$lock a

where a.id1 in ( select id1 from v$lock where lmode = 0 )
  and a.sid = b.sid
  and c.obj# = b.id1
  and b.type = 'TM'

order by a.id1,a.request,b.sid,c.name
/

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

What would you see if you were allowed to look back at your life at the end of your journey in this earth?

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Govind.Arumugam_at_alltel.com [mailto:Govind.Arumugam_at_alltel.com]
> Sent: Monday, May 19, 2003 3:22 PM
> To: Multiple recipients of list ORACLE-L
> Subject: root blocker
>
>
> Hi List,
>
> I am looking for a script that gives the database blocking
> info. All I need is to identify the root blocker.
>
> ( ie if A is blocking B and B is blocking C and C is blocking
> D, then A is the root blocker ). We tried a couple
> of queries using DBA_LOCK and V$LOCK doing self joins but
> they are inordinately slow. The faster the better.
>
> Thanks,
> Govind
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: <Govind.Arumugam_at_alltel.com
> INET: Govind.Arumugam_at_alltel.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

Received on Mon May 19 2003 - 17:52:34 CDT