Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Hierchical wth null and holes

bpolarskiApr 6 2011 — edited Apr 7 2011
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.
This post has been answered by Frank Kulash on Apr 6 2011
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 5 2011
Added on Apr 6 2011
8 comments
1,810 views