Check number of sessions per hour for EACH INSTANCE in a RAC environment

Script :

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
/


Output :



SNAP_BEGIN             INSTANCE   SESSIONS
——————– ———- ———-
29-SEP-2018 00:00:00          1         93
29-SEP-2018 00:00:00          2         89
29-SEP-2018 01:00:00          1         98
29-SEP-2018 01:00:00          2         96
29-SEP-2018 02:00:00          1         99
29-SEP-2018 02:00:00          2         99
29-SEP-2018 03:00:00          1         95
29-SEP-2018 03:00:00          2         94
29-SEP-2018 04:00:00          1        100
29-SEP-2018 04:00:00          2         95
29-SEP-2018 05:00:00          1        102

  • September 30, 2018 | 16 views
  • Comments