*********************************************************
* 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