This discussion is archived
7 Replies Latest reply: Apr 18, 2013 10:23 PM by Midhun GT RSS

Hierarchical query with multiple roots

Midhun GT Newbie
Currently Being Moderated
Hi,

I'm trying to write a sql query to find blocking lock in database. The idea is to get the blocker in 1st column, comma separated list of waiters in 2nd column and number of waiters in 3rd column.

As of now i use below query to get a tree structure
 
WITH lk AS 
     (SELECT  blocking_session||' (Inst-'||blocking_instance || ')' blocker, 
             SID||' (Inst-'||inst_id || ')' 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'; 

Result:-
===========

 
LOCK_TREE 
-------------------- 

1966 (Inst-1) 
  908 (Inst-1) 
  1906 (Inst-1) 
  1900 (Inst-1) 
981 (Inst-1) 
  921 (Inst-1) 
  937 (Inst-1) 
  962 (Inst-1) 
  1889 (Inst-1) 
  1904 (Inst-1) 
  974 (Inst-1) 
But what i expect is like below. My below query works when there is only one root blocker, but fails when there are multiple root node.
 
WITH lk AS 
     (SELECT blocking_session || '(Inst-' || blocking_instance || ')' blocker, 
             SID || '(Inst-' || inst_id || ')' waiter 
        FROM gv$session 
       WHERE blocking_instance IS NOT NULL AND blocking_session IS NOT NULL) 
SELECT     blocker, SUBSTR (SYS_CONNECT_BY_PATH (waiter, ';'), 2) waiters 
      FROM (SELECT   blocker, waiter, ROW_NUMBER () OVER (ORDER BY waiter) 
                                                                          val 
                FROM lk 
            GROUP BY blocker, waiter) 
     WHERE CONNECT_BY_ISLEAF = 1 
START WITH val = 1 
CONNECT BY PRIOR val = val - 1 

Result:- 
===========


  WAITERS# BLOCKER                                                                                 WAITERS 
---------- --------------------------------------------------------------------------------------- -------------------------------------------------- 
         3 981(Inst-1)                                                                             1904(Inst-1);921(Inst-1);937(Inst-1) 
....lot of duplicates 

expected result:- 
===========

  WAITERS# BLOCKER                                                                                 WAITERS 
---------- --------------------------------------------------------------------------------------- -------------------------------------------------- 
         4 981(Inst-1)                                                                             1904(Inst-1);921(Inst-1);937(Inst-1);974(Inst-1) 
         3 1966(Inst-1)                                                                             908 (Inst-1);1906 (Inst-1);1900 (Inst-1) 
can you please help me correct above query or suggest other ways to archive this result.

Thanks in advance
MidhunGT
  • 1. Re: Hierarchical query with multiple roots
    Hoek Guru
    Currently Being Moderated
    suggest other ways to archive this result.
    For suggestions you could use yourself. please post your database version ( the result of: select * from v$version; ).
    For example: perhaps it can also be done using a recursive WITH clause, but that'll only work on 11gR2...
  • 2. Re: Hierarchical query with multiple roots
    DK2010 Guru
    Currently Being Moderated
    Hi,

    have you tried

    @?/rdbms/admin/utllockt.sql

    It will also give the tree like statucture , and you have to just run :)
  • 3. Re: Hierarchical query with multiple roots
    Midhun GT Newbie
    Currently Being Moderated
    Hi Hoek,

    Here is my database version
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE    10.2.0.4.0      Production
    TNS for Solaris: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production
    Tnx
    MidhunGT
  • 4. Re: Hierarchical query with multiple roots
    gaverill Journeyer
    Currently Being Moderated
    just a quick thought: have you looked at aggregating from V$SESSION_BLOCKERS instead?

    Gerard
  • 5. Re: Hierarchical query with multiple roots
    Hoek Guru
    Currently Being Moderated
    OK, thanks for mentioning, that means recursive WITH is not an option.

    DK2010's idea sounds good to me. Are you able to look at those sql scripts?
    ("A second script, catblock.sql, creates the lock views that utllockt.sql needs, so you must run it before running utllockt.sql.")
    http://docs.oracle.com/cd/E11882_01/server.112/e25494/monitoring002.htm#CEGIBJGH

    Cannot test myself at the moment (but sure will do later today), but here's the connect by from utllockt.sql:
    connect by  prior waiting_session = holding_session
      start with holding_session is null;
    @gaverill: that's exactly what utllockt does, but it also uses another table in between, which has disadvantages (as described in the script).

    I have the feeling that Midhun could use those scripts however, and for example could try to replace that rather unwanted table with a WITH clause as already used.
  • 6. Re: Hierarchical query with multiple roots
    Midhun GT Newbie
    Currently Being Moderated
    Hi Gaverill,

    Is the view available in 10gR2?

    My actual requirement is to define a metrics extension in oem 12c, were
    1. it takes a key column(blocker sid) and
    2. a data column(No of waiters) and
    3. another data column (list of waiter sid)

    evaluate metrics and generate a IM when No of waiters exceeds critical/warning thresholds. My idea is to give as much information as possible to on-call who get the page.


    Any view or table is just fine for this.

    Thanks and Regards,
    MidhunGT
  • 7. Re: Hierarchical query with multiple roots
    Midhun GT Newbie
    Currently Being Moderated
    Hi All,

    Thank you all for your support. I never knew these many ways to see blocking lock in database :)

    Somehow i was able get the desired result for my specific requirement with below query
    sql> WITH lk AS
      2       (SELECT blocking_session || ' (Inst-' || blocking_instance
      3               || ')' blocker,
      4               SID || ' (Inst-' || inst_id || ')' waiter
      5          FROM gv$session
      6         WHERE blocking_instance IS NOT NULL AND blocking_session IS NOT NULL)
      7  SELECT     blocker,
      8             LTRIM
      9                (MAX (SYS_CONNECT_BY_PATH (waiter, ','))KEEP (DENSE_RANK LAST ORDER BY cnt),
     10                 ','
     11                ) AS waiters,
     12             MAX (cnt) waiters#
     13        FROM (SELECT blocker, waiter,
     14                     ROW_NUMBER () OVER (PARTITION BY blocker ORDER BY waiter)
     15                                                                         AS cnt
     16                FROM lk)
     17    GROUP BY blocker
     18  CONNECT BY cnt - 1 = PRIOR cnt AND blocker = PRIOR blocker
     19  START WITH cnt = 1;
    
    BLOCKER         WAITERS                                                                            WAITERS#
    --------------- -------------------------------------------------------------------------------- ----------
    1946 (Inst-1)   1987 (Inst-1),879 (Inst-1),910 (Inst-1)                                                3
    930 (Inst-1)    1919 (Inst-1),1945 (Inst-1),1953 (Inst-1),1983 (Inst-1)                                4
    please advice, if any scope for improvement

    Thanks and Regards,
    MidhunGT

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points