RE: root blocker

A few days ago, I posted one. It's a fairly standard one. We found the key to getting the thing to run was to put in the /*+ rule */ hint. It makes the difference from the query running forever (not exaggerating here!) to running in less than five seconds. Yeah boy, the old optimizer still needs a little work.

I don't know if this is what you had in mind. But at least it's an example of using the hint.

set lines 150
set pages 600
col mode_held for a12
col mode_requested for a12

select /*+ rule */ a.osuser waiter, nvl(b.osuser,'NOBODY') blocker, w.lock_type, h.mode_held, w.mode_requested -- w.lock_id1, w.lock_id2
from dba_locks w, dba_locks h, v$session a, v$session b where h.blocking_others = 'Blocking' and h.mode_held != 'None' and h.mode_held != 'Null' and w.mode_requested != 'None' and w.lock_type = h.lock_type
and w.lock_id1 = h.lock_id1 and w.lock_id2 = h.lock_id2 and w.session_id in (select sid from v$session where last_call_et > 100 and sid > 10 and osuser is not null)
and w.session_id = a.sid and h.session_id = b.sid;

> -----Original Message-----
> From: Govind.Arumugam_at_alltel.com [mailto:Govind.Arumugam_at_alltel.com]
> Sent: Monday, May 19, 2003 5: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: Stephen Lee
  INET: Stephen.Lee_at_DTAG.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).