Updated on : 14 Jul 2019, since the original version released on 12Jul2019 had #2 bugs, which is fixed now
Objective: On our day to day DBA life, there are chances we might end up needing the db session block chain from the past, if it is current or live issue - then you have V$WAIT_CHAINS to see the block chain live.But if it is from the past, how do you pull it?
DBA_HIST_ACTIVE_SESS_HISTORY stores the information, but it needs some effort in mapping the block chain with the volume of data we have to process.
Instead that, I just used oracle's hierarchial query (thanks to Tanel Poder for sparking the idea in me) and some join tweeks to come up with the below sqls.
Version tested: 11.2.0.1
SQL1 - use the below row source [this query gives the sample sorted session, what it is doing along with what is its blocker and what is that doing, in case there is no information, it marks that session as IDLE - with username marked as NOSESSION]
with dash as
(
select /*+ parallel(4) materialize */
*
from dba_hist_active_sess_history
where cast(SAMPLE_TIME as date) between to_date('11-JUL-2019 11:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('11-JUL-2019 11:10:00','DD-MON-YYYY HH24:MI:SS')
),
do as
(
select /*+ parallel(4) materialize */
*
from dba_objects
where object_id in (select /*+ parallel(2) */ distinct(CURRENT_OBJ#) from dash)
),
du as
(
select user_id,username
from dba_users
where user_id in (select /*+ parallel(2) */ distinct(user_id) from dash)
),
dashmap as
(
select /*+ parallel(4) materialize */ a.INSTANCE_NUMBER ino
,a.SAMPLE_TIME sampt
,a.SAMPLE_ID sampi
,a.SESSION_ID sid
,a.SESSION_SERIAL# s#
,a.SESSION_TYPE stype
,decode(a.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(a.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)) pgm
,b.username iuser
,a.SQL_ID sql
,a.EVENT
,a.WAIT_CLASS wc
,a.TIME_WAITED tw
,a.MACHINE mach
,e.owner own
,e.object_type obty
,e.object_name obnm
,e.subobject_name suobnm
,a.BLOCKING_INST_ID
,a.BLOCKING_SESSION
,a.BLOCKING_SESSION_SERIAL#
,nvl(c.instance_number,99) bino
,nvl(c.session_id,9999999) bsid
,nvl(c.SESSION_SERIAL#,9999999) bs#
,nvl(c.SAMPLE_ID,99) bsample
,nvl(d.username,'NOSESSION') buser
,nvl(c.sql_id,'NOINFO') bsql
,nvl(decode(c.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(c.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)),'NOINFO') bpgm
,nvl(c.event,'NOINFO') bevent
,nvl(c.WAIT_CLASS,'NOINFO') bwc
,nvl(c.TIME_WAITED,9999999) btw
,nvl(c.MACHINE,'NOINFO') bmach
,nvl(f.owner,'NOINFO') bown
,nvl(f.object_type,'NOINFO') bobty
,nvl(f.object_name,'NOINFO') bobnm
,nvl(f.subobject_name,'NOINFO') bsuobnm
from dash a
inner join du b on a.user_id=b.user_id
left join do e on a.current_obj#=e.object_id
left join (dash c
inner join du d on c.user_id=d.user_id
left join do f on c.current_obj#=f.object_id
) on (
a.BLOCKING_INST_ID=c.INSTANCE_NUMBER
and a.SAMPLE_ID=c.SAMPLE_ID
and a.BLOCKING_SESSION=c.SESSION_ID
and a.BLOCKING_SESSION_SERIAL#=c.SESSION_SERIAL#
)
)
select * from dashmap
order by 1,2,3,4,5;
Output:
Query 2: To get a map of block chain and other session details [this query gives you a over view of all the sessions - i) sessions with no blockers ii) sessions with blockers available in ash. The only caveat is you wont get the sessions with blockers, but the blockers arent visible in ash, see the hierarchial query looks for 2 conditions, a) sessions with no blockers and b) sessions with blockers & such blockers are available in ash, I dont know today if there is way to get those sessions with blockers which arent available/visible in ash using hierarchial query]
with dash as
(
select /*+ parallel(4) materialize */
*
from dba_hist_active_sess_history
where cast(SAMPLE_TIME as date) between to_date('11-JUL-2019 11:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('11-JUL-2019 11:10:00','DD-MON-YYYY HH24:MI:SS')
),
do as
(
select /*+ parallel(4) materialize */
*
from dba_objects
where object_id in (select /*+ parallel(2) */ distinct(CURRENT_OBJ#) from dash)
),
du as
(
select /*+ materialize */ user_id,username
from dba_users
where user_id in (select /*+ parallel(2) */ distinct(user_id) from dash)
),
dashmap as
(
select /*+ parallel(4) materialize */ a.INSTANCE_NUMBER ino
,a.SAMPLE_TIME sampt
,a.SAMPLE_ID sampi
,a.SESSION_ID sid
,a.SESSION_SERIAL# s#
,a.SESSION_TYPE stype
,decode(a.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(a.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)) pgm
,b.username iuser
,a.SQL_ID sql
,a.EVENT
,a.WAIT_CLASS wc
,a.TIME_WAITED tw
,a.MACHINE mach
,e.owner own
,e.object_type obty
,e.object_name obnm
,e.subobject_name suobnm
,a.BLOCKING_INST_ID abino
,a.BLOCKING_SESSION absid
,a.BLOCKING_SESSION_SERIAL# abs#
,nvl(c.instance_number,99) bino
,nvl(c.session_id,9999999) bsid
,nvl(c.SESSION_SERIAL#,9999999) bs#
,nvl(c.SAMPLE_ID,99) bsample
,nvl(d.username,'NOSESSION') buser
,nvl(c.sql_id,'NOINFO') bsql
,nvl(decode(c.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(c.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)),'NOINFO') bpgm
,nvl(c.event,'NOINFO') bevent
,nvl(c.WAIT_CLASS,'NOINFO') bwc
,nvl(c.TIME_WAITED,9999999) btw
,nvl(c.MACHINE,'NOINFO') bmach
,nvl(f.owner,'NOINFO') bown
,nvl(f.object_type,'NOINFO') bobty
,nvl(f.object_name,'NOINFO') bobnm
,nvl(f.subobject_name,'NOINFO') bsuobnm
from dash a
inner join du b on a.user_id=b.user_id
left join do e on a.current_obj#=e.object_id
left join (dash c
inner join du d on c.user_id=d.user_id
left join do f on c.current_obj#=f.object_id
) on (
a.BLOCKING_INST_ID=c.INSTANCE_NUMBER
and a.SAMPLE_ID=c.SAMPLE_ID
and a.BLOCKING_SESSION=c.SESSION_ID
and a.BLOCKING_SESSION_SERIAL#=c.SESSION_SERIAL#
)
)
select /* parallel(4) */
sampt
,ino
,sid
,s#
,stype
,pgm
,iuser
,sql
,event
,wc
,tw
,mach
,own
,obty
,obnm
,suobnm
,bino
,bsid
,bs#
,buser
,bsql
,bpgm
,bevent
,bwc
,btw
,bmach
,bown
,bobty
,bobnm
,bsuobnm
,RPAD('.', (level-1)*2, '.') ||ino||'~'||sid||'~'||s#||'~'||iuser AS tree
,level
,CONNECT_BY_ROOT (ino||'~'||sid||'~'||s#||'~'||iuser) AS root_id
,LTRIM(SYS_CONNECT_BY_PATH((ino||'~'||sid||'~'||s#||'~'||iuser), '->'),'->') AS path
,CONNECT_BY_ISLEAF AS leaf
,CONNECT_BY_ISCYCLE AS cycle
from dashmap
START WITH (abino is null
and absid is null
and abs# is null)
CONNECT BY NOCYCLE prior (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser) = (sampi||'~'||bino||'~'||bsid||'~'||bs#||'~'||buser)
ORDER SIBLINGS BY (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser)
;
Output:
Query 3: To filter out only the session blocking chain . Remeber to read the conditions the query works on from last (query#2) [this query will filter out only blocked sessions with their blocker details, kind of chain identity]
with dash as
(
select /*+ parallel(4) materialize */
*
from dba_hist_active_sess_history
where cast(SAMPLE_TIME as date) between to_date('11-JUL-2019 11:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('11-JUL-2019 12:05:00','DD-MON-YYYY HH24:MI:SS')
),
do as
(
select /*+ parallel(4) materialize */
*
from dba_objects
where object_id in (select /*+ parallel(2) */ distinct(CURRENT_OBJ#) from dash)
),
du as
(
select /*+ materialize */ user_id,username
from dba_users
where user_id in (select /*+ parallel(2) */ distinct(user_id) from dash)
),
dashmap as
(
select /*+ parallel(4) materialize */ a.INSTANCE_NUMBER ino
,a.SAMPLE_TIME sampt
,a.SAMPLE_ID sampi
,a.SESSION_ID sid
,a.SESSION_SERIAL# s#
,a.SESSION_TYPE stype
,decode(a.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(a.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)) pgm
,b.username iuser
,a.SQL_ID sql
,a.EVENT
,a.WAIT_CLASS wc
,a.TIME_WAITED tw
,a.MACHINE mach
,e.owner own
,e.object_type obty
,e.object_name obnm
,e.subobject_name suobnm
,a.BLOCKING_INST_ID abino
,a.BLOCKING_SESSION absid
,a.BLOCKING_SESSION_SERIAL# abs#
,nvl(c.instance_number,99) bino
,nvl(c.session_id,9999999) bsid
,nvl(c.SESSION_SERIAL#,9999999) bs#
,nvl(c.SAMPLE_ID,99) bsample
,nvl(d.username,'NOSESSION') buser
,nvl(c.sql_id,'NOINFO') bsql
,nvl(decode(c.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(c.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)),'NOINFO') bpgm
,nvl(c.event,'NOINFO') bevent
,nvl(c.WAIT_CLASS,'NOINFO') bwc
,nvl(c.TIME_WAITED,9999999) btw
,nvl(c.MACHINE,'NOINFO') bmach
,nvl(f.owner,'NOINFO') bown
,nvl(f.object_type,'NOINFO') bobty
,nvl(f.object_name,'NOINFO') bobnm
,nvl(f.subobject_name,'NOINFO') bsuobnm
from dash a
inner join du b on a.user_id=b.user_id
left join do e on a.current_obj#=e.object_id
left join (dash c
inner join du d on c.user_id=d.user_id
left join do f on c.current_obj#=f.object_id
) on (
a.BLOCKING_INST_ID=c.INSTANCE_NUMBER
and a.SAMPLE_ID=c.SAMPLE_ID
and a.BLOCKING_SESSION=c.SESSION_ID
and a.BLOCKING_SESSION_SERIAL#=c.SESSION_SERIAL#
)
)
select *
from
(
select /* parallel(4) materialize */
sampt
,ino
,sid
,s#
,stype
,pgm
,iuser
,sql
,event
,wc
,tw
,mach
,own
,obty
,obnm
,suobnm
,bino
,bsid
,bs#
,buser
,bsql
,bpgm
,bevent
,bwc
,btw
,bmach
,bown
,bobty
,bobnm
,bsuobnm
,RPAD('.', (level-1)*2, '.') ||ino||'~'||sid||'~'||s#||'~'||iuser AS tree
,level lvl
,CONNECT_BY_ROOT (ino||'~'||sid||'~'||s#||'~'||iuser) AS root_id
,LTRIM(SYS_CONNECT_BY_PATH((ino||'~'||sid||'~'||s#||'~'||iuser), '->'),'->') AS path
,CONNECT_BY_ISLEAF AS lf
,CONNECT_BY_ISCYCLE AS cycle
from dashmap
START WITH (abino is null
and absid is null
and abs# is null)
CONNECT BY NOCYCLE prior (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser) = (sampi||'~'||bino||'~'||bsid||'~'||bs#||'~'||buser)
ORDER SIBLINGS BY (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser)
)
where (lvl>1)
;
Output:
Remember the caveat: The only caveat of query#2 & query#3 is you wont get the sessions with blockers, but the blockers aren't visible in ash, see the hierarchical query (#2 & #3) looks for 2 conditions, a) sessions with no blockers and b) sessions with blockers & such blockers are available in ash, I don't know today if there is way to get those sessions with blockers which aren't available/visible in ash using hierarchical query]
If you find it difficult to follow this blog, please bear with me, I will write more explanation. Also if you want the excel view of the data, please get in touch with me through feedback button.
Objective: On our day to day DBA life, there are chances we might end up needing the db session block chain from the past, if it is current or live issue - then you have V$WAIT_CHAINS to see the block chain live.But if it is from the past, how do you pull it?
DBA_HIST_ACTIVE_SESS_HISTORY stores the information, but it needs some effort in mapping the block chain with the volume of data we have to process.
Instead that, I just used oracle's hierarchial query (thanks to Tanel Poder for sparking the idea in me) and some join tweeks to come up with the below sqls.
Version tested: 11.2.0.1
SQL1 - use the below row source [this query gives the sample sorted session, what it is doing along with what is its blocker and what is that doing, in case there is no information, it marks that session as IDLE - with username marked as NOSESSION]
with dash as
(
select /*+ parallel(4) materialize */
*
from dba_hist_active_sess_history
where cast(SAMPLE_TIME as date) between to_date('11-JUL-2019 11:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('11-JUL-2019 11:10:00','DD-MON-YYYY HH24:MI:SS')
),
do as
(
select /*+ parallel(4) materialize */
*
from dba_objects
where object_id in (select /*+ parallel(2) */ distinct(CURRENT_OBJ#) from dash)
),
du as
(
select user_id,username
from dba_users
where user_id in (select /*+ parallel(2) */ distinct(user_id) from dash)
),
dashmap as
(
select /*+ parallel(4) materialize */ a.INSTANCE_NUMBER ino
,a.SAMPLE_TIME sampt
,a.SAMPLE_ID sampi
,a.SESSION_ID sid
,a.SESSION_SERIAL# s#
,a.SESSION_TYPE stype
,decode(a.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(a.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)) pgm
,b.username iuser
,a.SQL_ID sql
,a.EVENT
,a.WAIT_CLASS wc
,a.TIME_WAITED tw
,a.MACHINE mach
,e.owner own
,e.object_type obty
,e.object_name obnm
,e.subobject_name suobnm
,a.BLOCKING_INST_ID
,a.BLOCKING_SESSION
,a.BLOCKING_SESSION_SERIAL#
,nvl(c.instance_number,99) bino
,nvl(c.session_id,9999999) bsid
,nvl(c.SESSION_SERIAL#,9999999) bs#
,nvl(c.SAMPLE_ID,99) bsample
,nvl(d.username,'NOSESSION') buser
,nvl(c.sql_id,'NOINFO') bsql
,nvl(decode(c.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(c.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)),'NOINFO') bpgm
,nvl(c.event,'NOINFO') bevent
,nvl(c.WAIT_CLASS,'NOINFO') bwc
,nvl(c.TIME_WAITED,9999999) btw
,nvl(c.MACHINE,'NOINFO') bmach
,nvl(f.owner,'NOINFO') bown
,nvl(f.object_type,'NOINFO') bobty
,nvl(f.object_name,'NOINFO') bobnm
,nvl(f.subobject_name,'NOINFO') bsuobnm
from dash a
inner join du b on a.user_id=b.user_id
left join do e on a.current_obj#=e.object_id
left join (dash c
inner join du d on c.user_id=d.user_id
left join do f on c.current_obj#=f.object_id
) on (
a.BLOCKING_INST_ID=c.INSTANCE_NUMBER
and a.SAMPLE_ID=c.SAMPLE_ID
and a.BLOCKING_SESSION=c.SESSION_ID
and a.BLOCKING_SESSION_SERIAL#=c.SESSION_SERIAL#
)
)
select * from dashmap
order by 1,2,3,4,5;
Output:
Query 2: To get a map of block chain and other session details [this query gives you a over view of all the sessions - i) sessions with no blockers ii) sessions with blockers available in ash. The only caveat is you wont get the sessions with blockers, but the blockers arent visible in ash, see the hierarchial query looks for 2 conditions, a) sessions with no blockers and b) sessions with blockers & such blockers are available in ash, I dont know today if there is way to get those sessions with blockers which arent available/visible in ash using hierarchial query]
with dash as
(
select /*+ parallel(4) materialize */
*
from dba_hist_active_sess_history
where cast(SAMPLE_TIME as date) between to_date('11-JUL-2019 11:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('11-JUL-2019 11:10:00','DD-MON-YYYY HH24:MI:SS')
),
do as
(
select /*+ parallel(4) materialize */
*
from dba_objects
where object_id in (select /*+ parallel(2) */ distinct(CURRENT_OBJ#) from dash)
),
du as
(
select /*+ materialize */ user_id,username
from dba_users
where user_id in (select /*+ parallel(2) */ distinct(user_id) from dash)
),
dashmap as
(
select /*+ parallel(4) materialize */ a.INSTANCE_NUMBER ino
,a.SAMPLE_TIME sampt
,a.SAMPLE_ID sampi
,a.SESSION_ID sid
,a.SESSION_SERIAL# s#
,a.SESSION_TYPE stype
,decode(a.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(a.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)) pgm
,b.username iuser
,a.SQL_ID sql
,a.EVENT
,a.WAIT_CLASS wc
,a.TIME_WAITED tw
,a.MACHINE mach
,e.owner own
,e.object_type obty
,e.object_name obnm
,e.subobject_name suobnm
,a.BLOCKING_INST_ID abino
,a.BLOCKING_SESSION absid
,a.BLOCKING_SESSION_SERIAL# abs#
,nvl(c.instance_number,99) bino
,nvl(c.session_id,9999999) bsid
,nvl(c.SESSION_SERIAL#,9999999) bs#
,nvl(c.SAMPLE_ID,99) bsample
,nvl(d.username,'NOSESSION') buser
,nvl(c.sql_id,'NOINFO') bsql
,nvl(decode(c.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(c.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)),'NOINFO') bpgm
,nvl(c.event,'NOINFO') bevent
,nvl(c.WAIT_CLASS,'NOINFO') bwc
,nvl(c.TIME_WAITED,9999999) btw
,nvl(c.MACHINE,'NOINFO') bmach
,nvl(f.owner,'NOINFO') bown
,nvl(f.object_type,'NOINFO') bobty
,nvl(f.object_name,'NOINFO') bobnm
,nvl(f.subobject_name,'NOINFO') bsuobnm
from dash a
inner join du b on a.user_id=b.user_id
left join do e on a.current_obj#=e.object_id
left join (dash c
inner join du d on c.user_id=d.user_id
left join do f on c.current_obj#=f.object_id
) on (
a.BLOCKING_INST_ID=c.INSTANCE_NUMBER
and a.SAMPLE_ID=c.SAMPLE_ID
and a.BLOCKING_SESSION=c.SESSION_ID
and a.BLOCKING_SESSION_SERIAL#=c.SESSION_SERIAL#
)
)
select /* parallel(4) */
sampt
,ino
,sid
,s#
,stype
,pgm
,iuser
,sql
,event
,wc
,tw
,mach
,own
,obty
,obnm
,suobnm
,bino
,bsid
,bs#
,buser
,bsql
,bpgm
,bevent
,bwc
,btw
,bmach
,bown
,bobty
,bobnm
,bsuobnm
,RPAD('.', (level-1)*2, '.') ||ino||'~'||sid||'~'||s#||'~'||iuser AS tree
,level
,CONNECT_BY_ROOT (ino||'~'||sid||'~'||s#||'~'||iuser) AS root_id
,LTRIM(SYS_CONNECT_BY_PATH((ino||'~'||sid||'~'||s#||'~'||iuser), '->'),'->') AS path
,CONNECT_BY_ISLEAF AS leaf
,CONNECT_BY_ISCYCLE AS cycle
from dashmap
START WITH (abino is null
and absid is null
and abs# is null)
CONNECT BY NOCYCLE prior (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser) = (sampi||'~'||bino||'~'||bsid||'~'||bs#||'~'||buser)
ORDER SIBLINGS BY (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser)
;
Output:
Query 3: To filter out only the session blocking chain . Remeber to read the conditions the query works on from last (query#2) [this query will filter out only blocked sessions with their blocker details, kind of chain identity]
with dash as
(
select /*+ parallel(4) materialize */
*
from dba_hist_active_sess_history
where cast(SAMPLE_TIME as date) between to_date('11-JUL-2019 11:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('11-JUL-2019 12:05:00','DD-MON-YYYY HH24:MI:SS')
),
do as
(
select /*+ parallel(4) materialize */
*
from dba_objects
where object_id in (select /*+ parallel(2) */ distinct(CURRENT_OBJ#) from dash)
),
du as
(
select /*+ materialize */ user_id,username
from dba_users
where user_id in (select /*+ parallel(2) */ distinct(user_id) from dash)
),
dashmap as
(
select /*+ parallel(4) materialize */ a.INSTANCE_NUMBER ino
,a.SAMPLE_TIME sampt
,a.SAMPLE_ID sampi
,a.SESSION_ID sid
,a.SESSION_SERIAL# s#
,a.SESSION_TYPE stype
,decode(a.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(a.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)) pgm
,b.username iuser
,a.SQL_ID sql
,a.EVENT
,a.WAIT_CLASS wc
,a.TIME_WAITED tw
,a.MACHINE mach
,e.owner own
,e.object_type obty
,e.object_name obnm
,e.subobject_name suobnm
,a.BLOCKING_INST_ID abino
,a.BLOCKING_SESSION absid
,a.BLOCKING_SESSION_SERIAL# abs#
,nvl(c.instance_number,99) bino
,nvl(c.session_id,9999999) bsid
,nvl(c.SESSION_SERIAL#,9999999) bs#
,nvl(c.SAMPLE_ID,99) bsample
,nvl(d.username,'NOSESSION') buser
,nvl(c.sql_id,'NOINFO') bsql
,nvl(decode(c.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(c.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)),'NOINFO') bpgm
,nvl(c.event,'NOINFO') bevent
,nvl(c.WAIT_CLASS,'NOINFO') bwc
,nvl(c.TIME_WAITED,9999999) btw
,nvl(c.MACHINE,'NOINFO') bmach
,nvl(f.owner,'NOINFO') bown
,nvl(f.object_type,'NOINFO') bobty
,nvl(f.object_name,'NOINFO') bobnm
,nvl(f.subobject_name,'NOINFO') bsuobnm
from dash a
inner join du b on a.user_id=b.user_id
left join do e on a.current_obj#=e.object_id
left join (dash c
inner join du d on c.user_id=d.user_id
left join do f on c.current_obj#=f.object_id
) on (
a.BLOCKING_INST_ID=c.INSTANCE_NUMBER
and a.SAMPLE_ID=c.SAMPLE_ID
and a.BLOCKING_SESSION=c.SESSION_ID
and a.BLOCKING_SESSION_SERIAL#=c.SESSION_SERIAL#
)
)
select *
from
(
select /* parallel(4) materialize */
sampt
,ino
,sid
,s#
,stype
,pgm
,iuser
,sql
,event
,wc
,tw
,mach
,own
,obty
,obnm
,suobnm
,bino
,bsid
,bs#
,buser
,bsql
,bpgm
,bevent
,bwc
,btw
,bmach
,bown
,bobty
,bobnm
,bsuobnm
,RPAD('.', (level-1)*2, '.') ||ino||'~'||sid||'~'||s#||'~'||iuser AS tree
,level lvl
,CONNECT_BY_ROOT (ino||'~'||sid||'~'||s#||'~'||iuser) AS root_id
,LTRIM(SYS_CONNECT_BY_PATH((ino||'~'||sid||'~'||s#||'~'||iuser), '->'),'->') AS path
,CONNECT_BY_ISLEAF AS lf
,CONNECT_BY_ISCYCLE AS cycle
from dashmap
START WITH (abino is null
and absid is null
and abs# is null)
CONNECT BY NOCYCLE prior (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser) = (sampi||'~'||bino||'~'||bsid||'~'||bs#||'~'||buser)
ORDER SIBLINGS BY (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser)
)
where (lvl>1)
;
Output:
Remember the caveat: The only caveat of query#2 & query#3 is you wont get the sessions with blockers, but the blockers aren't visible in ash, see the hierarchical query (#2 & #3) looks for 2 conditions, a) sessions with no blockers and b) sessions with blockers & such blockers are available in ash, I don't know today if there is way to get those sessions with blockers which aren't available/visible in ash using hierarchical query]
If you find it difficult to follow this blog, please bear with me, I will write more explanation. Also if you want the excel view of the data, please get in touch with me through feedback button.
Foot notes - 19Jul23 - to get the output formatted
set time on
set timing on
set lines 2000
set pages 49999
col sampt for a30
col pgm for a18
col iuser for a20
col event for a30
col mach for a30
col own for a30
col obty for a30
col obnm for a30
col subonm for a30
col buser for a20
col bpgm for a18
col bevent for a30
col bmach for a30
col bown for a30
col bobty for a30
col bobnm for a30
col bsuobnm for a30
col tree for a70
col root_id for a70
col path for a700
set colsep ,
alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
As usual thanks for the visit :)
Have a good day.
No comments:
Post a Comment