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.