7 Replies Latest reply: Apr 19, 2013 12:23 AM by Midhun GT RSS

    Hierarchical query with multiple roots

    Midhun GT
      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
          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
            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
              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
                just a quick thought: have you looked at aggregating from V$SESSION_BLOCKERS instead?

                Gerard
                • 5. Re: Hierarchical query with multiple roots
                  Hoek
                  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
                    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
                      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