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.