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.
