Re: v$lock connect by prior

From: Yong Huang <yong321_at_yahoo.com>
Date: Wed, 10 Oct 2012 07:51:01 -0700 (PDT)
Message-ID: <1349880661.27552.YahooMailClassic_at_web184806.mail.gq1.yahoo.com>

> I am presently using the queries below to find out which session
> locks the others.

I have a SQL that does that, limited to showing blocking locks (if no session is blocking other sessions through enqueue locks, there's no output).

--Session is displayed as <inst_id>.<sid> with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter

            from gv$session where blocking_instance is not null and blocking_session is not null) select lpad(' ',2*(level-1))||waiter lock_tree from  (select * from lk
  union all
  select distinct 'root', blocker from lk   where blocker not in (select waiter from lk)) connect by prior waiter=blocker start with blocker='root';

Sample output is like:

LOCK_TREE

---

2.2097
  2.1022

    2.1943
    2.2082
    3.1879
    3.2054

3.2108
  2.1946
    3.2031
7.1909
  2.1895
  2.1908
...

If it runs slow, it may be because Oracle version is low (e.g. early versions of 10gR2 have performance problem with blocking* columns of v$session), or there're too many sessions involved; materializing the subquery factoring part is unlikely to help.

(from http://yong321.freeshell.org/oranotes/BlockingSessionInRAC.txt)

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 10 2012 - 16:51:01 CEST