Forum Stats

  • 3,734,034 Users
  • 2,246,862 Discussions
  • 7,857,003 Comments

Discussions

Hierchical wth null and holes

bpolarski
bpolarski Member Posts: 319
edited April 2011 in SQL & PL/SQL
I am trying to mine the DBA_HIST_ACTIVE_SESS_HISTORY. The column that interrest me are 'sample_time','session_sid','session_serial#', 'blocking_session' and blocking_Session_serial#'.

The purpose is to list per sample time the blocking session tree (and related data here out of scope).
When the blocking last several minutes, all blocked session are sampled and the data is rather relevant
for troubleshooting, thought not absolute. But the data model is not easy for you may have holes.
I have reproduced a simplified version of the data pattern as
it is in DBA_HIST_ACTIVE_SESS_HISTORY and what I want to achieve.

-- grp stands for time_sample and it is the base grouping
-- sid  stands for session 
-- bsid stands for blocking session, this is a column of the SID row 
--         but reference the same type of object (session).
--         bsid itself may be present (or not) as a row.
-- We don't consider the serial in this simplified model

create table hist_table ( grp varchar2(1) , sid number, bsid number ) ;
insert into hist_table values ( 'A', 10, 100 ) ;
insert into hist_table values ( 'A',  20 , null ) ;
insert into hist_table values ( 'A', 30,  40  ) ;
insert into hist_table values ( 'A', 40,  50  ) ;
insert into hist_table values ('A', 50, null ) ;
insert into hist_table values ( 'B',  10, 100 ) ;
insert into hist_table values ( 'B',20 , null ) ;



SQL> select * from hist_table ;

G        SID       BSID
- ---------- ----------
A         10        100
A         20
A         30         40
A         40         50
A         50
B         10        100
B         20


We want : 

grp  Session
---   ------------------- 
  A    10
  A        100
  A    20
  A    30
  A       40
  A         50
  B   10
  B       100
  B   20



-- The difficulty is that blocking session 100 has no dedicated row.
-- Session 20 has no blocking session.
-- Sessions 30,40,50 are all linked in a hierarchic pattern.

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,497 Red Diamond
    Accepted Answer
    Hi,
    bpolarski wrote:
    ... create table hist_table ( grp varchar2(1) , sid number, bsid number ) ;
    insert into hist_table values ( 'A', 10, 100 ) ; ...
    Thanks for including this; it's very helpful.
    ... -- The difficulty is that blocking session 100 has no dedicated row.
    -- Session 20 has no blocking session.
    -- Sessions 30,40,50 are all linked in a hierarchic pattern.
    Exactly! CONNECT BY queries were designed for cases where you want to display the same column from every row. Sometimes (and this is one of those times) you have to manipulate your data so that it fits that pattern. In the query below, every id (either a sid or bsid) is in the node_id column:
    WITH	got_roots	AS
    (
    	SELECT	grp
    	, 	sid	AS parent_id
    	, 	bsid	AS node_id
    	FROM	hist_table
    	WHERE	bsid	IS NOT NULL
    		--
        UNION ALL
    		--
    	SELECT	grp
    	,	NULL	AS parent_id
    	,	sid	AS node_id
    	FROM	hist_table
    	WHERE	(grp, sid)  NOT IN (
    					SELECT	grp
    					,	bsid
    					FROM	hist_table
    					WHERE	bsid	IS NOT NULL
    				   )
    )
    SELECT	grp
    ,	LPAD ( ' '
    	     , 3 * (LEVEL - 1)
    	     )	|| node_id	AS session_txt
    FROM	got_roots
    START WITH	parent_id	IS NULL
    CONNECT BY	parent_id	= PRIOR node_id
    	AND	grp		= PRIOR grp
    ;
    Output:
    GRP SESSION_TXT
    --- --------------------
    A   10
    A      100
    A   20
    A   30
    A      40
    A         50
    B   10
    B      100
    B   20

Answers

  • hm
    hm Member Posts: 1,175
    Does this help?
    with 
    hist_table as
    (
    select 'A' GRP,         10  sid,      100 bsid from dual union all
    select 'A',         20,   null from dual union all
    select 'A',         30,         40 from dual union all
    select 'A',         40,         50 from dual union all
    select 'A',         50, null from dual union all
    select 'B',         10,        100 from dual union all
    select 'B',         20, null from dual
    )
    select grp, 
             max(p) keep (dense_rank last order by length(p))  -- longest path per GRP and SID (take a look at the group by clause)
    from
    (
    select grp, sys_connect_by_path(sid,' ')||decode(bsid,null,null,' '||bsid) p, sid, bsid
    from hist_table 
    where connect_by_isleaf=1
    connect by prior bsid=sid and prior grp=grp
    )
    group by grp, sid
    order by grp
  • bpolarski
    bpolarski Member Posts: 319
    not really as for each row I intend to display more attribute columns like events and sqlid.
    Meantime I found I can supplement with missing row to help the hierrachical :
    -- the following introduces the missing rows. This is supposed to help build the hierarchical query:
    
        with v as ( select grp,bsid sid , -1 bsid
                             from hist_table a
                             where
                                    not exists (select null
                                                 from hist_table where sid=a.bsid  )
                                and bsid is not null
                     union select * from hist_table
                     )
       select * from v
    SQL> /
    
    G        SID       BSID
    - ---------- ----------
    A         10        100
    A         20
    A         30         40
    A         40         50
    A         50
    A        100         -1
    B         10        100
    B         20
    B        100         -1
    but I have not found a proper way to exploit this. The best I could procude have a cycle error:
    with v as ( select grp,bsid sid , -1 bsid
                         from hist_table a
                         where
                                not exists (select null
                                             from hist_table where sid=a.bsid  )
                            and bsid is not null
                 union select * from hist_table
                 )
    select distinct * from (
    select  grp , lpad(' ', 2*level)|| sid sid , sid fsid, bsid, level  flevel,
         connect_by_root sid froot, SYS_CONNECT_BY_PATH(sid,':') fpath
      from v
        connect by nocycle   sid = prior bsid    --and   grp = prior  grp
    )  where bsid is null or (bsid,flevel) not in  (select -1,1 from dual)
    order by grp, froot
    /
    SQL> /
    
    G SID                                  FSID       BSID     FLEVEL      FROOT FPATH
    - ------------------------------ ---------- ---------- ---------- ---------- ------------------------------
    A   10                                   10        100          1         10 :10
    A     100                               100         -1          2         10 :10:100
    A   20                                   20                     1         20 :20
    A   30                                   30         40          1         30 :30
    A     40                                 40         50          2         30 :30:40
    A       50                               50                     3         30 :30:40:50
    A   40                                   40         50          1         40 :40
    A     50                                 50                     2         40 :40:50
    A   50                                   50                     1         50 :50
    B   10                                   10        100          1         10 :10
    B     100                               100         -1          2         10 :10:100
    B   20                                   20                     1         20 :20
     
    and we see that 40 50 appears twice in the hierarchiy :(
    
    Still working on a solution
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,497 Red Diamond
    Accepted Answer
    Hi,
    bpolarski wrote:
    ... create table hist_table ( grp varchar2(1) , sid number, bsid number ) ;
    insert into hist_table values ( 'A', 10, 100 ) ; ...
    Thanks for including this; it's very helpful.
    ... -- The difficulty is that blocking session 100 has no dedicated row.
    -- Session 20 has no blocking session.
    -- Sessions 30,40,50 are all linked in a hierarchic pattern.
    Exactly! CONNECT BY queries were designed for cases where you want to display the same column from every row. Sometimes (and this is one of those times) you have to manipulate your data so that it fits that pattern. In the query below, every id (either a sid or bsid) is in the node_id column:
    WITH	got_roots	AS
    (
    	SELECT	grp
    	, 	sid	AS parent_id
    	, 	bsid	AS node_id
    	FROM	hist_table
    	WHERE	bsid	IS NOT NULL
    		--
        UNION ALL
    		--
    	SELECT	grp
    	,	NULL	AS parent_id
    	,	sid	AS node_id
    	FROM	hist_table
    	WHERE	(grp, sid)  NOT IN (
    					SELECT	grp
    					,	bsid
    					FROM	hist_table
    					WHERE	bsid	IS NOT NULL
    				   )
    )
    SELECT	grp
    ,	LPAD ( ' '
    	     , 3 * (LEVEL - 1)
    	     )	|| node_id	AS session_txt
    FROM	got_roots
    START WITH	parent_id	IS NULL
    CONNECT BY	parent_id	= PRIOR node_id
    	AND	grp		= PRIOR grp
    ;
    Output:
    GRP SESSION_TXT
    --- --------------------
    A   10
    A      100
    A   20
    A   30
    A      40
    A         50
    B   10
    B      100
    B   20
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I like recursive with clause B-)
    col Session for a30
    
    with hist_table(grp,sid,bsid) as(
    select 'A',10, 100 from dual union all
    select 'A',20,null from dual union all
    select 'A',30,  40 from dual union all
    select 'A',40,  50 from dual union all
    select 'A',50,null from dual union all
    select 'B',10, 100 from dual union all
    select 'B',20,null from dual),
    rec(grp,sid,bsid,LV) as(
    select grp,sid,bsid,1
      from hist_table a
     where not exists(select 1 from hist_table b
                       where b.grp=a.grp
                         and b.bsid=a.sid)
    union all
    select a.grp,a.bsid,b.bsid,a.LV+1
      from rec a Left Join hist_table b
        on a.grp  = b.grp
       and a.bsid = b.sid
     where a.bsid is not null)
    SEARCH DEPTH FIRST BY grp,sid SET rn
    select grp,LPad(to_char(SID),LV*4,' ') as "Session"
      from rec order by rn;
    
    GRP  Session
    ---  ------------
    A      10
    A         100
    A      20
    A      30
    A          40
    A              50
    B      10
    B         100
    B      20
    My SQL articles of OTN-Japan B-)
    http://www.oracle.com/technetwork/jp/articles/otnj-sql-image3-1-323602-ja.html
    Aketi Jyuuzou
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I like hierarchical Query ;-)
    I think that in this case,recursive with clause is simpler than hierarchical Query
    with hist_table(grp,sid,bsid) as(
    select 'A',10, 100 from dual union all
    select 'A',20,null from dual union all
    select 'A',30,  40 from dual union all
    select 'A',40,  50 from dual union all
    select 'A',50,null from dual union all
    select 'B',10, 100 from dual union all
    select 'B',20,null from dual),
    tmp as(
    select grp,sid,bsid
      from hist_table
    union all
    select grp,bsid,null
      from hist_table a
     where bsid is not null
       and not exists(select 1 from hist_table b
                       where b.grp=a.grp
                         and b.sid=a.bsid))
    select grp,LPad(to_char(SID),Level*4,' ') as "Session"
      from tmp a
    start with not exists(select 1 from tmp b
                           where b.grp=a.grp
                             and b.bsid=a.sid)
    connect by prior grp = grp
           and prior bsid = sid;
    Aketi Jyuuzou
  • bpolarski
    bpolarski Member Posts: 319
    Aketi : I don't understand how the recursive way works but it works. The syntax is strange and never saw something like that before. As of the logic behind and readability of the code, I would not recommend it to any customer. I am quite confident very few people will be able to understand it right away, at least in the Oracle world.
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,497 Red Diamond
    Hi,
    bpolarski wrote:
    Aketi : I don't understand how the recursive way works but it works. The syntax is strange and never saw something like that before. As of the logic behind and readability of the code, I would not recommend it to any customer. I am quite confident very few people will be able to understand it right away, at least in the Oracle world.
    Recursive WITH clauses are new in Oracle 11.2, and they are a great way to do all the things that creative and clever CONNECT BY queries did in earlier versions. ("Clever" is not complimentary when describing programming.) It looks like your application can use recursive WITH clauses, so it's probably worth while for you to take a look at them.
    Very few Oracle SQL programmers understand CONNECT BY queries; that's no reason for you not to use them. Why should it be a reason not to use some other tool?
  • bpolarski
    bpolarski Member Posts: 319
    edited April 2011
    Here is my work derieved on above answers. thanks to all who devoted time and helped me.

    Bernard Polarski
    http://www.smenu.org
    set lines 190 page 82
    col user_id head 'Usr|id' format 999
    col event head 'Event' format a30
    col usr_sqlid format a35
    col fsid for a14 head 'Sid'
    col sid_sql for a35 head 'Session sql text'
    col bser for 99999 head 'Block|sess|serl#'
    col ser for 99999 head 'Serl#'
    col file# for 999 head 'Fl#'
    col block# for 9999999 head 'block#'
    col obj# for 999999 head 'obj#'
    col sample_time for a8 head 'Time'
    break on instance_number on sample_time on report
    col wait_time for 9990.00 head 'Prev |wait| time(s)' justify c
    
    prompt  Prev wait : any value > 0 means SQL currently running on CPU, numeric refer to wait(secs) before this run
    prompt
    
    with fview as (
        select
               blocking_session as session_id, blocking_SESSION_SERIAL# as SESSION_SERIAL# ,
               -1 as blocking_session, -1 as blocking_SESSION_SERIAL#,
               null sql_id, null event , sample_time, null obj# , null file# ,
               null block# ,  null wait_time, null user_id, null  instance_number
        from DBA_HIST_ACTIVE_SESS_HISTORY a
             where  1=1  and snap_id = '5884'   and dbid= '810902110'  and blocking_session is not null
                and not exists (select null
                                       from DBA_HIST_ACTIVE_SESS_HISTORY
                                 where
                                        session_id=a.blocking_session
                                  and   session_serial#=a.blocking_session_serial#  and snap_id = '5884'   and dbid= '810902110'
                                  and   snap_id=a.snap_id
                                  and   dbid=a.dbid
              )
       union
       select
             session_id, SESSION_SERIAL#, blocking_session, blocking_session_serial#,
             sql_id, event, sample_time,  CURRENT_OBJ# obj#, CURRENT_FILE# file#,
             CURRENT_BLOCK# block#,  wait_time/100 wait_time, user_id, instance_number
       from DBA_HIST_ACTIVE_SESS_HISTORY a
            where 1=1  and snap_id = '5884'   and dbid= '810902110'
       )
    select
           a.instance_number, to_char(a.sample_time,'HH24:MI:SS') sample_time,
           lpad(' ', 2*level)||session_id  fsid,
           SESSION_SERIAL# ser, user_id, a.sql_id,
           decode(a.wait_time,0,' -wait-', to_char(wait_time,'99990.00')) wait_time,
           event  , a.file#, a.obj#, block#, substr(t.sql_text,1,35) sid_sql --,  blocking_session
    from   ( select
                      instance_number, sample_time,
                      session_id, SESSION_SERIAL# , sql_id, event,  obj#, file#, block#,
                      wait_time, user_id, blocking_session, blocking_SESSION_SERIAL#
               from fview a
               start with not exists (select 1
                                      from
                                         fview b
                                      where
                                              b.sample_time            = a.sample_time
                                         and  b.blocking_session       = a.session_id
                                         and  blocking_session_serial# = a.SESSION_SERIAL# )
              connect by prior  sample_time              = sample_time
                          and   prior  blocking_session         = session_id
                          and   prior  blocking_SESSION_SERIAL# = SESSION_SERIAL#
              )a,
              dba_hist_sqltext t
        where
              t.sql_id (+) = a.sql_id
              connect by nocycle
                               prior session_id = blocking_session
                             and   prior session_serial# = blocking_session_serial#
                             and  sample_time= prior sample_time
    /
    
    
                                                             Prev
    In                                  Usr                 wait
    st  Time     Sid             Serl#   id SQL_ID         time(s)  Event                           Fl#    obj#   block# Session sql text
    --- -------- -------------- ------ ---- ------------- --------- ------------------------------ ---- ------- -------- -----------------------------------
      2 09:00:01   1478          45824   48 fph5dfjrr6spu      2.78                                  58   77692   150780 SELECT  bf.bf_id, bf.status, bs.sho
                   1482          34169   48 6gpfyfz414w80      2.44                                  53   78348   713894 select field1 from ( SELECT field1
      1 09:00:03   1519          12157   93 96swh01bpcv54  -wait-   gc cr multi block request        58   77692    13920 SELECT COUNT (O.BF_ID) FROM IBS6_EB
                   1515          29566    0                -wait-   log switch/archive                0      -1        0
      2 09:00:11   1482          34169   48 4ks1swd743v6h  -wait-   db file sequential read          52   77987   294036 INSERT into SUMMTRANSACTIONLVL(SUMM
      1 09:00:13   1549          25308    0 fjtb8ybf2g817  -wait-   control file sequential read      0      -1        0 select nvl(max(cpmid),0) from x$kcc
                   1600          15870   48 07x2k1s911cs1      2.69                                  52   78010   837991 SELECT  bf.bf_id, bf.status, bs.sho
        09:00:23   1519          12157   93 96swh01bpcv54  -wait-   gc cr multi block request        52   77692   165451 SELECT COUNT (O.BF_ID) FROM IBS6_EB
                   1550          18167   48 69u51auyg39c0      0.01                                  44   77975    14854 select count(1) from r4rrequest r,
      2 09:00:31   1518          30594    0                -wait-   db file sequential read           0      -1        0
      1 09:00:33   1563           7961    0                -wait-   enq: WF - contention              0      -1        0
      2 09:01:11   1478          45824   48 0wva63ycbrqxn      0.02                                   0      -1        0 SELECT cp.counterparty_id, cp.updat
      1 09:01:14   1584          55897   48 dj4vc1pdbvy98  -wait-   db file sequential read          49   78106   224094 select unique eb1.bankcustomer_id,
                   1655              1    0                29279.20                                   0      -1        0
      2 09:01:31   1604           3421   48 cdg898bazxj0t      0.01                                  47   77994   154271    SELECT unique ucp.USERCONTRACTPROF
        09:01:42   1508          28278   48 fgacpsh4cctb0  -wait-   gc current block busy            49   78367   258887 INSERT into USERMESSAGE(USERMESSAGE
                   1480          14992   48 25wncxupku5ty      0.01                                   5   78021    28828 SELECT        aubu.ebuser_id  FROM
                     1617        62553   48 0g7y44z6btjh0  -wait-   enq: TX - row lock contention     5   78021    28828 update usermessagepkiuser set useri
                     1615        18792   48 0g7y44z6btjh0  -wait-   enq: TX - row lock contention     5   78021    28828 update usermessagepkiuser set useri
                     1613         6428   48 0g7y44z6btjh0  -wait-   enq: TX - row lock contention     5   78021    28828 update usermessagepkiuser set useri
                     1609        18013   48 0g7y44z6btjh0  -wait-   enq: TX - row lock contention     5   78021    28828 update usermessagepkiuser set useri
                     1579        37687   48 0g7y44z6btjh0  -wait-   enq: TX - row lock contention     5   78021    28828 update usermessagepkiuser set useri
                     1572         4128   48 0g7y44z6btjh0  -wait-   enq: TX - row lock contention     5   78021    28828 update usermessagepkiuser set useri
                     1520        59235   48 0g7y44z6btjh0  -wait-   enq: TX - row lock contention     5   78021    28828 update usermessagepkiuser set useri
                     1501        20868   48 0g7y44z6btjh0  -wait-   enq: TX - row lock contention     5   78021    28828 update usermessagepkiuser set useri
                     1478        45824   48 0g7y44z6btjh0  -wait-   enq: TX - row lock contention     5   78021    28828 update usermessagepkiuser set useri
                     1482        34169   48 0g7y44z6btjh0  -wait-   enq: TX - row lock contention     5   78021    28828 update usermessagepkiuser set useri
                     1507         4886   48 0g7y44z6btjh0  -wait-   enq: TX - row lock contention     5   78021    28828 update usermessagepkiuser set useri
                     1566        39358   48 0g7y44z6btjh0  -wait-   enq: TX - row lock contention     5   78021    28828 update usermessagepkiuser set useri
                     1578        20585   48 0g7y44z6btjh0  -wait-   enq: TX - row lock contention     5   78021    28828 update usermessagepkiuser set useri
                     1597        48687   48 0g7y44z6btjh0  -wait-   enq: TX - row lock contention     5   78021    28828 update usermessagepkiuser set useri
                     1603        48768   48 0g7y44z6btjh0  -wait-   enq: TX - row lock contention     5   78021    28828 update usermessagepkiuser set useri
                     1604         3421   48 0g7y44z6btjh0  -wait-   enq: TX - row lock contention     5   78021    28828 update usermessagepkiuser set useri
This discussion has been closed.