 |
 |
*********************************************************
* Description: SQL for Querying Session Number by Period
* Compatiablity: RDBMS 10g, 11g, 12c
* Date: 10:38 AM EST, 01/11/2017
*********************************************************
<1> Querying session number in minutes period by Stats Pack table. Result was generated by (DB time)/duration = session/minute.
The purpose of below query is for consulting general average case for performance tunning only:
|
|__ SQL> with sessiondata as (
select snaptime,
snaptime - lag(snaptime) over (order by snaptime) duration,
dbtime - lag(dbtime) over (order by snaptime) timedelta
from (
select s.snap_time snaptime,
(select sum(time_waited_micro) / 1000000 from stats$system_event se where event not in (select event from stats$idle_Event)
and se.snap_id = s.snap_id and se.dbid = s.dbid and se.instance_number = s.instance_number) +
(ss.value / 100) dbtime
from stats$snapshot s, stats$sysstat ss
where s.snap_id = ss.snap_id and s.dbid = ss.dbid and s.instance_number = ss.instance_number
and s.dbid = (select dbid from v$database)
and s.instance_number = (select instance_number from v$instance)
and ss.name = 'CPU used by this session'
)
)
select to_char(snaptime, 'DD-MON-YYYY HH24:MI:SS') snaptime, duration * 86400 "Duration (s)",
round((timedelta / (duration * 86400)) * 60, 2) "Avg. Active Sessions/min"
from sessiondata;
SNAPTIME Duration (s) Avg. Active Sessions/min
------------------------------ ------------ ------------------------
11-JAN-2017 01:00:02 3599 .49
11-JAN-2017 02:00:01 3599 .47
11-JAN-2017 03:00:04 3603 .48
11-JAN-2017 04:00:03 3599 .47
11-JAN-2017 05:00:02 3599 6.46
11-JAN-2017 06:00:01 3599 .43
11-JAN-2017 07:00:04 3603 1.41
11-JAN-2017 08:00:03 3599 .43
11-JAN-2017 09:00:02 3599 .47
11-JAN-2017 10:00:01 3599 .42
<2> Querying session number by hour. The query is based on dba_hist_resource_limit, which only fits for Oracle RDBMS software Enterprise Edition:
|
|__ SQL> 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-JAN-2017 22:00:00 46
10-JAN-2017 23:00:00 46
11-JAN-2017 00:00:00 46
11-JAN-2017 01:00:00 44
11-JAN-2017 02:00:00 45
11-JAN-2017 03:00:00 45
11-JAN-2017 04:00:00 43
11-JAN-2017 05:00:00 48
11-JAN-2017 06:00:00 42
11-JAN-2017 07:00:00 42
11-JAN-2017 08:00:00 44
<3> Querying session number by hour. This query is based on dba_hist_sysmetric_summary, which is the most accurate and fits for general cases:
|
|__ SQL> set colsep ','
set pages 5000
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
select min(begin_time), max(end_time),
round(sum(case metric_name when 'Session Count' then maxval end)) SC
from dba_hist_sysmetric_summary
group by snap_id,instance_number
order by instance_number,snap_id;
MIN(BEGIN_TIME) ,MAX(END_TIME) , SC
----------------,----------------,----------
02-03-2017 21:59,02-03-2017 23:00, 114
02-03-2017 23:00,03-03-2017 00:00, 109
03-03-2017 00:00,03-03-2017 01:00, 105
03-03-2017 01:00,03-03-2017 01:59, 105
03-03-2017 01:59,03-03-2017 03:00, 108
03-03-2017 03:00,03-03-2017 04:00, 99
03-03-2017 04:00,03-03-2017 04:59, 100
03-03-2017 04:59,03-03-2017 05:59, 117
03-03-2017 05:59,03-03-2017 07:00, 109
03-03-2017 07:00,03-03-2017 08:00, 127
Your Comments