script to check sessions information from awr snap

script to check session information from awr snap.

===========This script will helps you to check session histaory from awr snap use with change sysdate as per neeed============

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';

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-2)
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-2)
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


==============in need only LAST 24 from hour 15min session report==================

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';

SELECT TO_CHAR(B.BEGIN_INTERVAL_TIME, 'DD-MON-YYYY HH24:MI')||' - '||TO_CHAR(B.END_INTERVAL_TIME, 'HH24:MI') as SNAP_TIME_INTERVAL_15MINS,
A.CURRENT_UTILIZATION
FROM SYS.WRH$_RESOURCE_LIMIT A, SYS.WRM$_SNAPSHOT B
WHERE A.RESOURCE_NAME LIKE 'processes'
AND A.SNAP_ID=B.SNAP_ID
AND B.BEGIN_INTERVAL_TIME between TRUNC(SYSDATE-1) and TRUNC(SYSDATE)

order by A.SNAP_ID;










-------------------------------------
Thanks for your vist!!!
Any suggestions or you have any better scipt please share in comment session it helps me in future posts
------------------------------------