Oracle RAC session info

Oracle Session Per Hour for Each Instance ..

 sessions per hour for EACH INSTANCE in a RAC

Oracle sessions per hour for EACH INSTANCE in a RAC

How to find number of sessions per hour node wise .


set linesize 200 pagesize 200
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-1)
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-1)
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


SNAP_BEGIN                      INSTANCE   SESSIONS
----------------------------- ---------- ----------
10-APR-2016 00:00:00                   1         81
10-APR-2016 00:00:00                   2         79
10-APR-2016 01:00:00                   1         85
10-APR-2016 01:00:00                   2         82
10-APR-2016 02:00:00                   1         84
10-APR-2016 02:00:00                   2         84
10-APR-2016 03:00:00                   1         83
10-APR-2016 03:00:00                   2         81
10-APR-2016 04:00:00                   1         83
10-APR-2016 04:00:00                   2         81
10-APR-2016 05:00:00                   1         84
10-APR-2016 05:00:00                   2         86
10-APR-2016 06:00:00                   1         83
10-APR-2016 06:00:00                   2         80
10-APR-2016 07:00:00                   1         83
10-APR-2016 07:00:00                   2         77





How to find number of sessions per hour

set linesize 200 pagesize 200
SELECT
to_char(TRUNC(s.begin_interval_time,'HH24'),'DD-MON-YYYY HH24:MI:SS') snap_begin,
sum(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 more than one snapshot IDs
--have the same number of sessions within that hour , so then picking one of the snapIds
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-1)
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
-- All the snapshots within a given hour will have the same begin interval time when TRUNC is used
-- for HH24 and we are selecting the Maximum sessions for a given one hour
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-1)
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'
GROUP BY
to_char(TRUNC(s.begin_interval_time,'HH24'),'DD-MON-YYYY HH24:MI:SS')
ORDER BY snap_begin ;



SNAP_BEGIN                      SESSIONS
----------------------------- ----------
10-APR-2016 00:00:00                 160
10-APR-2016 01:00:00                 167
10-APR-2016 02:00:00                 168
10-APR-2016 03:00:00                 164
10-APR-2016 04:00:00                 164
10-APR-2016 05:00:00                 170
10-APR-2016 06:00:00                 163
10-APR-2016 07:00:00                 160
10-APR-2016 08:00:00                 157
10-APR-2016 09:00:00                 157
10-APR-2016 10:00:00                 157
10-APR-2016 11:00:00                 162
10-APR-2016 12:00:00                 162
10-APR-2016 13:00:00                 163
10-APR-2016 14:00:00                 168
10-APR-2016 15:00:00                 160
10-APR-2016 16:00:00                 160
10-APR-2016 17:00:00                 157
10-APR-2016 18:00:00                 154
10-APR-2016 19:00:00                 154
10-APR-2016 20:00:00                 159
10-APR-2016 21:00:00                 160
10-APR-2016 22:00:00                 160
10-APR-2016 23:00:00                 160
11-APR-2016 00:00:00                 164
11-APR-2016 01:00:00                 164
11-APR-2016 02:00:00                 162
11-APR-2016 03:00:00                 157
11-APR-2016 04:00:00                 155

29 rows selected.