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.