Use Oracle ASH to draw oracle session block chain

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.

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.

Aggregate dba_hist_active_sess_history to find out the total # of sessions logged in overall, the aggregated elapsed time, aggregated dbtime,aggregated pga max size and aggregated temp max size in the window we specify

Obective: let us aggregate dba_hist_active_sess_history to find out the total # of sessions logged in overall, the aggregated elapsed time, aggregated dbtime,aggregated pga max size and aggregated temp max size in the window we specify.

Query:

set lines 300
set pages 3000
set colsep ,

variable d1 varchar2(20);
variable d2 varchar2(20);
prompt enter date in format "DD/MM/YYYY HH24:MI:SS" d1:
exec :d1:=&d1
prompt enter date in format "DD/MM/YYYY HH24:MI:SS" d2:

exec :d2:=&d2

with dhash as
(
select a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine,min(cast(a.sample_time as date)) firstsample,(max(cast(a.sample_time as date))-min(cast(a.sample_time as date)))*86400 sess_ela_sec_insamp,sum(tm_delta_time) sess_dbtime_insamp,max(a.PGA_ALLOCATED) max_pga,max(TEMP_SPACE_ALLOCATED) max_tmps
from dba_hist_active_sess_history a,dba_users b
where a.sample_time between to_date(:d1,'DD/MM/YYYY HH24:MI:SS') and to_date(:d2,'DD/MM/YYYY HH24:MI:SS')
and b.user_id=a.user_id
group by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine
order by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine)
select count(1) session_cnt,sum(sess_ela_sec_insamp) sum_ela,sum(sess_dbtime_insamp)/1000/1000 sum_dbt_sec,sum(max_pga) sum_mxpga,sum(max_tmps) sum_mxtmp
from dhash;

SESSION_CNT,   SUM_ELA,   SUM_DBT, SUM_MXPGA, SUM_MXTMP
-----------,----------,----------,----------,----------
        177,  19009211,4.2618E+11,1071654912,  28311552

Report by hour:

with dhash as
(
select a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine,min(cast(a.sample_time as date)) firstsample,(max(cast(a.sample_time as date))-min(cast(a.sample_time as date)))*86400 sess_ela_sec_insamp,sum(tm_delta_time) sess_dbtime_insamp,max(a.PGA_ALLOCATED) max_pga,max(TEMP_SPACE_ALLOCATED) max_tmps
from dba_hist_active_sess_history a,dba_users b
where a.sample_time between to_date(:d1,'DD/MM/YYYY HH24:MI:SS') and to_date(:d2,'DD/MM/YYYY HH24:MI:SS')
and b.user_id=a.user_id
group by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine
order by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine)
select trunc(firstsample,'HH') firstsample_HH,count(1) session_cnt,sum(sess_ela_sec_insamp) sum_ela,sum(sess_dbtime_insamp)/1000/1000 sum_dbt_sec,sum(max_pga) sum_mxpga,sum(max_tmps) sum_mxtmp
from dhash
group by trunc(firstsample,'HH')

order by 1;

SQL> with dhash as
(
select a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine,min(cast(a.sample_time as date)) firstsample,(max(cast(a.sample_time as date))-min(cast(a.sample_time as date)))*86400 sess_ela_sec_insamp,sum(tm_delta_time) sess_dbtime_insamp,max(a.PGA_ALLOCATED) max_pga,max(TEMP_SPACE_ALLOCATED) max_tmps
from dba_hist_active_sess_history a,dba_users b
where a.sample_time between to_date(:d1,'DD/MM/YYYY HH24:MI:SS') and to_date(:d2,'DD/MM/YYYY HH24:MI:SS')
and b.user_id=a.user_id
group by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine
order by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine)
select trunc(firstsample,'HH') firstsample_HH,count(1) session_cnt,sum(sess_ela_sec_insamp) sum_ela,sum(sess_dbtime_insamp)/1000/1000 sum_dbt_sec,sum(max_pga) sum_mxpga,sum(max_tmps) sum_mxtmp
from dhash
group by trunc(firstsample,'HH')
order by 1;  2    3    4    5    6    7    8    9   10   11   12

FIRSTSAMPLE_HH     ,SESSION_CNT,   SUM_ELA,SUM_DBT_SEC, SUM_MXPGA, SUM_MXTMP
-------------------,-----------,----------,-----------,----------,----------
10/03/2019 13:00:00,         33,  14649921, 208786.223, 189300736,   1048576
..
07/04/2019 15:00:00,          4,        10,  12.442203,  21295104,   2097152

17 rows selected.

Thank you for paying the visit.

version 3 - Write a query to find out how many new connections or session were connected in db from dba_hist_active_sess_history - aggregated by day/hr/mi

Objective: In continuation to the previous 2 blogs or versions on determining sessions which are connected on db from dba_hist_active_Sess_history. In this blog we will see how we can aggregate the metrics to report it based on day/hr/mi.

Query:

--- version 3 of new session identification by sample as we like (more of aggregation and averaging).

variable d1 varchar2(20);
variable d2 varchar2(20);
prompt enter date in format "DD/MM/YYYY HH24:MI:SS" d1:
exec :d1:=&d1
prompt enter date in format "DD/MM/YYYY HH24:MI:SS" d2:
exec :d2:=&d2

SQL> exec :d1:=&d1
Enter value for d1: '07/03/2019 00:00:00'

PL/SQL procedure successfully completed.

SQL> exec :d2:=&d2
Enter value for d2: '07/04/2019 23:59:59'

PL/SQL procedure successfully completed.


set lines 500
set pages 49999
set colsep ,
col program for a45 wrap
col username for a15
col machine for a30
set trim on
set trims on
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
-- remember to ignore the very first sample output on this query.
--daywise query first
with dhash as
(
select a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine,min(cast(a.sample_time as date)) firstsample,(max(cast(a.sample_time as date))-min(cast(a.sample_time as date)))*86400 sess_ela_sec_insamp,sum(tm_delta_time) sess_dbtime_insamp,max(a.PGA_ALLOCATED) max_pga,max(TEMP_SPACE_ALLOCATED) max_tmps
from dba_hist_active_sess_history a,dba_users b
where a.sample_time between to_date(:d1,'DD/MM/YYYY HH24:MI:SS') and to_date(:d2,'DD/MM/YYYY HH24:MI:SS')
and b.user_id=a.user_id
group by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine
order by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine)
select trunc(firstsample),count(1)
from dhash
group by trunc(firstsample)
order by trunc(firstsample);

TRUNC(FIRSTSAMPLE) ,  COUNT(1)
-------------------,----------
10/03/2019 00:00:00,        61
..
07/04/2019 00:00:00,        34

--hrly query
with dhash as
(
select a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine,min(cast(a.sample_time as date)) firstsample,(max(cast(a.sample_time as date))-min(cast(a.sample_time as date)))*86400 sess_ela_sec_insamp,sum(tm_delta_time) sess_dbtime_insamp,max(a.PGA_ALLOCATED) max_pga,max(TEMP_SPACE_ALLOCATED) max_tmps
from dba_hist_active_sess_history a,dba_users b
where a.sample_time between to_date(:d1,'DD/MM/YYYY HH24:MI:SS') and to_date(:d2,'DD/MM/YYYY HH24:MI:SS')
and b.user_id=a.user_id
group by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine
order by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine)
select trunc(firstsample,'HH'),count(1)
from dhash
group by trunc(firstsample,'HH')
order by trunc(firstsample,'HH');

TRUNC(FIRSTSAMPLE,',  COUNT(1)
-------------------,----------
10/03/2019 13:00:00,        33
10/03/2019 14:00:00,         6
10/03/2019 15:00:00,         5
...
07/04/2019 14:00:00,        30
07/04/2019 15:00:00,         4

-- minute precision

with dhash as
(
select a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine,min(cast(a.sample_time as date)) firstsample,(max(cast(a.sample_time as date))-min(cast(a.sample_time as date)))*86400 sess_ela_sec_insamp,sum(tm_delta_time) sess_dbtime_insamp,max(a.PGA_ALLOCATED) max_pga,max(TEMP_SPACE_ALLOCATED) max_tmps
from dba_hist_active_sess_history a,dba_users b
where a.sample_time between to_date(:d1,'DD/MM/YYYY HH24:MI:SS') and to_date(:d2,'DD/MM/YYYY HH24:MI:SS')
and b.user_id=a.user_id
group by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine
order by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine)
select trunc(firstsample,'MI'),count(1)
from dhash
group by trunc(firstsample,'MI')
order by trunc(firstsample,'MI');

TRUNC(FIRSTSAMPLE,',  COUNT(1)
-------------------,----------
10/03/2019 13:48:00,         2
10/03/2019 13:49:00,         4
10/03/2019 13:50:00,        25
10/03/2019 13:51:00,         1
...
07/04/2019 15:16:00,         2
07/04/2019 15:30:00,         1
07/04/2019 15:43:00,         1

58 rows selected.

Thanks for paying a visit.

version 2 - Write a query to find out how many new connections or session were connected in db from dba_hist_active_sess_history

objective: Write a query to find out how many new connections or session were connected in db from dba_hist_active_sess_history.
There is already way to mine listener to find this detail + there is already blog written, now in this blog we will try simplify the query to reduce the run duration and fetch results.

step 1)
Get out the existing query...

select a.sample_time
,count(1)
from dba_hist_active_sess_history a
,(select samp_time,lag(samp_time,1) over (order by samp_time) lag_time
from
(
select distinct(sample_time) samp_time
from dba_hist_active_sess_history
order by 1)) c
where a.sample_time=c.samp_time
and (a.session_id,a.SESSION_SERIAL#) not in (select session_id,sESSION_SERIAL# from dba_hist_active_sess_history where sample_time=c.lag_time)
group by a.sample_time
order by 1;

step 2)
Observe the query above, we are fetching from the same table 3 times, so we better find a way to reduce it. let us try to materialize all the needed columns from the desired table, this way we reduce the result set and query less data.

set lines 300
set pages 3000
set colsep ,

variable d1 varchar2(20);
variable d2 varchar2(20);
prompt enter date in format "DD/MM/YYYY HH24:MI:SS" d1:
exec :d1:=&d1
prompt enter date in format "DD/MM/YYYY HH24:MI:SS" d2:
exec :d2:=&d2


with dhash as
(select instance_number,sample_time,session_id,session_serial#
from dba_hist_active_sess_history
where sample_time between to_date(:d1,'DD/MM/YYYY HH24:MI:SS') and to_date(:d2,'DD/MM/YYYY HH24:MI:SS')
order by instance_number,sample_time,session_id,session_serial#)
select a.instance_number,a.sample_time,count(1)
from dhash a
,(select instance_number,samp_time,lag(samp_time,1) over (order by samp_time) lag_time
from
(
select instance_number,sample_time samp_time
from dhash
group by instance_number,sample_time
order by 1,2)) c
where a.sample_time=c.samp_time
and a.instance_number=c.instance_number
and (a.instance_number,a.session_id,a.SESSION_SERIAL#) not in (select instance_number,session_id,sESSION_SERIAL# from dhash where sample_time=c.lag_time and instance_number=c.instance_number)
group by a.instance_number,a.sample_time
order by 1,2;

step 3) test..

Result:

SQL> @check_sess.sql
enter date in format "DD/MM/YYYY HH24:MI:SS" d1:
Enter value for d1: '23/12/2018 12:50:00'

PL/SQL procedure successfully completed.

enter date in format "DD/MM/YYYY HH24:MI:SS" d2:
Enter value for d2: '23/12/2018 13:15:00'

PL/SQL procedure successfully completed.


INSTANCE_NUMBER,SAMPLE_TIME                                                                ,  COUNT(1)
---------------,---------------------------------------------------------------------------,----------
              1,23-DEC-18 12.56.33.462 PM                                                  ,         1
              1,23-DEC-18 01.07.24.256 PM                                                  ,         1



To test its effectiveness, let us query v$active_session_history..

with dhash as
(select inst_id instance_number,sample_time,session_id,session_serial#
from gv$active_session_history
where sample_time between to_date(:d1,'DD/MM/YYYY HH24:MI:SS') and to_date(:d2,'DD/MM/YYYY HH24:MI:SS')
order by instance_number,sample_time,session_id,session_serial#)
select a.instance_number,a.sample_time,count(1)
from dhash a
,(select instance_number,samp_time,lag(samp_time,1) over (order by samp_time) lag_time
from
(
select instance_number,sample_time samp_time
from dhash
group by instance_number,sample_time
order by 1,2)) c
where a.sample_time=c.samp_time
and a.instance_number=c.instance_number
and (a.instance_number,a.session_id,a.SESSION_SERIAL#) not in (select instance_number,session_id,sESSION_SERIAL# from dhash where sample_time=c.lag_time and instance_number=c.instance_number)
group by a.instance_number,a.sample_time
order by 1,2;

SQL> @check_sess_eff.sql
enter date in format "DD/MM/YYYY HH24:MI:SS" d1:
Enter value for d1: '23/12/2018 12:50:00'

PL/SQL procedure successfully completed.

enter date in format "DD/MM/YYYY HH24:MI:SS" d2:
Enter value for d2: '23/12/2018 13:39:00'

PL/SQL procedure successfully completed.


INSTANCE_NUMBER,SAMPLE_TIME                                                                ,  COUNT(1)
---------------,---------------------------------------------------------------------------,----------
              1,23-DEC-18 12.51.44.123 PM                                                  ,         1
              1,23-DEC-18 12.56.17.452 PM                                                  ,         1
              1,23-DEC-18 12.56.29.462 PM                                                  ,         1
              1,23-DEC-18 01.00.45.758 PM                                                  ,         2
              1,23-DEC-18 01.01.45.838 PM                                                  ,         1
              1,23-DEC-18 01.02.29.888 PM                                                  ,         1
              1,23-DEC-18 01.07.24.256 PM                                                  ,         1
              1,23-DEC-18 01.11.47.603 PM                                                  ,         2
              1,23-DEC-18 01.16.04.913 PM                                                  ,         1
              1,23-DEC-18 01.17.30.013 PM                                                  ,         1
              1,23-DEC-18 01.22.49.395 PM                                                  ,         1
              1,23-DEC-18 01.23.10.415 PM                                                  ,         1
              1,23-DEC-18 01.26.56.704 PM                                                  ,         1
              1,23-DEC-18 01.27.45.771 PM                                                  ,         2
              1,23-DEC-18 01.29.46.017 PM                                                  ,         1
              1,23-DEC-18 01.29.51.027 PM                                                  ,         2
              1,23-DEC-18 01.29.57.027 PM                                                  ,         1
              1,23-DEC-18 01.30.03.037 PM                                                  ,         1
              1,23-DEC-18 01.30.09.037 PM                                                  ,         1
              1,23-DEC-18 01.30.15.047 PM                                                  ,         1
              1,23-DEC-18 01.30.21.057 PM                                                  ,         1
              1,23-DEC-18 01.30.27.067 PM                                                  ,         1
              1,23-DEC-18 01.30.28.067 PM                                                  ,         1
              1,23-DEC-18 01.30.33.077 PM                                                  ,         1
              1,23-DEC-18 01.30.38.077 PM                                                  ,         1
              1,23-DEC-18 01.37.08.690 PM                                                  ,         1
              1,23-DEC-18 01.37.15.690 PM                                                  ,         1
              1,23-DEC-18 01.37.21.700 PM                                                  ,         1
              1,23-DEC-18 01.37.25.710 PM                                                  ,         3
              1,23-DEC-18 01.37.28.782 PM                                                  ,         2
              1,23-DEC-18 01.37.29.802 PM                                                  ,         1
              1,23-DEC-18 01.37.30.802 PM                                                  ,         1
              1,23-DEC-18 01.37.38.812 PM                                                  ,         1
              1,23-DEC-18 01.37.44.842 PM                                                  ,         1
              1,23-DEC-18 01.37.51.862 PM                                                  ,         1
              1,23-DEC-18 01.37.57.872 PM                                                  ,         1
              1,23-DEC-18 01.38.04.872 PM                                                  ,         1
              1,23-DEC-18 01.38.11.892 PM                                                  ,         1
              1,23-DEC-18 01.38.52.942 PM                                                  ,         1

39 rows selected.

step 4) validate...

select inst_id,sample_time,session_id,session_serial#
from gv$active_session_history
where sample_Time between sysdate-1/24 and sysdate-.5/24
order by inst_id,sample_time,session_id,session_serial#
/

   INST_ID,SAMPLE_TIME                                                                ,SESSION_ID,SESSION_SERIAL#
----------,---------------------------------------------------------------------------,----------,---------------
...
         1,23-DEC-18 01.37.20.700 PM                                                  ,       133,            135
         1,23-DEC-18 01.37.21.700 PM                                                  ,       133,            137    << new record 1
         1,23-DEC-18 01.37.22.710 PM                                                  ,       133,            137
         1,23-DEC-18 01.37.23.710 PM                                                  ,       133,            137
         1,23-DEC-18 01.37.24.710 PM                                                  ,       133,            137
         1,23-DEC-18 01.37.25.710 PM                                                  ,        15,            136    << new record 1
         1,23-DEC-18 01.37.25.710 PM                                                  ,       132,              1    << new record 2
         1,23-DEC-18 01.37.25.710 PM                                                  ,       133,            137    << repeat record
         1,23-DEC-18 01.37.25.710 PM                                                  ,       142,            277    << new record 3
         1,23-DEC-18 01.37.26.720 PM                                                  ,       133,            137
         1,23-DEC-18 01.37.26.720 PM                                                  ,       142,            277
         1,23-DEC-18 01.37.27.762 PM                                                  ,       133,            137
         1,23-DEC-18 01.37.27.762 PM                                                  ,       142,            277
         1,23-DEC-18 01.37.28.782 PM                                                  ,        19,             13
         1,23-DEC-18 01.37.28.782 PM                                                  ,       132,              1
         1,23-DEC-18 01.37.28.782 PM                                                  ,       133,            137
         1,23-DEC-18 01.37.28.782 PM                                                  ,       142,            277
         1,23-DEC-18 01.37.29.802 PM                                                  ,       139,             47
         1,23-DEC-18 01.37.29.802 PM                                                  ,       142,            277
         1,23-DEC-18 01.37.30.802 PM                                                  ,        19,             17

so it works fine.
There is one use case of this method of printing the session count is - the sessions which are established locally from the server are also traced where as listener doesnt know them.

If your intrested in knowing the cost difference between version 1 & 2 of this blog is...

version 1:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2908163802

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |    61 |  5612 |   167   (2)| 00:00:03 |       |       |
|   1 |  SORT GROUP BY NOSORT          |                             |    61 |  5612 |   167   (2)| 00:00:03 |       |       |
|*  2 |   FILTER                       |                             |       |       |            |          |       |       |
|   3 |    NESTED LOOPS                |                             |    62 |  5704 |    13  (24)| 00:00:01 |       |       |

version 2:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 386194343

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |     2 |    98 |    16  (32)| 00:00:01 |       |       |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |       |       |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6610_42F9DE   |       |       |            |          |       |       |
|   3 |    SORT ORDER BY           |                             |     2 |   136 |     6  (17)| 00:00:01 |       |       |
|*  4 |     FILTER                 |                             |       |       |            |          |       |       |

There is a huge difference in cost, I am sure the run duration of version 2 will be far better than the first one.

Thank you & best wishes.

version 1: query to find out how many new connections or session were connected in db from dba_hist_active_sess_history.

objective: Write a query to find out how many new connections or session were connected in db from dba_hist_active_sess_history.
There is already way to mine listener to find this detail, but let us try this for fun.


step 1) Write a query to get the sample time.

select distinct(sample_time)
from dba_hist_active_sess_history
order by 1;

Step 2) Write a wrapper on top of step 1 to fetch sample time and its lag

select samp_time,lag(samp_time,1) over (order by samp_time) lag_time
from
(
select distinct(sample_time) samp_time
from dba_hist_active_sess_history
order by 1);

Step 3) Write a wrapper on top of the step 2 to fetch sid,serial# from dba_hist_active_Sess_history for both lag and actual sample time and count the difference.

select a.sample_time
,count(1)
from dba_hist_active_sess_history a
,(select samp_time,lag(samp_time,1) over (order by samp_time) lag_time
from
(
select distinct(sample_time) samp_time
from dba_hist_active_sess_history
order by 1)) c
where a.sample_time=c.samp_time
and (a.session_id,a.SESSION_SERIAL#) not in (select session_id,sESSION_SERIAL# from dba_hist_active_sess_history where sample_time=c.lag_time)
group by a.sample_time
order by 1;

==> works well.

to test its effectiveness... replaced dba_hist with v$ to query the current info for every second sample.

select a.sample_time
,count(1)
from v$active_session_history a
,(select samp_time,lag(samp_time,1) over (order by samp_time) lag_time
from
(
select distinct(sample_time) samp_time
from v$active_session_history
order by 1)) c
where a.sample_time=c.samp_time
and (a.session_id,a.SESSION_SERIAL#) not in (select session_id,sESSION_SERIAL# from v$active_session_history where sample_time=c.lag_time)
group by a.sample_time
order by 1;


sample record from v$active_session_history...

21-DEC-18 11.31.39.436 AM                                                          136             188
21-DEC-18 11.31.40.436 AM                                                          136             188    << repeating all the time
21-DEC-18 11.31.41.436 AM                                                           12               3    << new 1
21-DEC-18 11.31.41.436 AM                                                           21               5    << new 2
21-DEC-18 11.31.41.436 AM                                                          128               1    << new 3
21-DEC-18 11.31.41.436 AM                                                          136             188    << repeating
21-DEC-18 11.31.42.446 AM                                                          136             188
21-DEC-18 11.31.43.446 AM                                                          136             188
21-DEC-18 11.31.44.446 AM                                                          136             188
21-DEC-18 11.31.45.446 AM                                                          136             188
21-DEC-18 11.31.46.446 AM                                                          136             188
21-DEC-18 11.31.47.446 AM                                                          136             188
21-DEC-18 11.31.48.446 AM                                                          136             188
21-DEC-18 11.31.49.446 AM                                                          136             188
21-DEC-18 11.31.50.446 AM                                                          136             188
21-DEC-18 11.31.51.446 AM                                                          136             188
21-DEC-18 11.31.52.446 AM                                                          136             188
21-DEC-18 11.31.53.456 AM                                                          136             188
21-DEC-18 11.31.54.456 AM                                                          136             188
21-DEC-18 11.31.55.456 AM                                                          136             188
21-DEC-18 11.31.56.456 AM                                                          136             188
21-DEC-18 11.31.57.456 AM                                                          136             188
21-DEC-18 11.31.58.456 AM                                                          136             188
21-DEC-18 11.31.59.456 AM                                                          136             188
21-DEC-18 11.32.00.456 AM                                                          136             188
21-DEC-18 11.32.01.466 AM                                                          136             188
21-DEC-18 11.32.02.476 AM                                                          136             188
21-DEC-18 11.32.03.476 AM                                                          136             188
21-DEC-18 11.32.04.486 AM                                                          136             188
21-DEC-18 11.32.05.486 AM                                                          136             188
21-DEC-18 11.32.06.486 AM                                                          136             188
21-DEC-18 11.32.07.486 AM                                                          136             188
21-DEC-18 11.32.08.486 AM                                                          136             188
21-DEC-18 11.32.09.486 AM                                                          136             188
21-DEC-18 11.32.10.486 AM                                                          136             188
21-DEC-18 11.32.11.496 AM                                                          136             188
21-DEC-18 11.32.41.556 AM                                                           12               3  << new 1
21-DEC-18 11.35.00.897 AM                                                           14              20    << new 1


so ideally we should have 21-DEC-18 11.31.41.436 AM (3) and 21-DEC-18 11.32.41.556 AM  & 21-DEC-18 11.35.00.897 AM (1) each. Other samples had repeating value, so should be skipped.

SAMPLE_TIME                                                                   COUNT(1)
--------------------------------------------------------------------------- ----------
21-DEC-18 10.47.14.345 AM                                                            2
21-DEC-18 10.47.17.365 AM                                                            2
..
21-DEC-18 11.30.27.236 AM                                                            1
21-DEC-18 11.30.31.256 AM                                                            1
21-DEC-18 11.30.39.266 AM                                                            1
21-DEC-18 11.30.41.276 AM                                                            1
21-DEC-18 11.30.42.276 AM                                                            1
21-DEC-18 11.31.21.396 AM                                                            1
21-DEC-18 11.31.41.436 AM                                                            3    << 3 as expected
21-DEC-18 11.32.41.556 AM                                                            1    << 1 as expected
21-DEC-18 11.35.00.897 AM                                                            1    << 1 as expected

So this works as expected.

Thanks for paying a visit.
All the best.