Session and Process count at a point in time

----Processes---

set lines 2000 pages 500
col BEGIN_INTERVAL_TIME for a40
col END_INTERVAL_TIME for a40
SELECT   s.begin_interval_time, s.end_interval_time,
         rl.instance_number, rl.resource_name, rl.current_utilization,
         rl.max_utilization
    FROM dba_hist_resource_limit rl, dba_hist_snapshot s
   WHERE s.snap_id = rl.snap_id AND rl.resource_name = 'processes'
ORDER BY s.begin_interval_time, rl.instance_number;


####Sessions#####

set lines 2000 pages 500
col BEGIN_INTERVAL_TIME for a40
col END_INTERVAL_TIME for a40
SELECT   rl.snap_id, s.begin_interval_time, s.end_interval_time,
         rl.instance_number, rl.resource_name, rl.current_utilization,
         rl.max_utilization
    FROM dba_hist_resource_limit rl, dba_hist_snapshot s
   WHERE s.snap_id = rl.snap_id AND rl.resource_name = 'sessions'
ORDER BY s.begin_interval_time, rl.instance_number;


####You can do that with auditing by enabling session auditing.

select
   username
   ,timestamp logon
   ,logoff_time logoff
   , os_username
   ,terminal
   ,action_name
   ,returncode
from dba_audit_session
order by  timestamp;

##How to find number of sessions per hour for EACH INSTANCE in a RAC###


SELECT
to_char(TRUNC(s.begin_interval_time,'HH24'),'DD-MON-YYYY HH24:MI:SS') snap_begin,
r.instance_number instance,
r.current_utilization sessions 
FROM
dba_hist_resource_limit r,
dba_hist_snapshot s
WHERE ( TRUNC(s.begin_interval_time,'HH24'),s.snap_id ) IN
( 
--Select the Maximum of the Snapshot IDs within an hour if all of the snapshot IDs
--have the same number of sessions
SELECT TRUNC(sn.begin_interval_time,'HH24'),MAX(rl.snap_id)
FROM dba_hist_resource_limit rl,dba_hist_snapshot sn
WHERE TRUNC(sn.begin_interval_time) >= TRUNC(sysdate-4)
AND rl.snap_id = sn.snap_id
AND rl.resource_name = 'sessions'
AND rl.instance_number = sn.instance_number
AND ( TRUNC(sn.begin_interval_time,'HH24'),rl.CURRENT_UTILIZATION ) IN
(
--Select the Maximum no.of sessions for a given begin interval time
SELECT TRUNC(s.begin_interval_time,'HH24'),MAX(r.CURRENT_UTILIZATION) "no_of_sess"
FROM dba_hist_resource_limit r,dba_hist_snapshot s
WHERE r.snap_id = s.snap_id
AND TRUNC(s.begin_interval_time) >= TRUNC(sysdate-4)
AND r.instance_number=s.instance_number
AND r.resource_name = 'sessions'
GROUP BY TRUNC(s.begin_interval_time,'HH24')
)
GROUP BY TRUNC(sn.begin_interval_time,'HH24'),CURRENT_UTILIZATION
)
AND r.snap_id = s.snap_id
AND r.instance_number = s.instance_number
AND r.resource_name = 'sessions'
ORDER BY snap_begin,instance
/