Oracle: session count history (AWR)
AWR snapshot query to track the number of sessions over 24 hours.
This query returns the history of the number of sessions over the last day, based on AWR snapshots (dba_hist_snapshot and dba_hist_resource_limit).
select
s.instance_number,
s.end_interval_time,
rn.current_utilization
from
dba_hist_snapshot s
join dba_hist_resource_limit rn on (s.snap_id = rn.snap_id and s.instance_number = rn.instance_number)
where
rn.resource_name = 'sessions'
and s.end_interval_time > sysdate - 1
order by
s.end_interval_time
;
Notes
- The
DBA_HIST_*views are part of AWR, which requires the Oracle Diagnostics Pack license. - To change the period, adjust
sysdate - 1(in days).