version 2 - Write a query to find out how many new connections or session were connected in db from dba_hist_active_sess_history

objective: Write a query to find out how many new connections or session were connected in db from dba_hist_active_sess_history.
There is already way to mine listener to find this detail + there is already blog written, now in this blog we will try simplify the query to reduce the run duration and fetch results.

step 1)
Get out the existing query...

select a.sample_time
,count(1)
from dba_hist_active_sess_history a
,(select samp_time,lag(samp_time,1) over (order by samp_time) lag_time
from
(
select distinct(sample_time) samp_time
from dba_hist_active_sess_history
order by 1)) c
where a.sample_time=c.samp_time
and (a.session_id,a.SESSION_SERIAL#) not in (select session_id,sESSION_SERIAL# from dba_hist_active_sess_history where sample_time=c.lag_time)
group by a.sample_time
order by 1;

step 2)
Observe the query above, we are fetching from the same table 3 times, so we better find a way to reduce it. let us try to materialize all the needed columns from the desired table, this way we reduce the result set and query less data.

set lines 300
set pages 3000
set colsep ,

variable d1 varchar2(20);
variable d2 varchar2(20);
prompt enter date in format "DD/MM/YYYY HH24:MI:SS" d1:
exec :d1:=&d1
prompt enter date in format "DD/MM/YYYY HH24:MI:SS" d2:
exec :d2:=&d2


with dhash as
(select instance_number,sample_time,session_id,session_serial#
from dba_hist_active_sess_history
where sample_time between to_date(:d1,'DD/MM/YYYY HH24:MI:SS') and to_date(:d2,'DD/MM/YYYY HH24:MI:SS')
order by instance_number,sample_time,session_id,session_serial#)
select a.instance_number,a.sample_time,count(1)
from dhash a
,(select instance_number,samp_time,lag(samp_time,1) over (order by samp_time) lag_time
from
(
select instance_number,sample_time samp_time
from dhash
group by instance_number,sample_time
order by 1,2)) c
where a.sample_time=c.samp_time
and a.instance_number=c.instance_number
and (a.instance_number,a.session_id,a.SESSION_SERIAL#) not in (select instance_number,session_id,sESSION_SERIAL# from dhash where sample_time=c.lag_time and instance_number=c.instance_number)
group by a.instance_number,a.sample_time
order by 1,2;

step 3) test..

Result:

SQL> @check_sess.sql
enter date in format "DD/MM/YYYY HH24:MI:SS" d1:
Enter value for d1: '23/12/2018 12:50:00'

PL/SQL procedure successfully completed.

enter date in format "DD/MM/YYYY HH24:MI:SS" d2:
Enter value for d2: '23/12/2018 13:15:00'

PL/SQL procedure successfully completed.


INSTANCE_NUMBER,SAMPLE_TIME                                                                ,  COUNT(1)
---------------,---------------------------------------------------------------------------,----------
              1,23-DEC-18 12.56.33.462 PM                                                  ,         1
              1,23-DEC-18 01.07.24.256 PM                                                  ,         1



To test its effectiveness, let us query v$active_session_history..

with dhash as
(select inst_id instance_number,sample_time,session_id,session_serial#
from gv$active_session_history
where sample_time between to_date(:d1,'DD/MM/YYYY HH24:MI:SS') and to_date(:d2,'DD/MM/YYYY HH24:MI:SS')
order by instance_number,sample_time,session_id,session_serial#)
select a.instance_number,a.sample_time,count(1)
from dhash a
,(select instance_number,samp_time,lag(samp_time,1) over (order by samp_time) lag_time
from
(
select instance_number,sample_time samp_time
from dhash
group by instance_number,sample_time
order by 1,2)) c
where a.sample_time=c.samp_time
and a.instance_number=c.instance_number
and (a.instance_number,a.session_id,a.SESSION_SERIAL#) not in (select instance_number,session_id,sESSION_SERIAL# from dhash where sample_time=c.lag_time and instance_number=c.instance_number)
group by a.instance_number,a.sample_time
order by 1,2;

SQL> @check_sess_eff.sql
enter date in format "DD/MM/YYYY HH24:MI:SS" d1:
Enter value for d1: '23/12/2018 12:50:00'

PL/SQL procedure successfully completed.

enter date in format "DD/MM/YYYY HH24:MI:SS" d2:
Enter value for d2: '23/12/2018 13:39:00'

PL/SQL procedure successfully completed.


INSTANCE_NUMBER,SAMPLE_TIME                                                                ,  COUNT(1)
---------------,---------------------------------------------------------------------------,----------
              1,23-DEC-18 12.51.44.123 PM                                                  ,         1
              1,23-DEC-18 12.56.17.452 PM                                                  ,         1
              1,23-DEC-18 12.56.29.462 PM                                                  ,         1
              1,23-DEC-18 01.00.45.758 PM                                                  ,         2
              1,23-DEC-18 01.01.45.838 PM                                                  ,         1
              1,23-DEC-18 01.02.29.888 PM                                                  ,         1
              1,23-DEC-18 01.07.24.256 PM                                                  ,         1
              1,23-DEC-18 01.11.47.603 PM                                                  ,         2
              1,23-DEC-18 01.16.04.913 PM                                                  ,         1
              1,23-DEC-18 01.17.30.013 PM                                                  ,         1
              1,23-DEC-18 01.22.49.395 PM                                                  ,         1
              1,23-DEC-18 01.23.10.415 PM                                                  ,         1
              1,23-DEC-18 01.26.56.704 PM                                                  ,         1
              1,23-DEC-18 01.27.45.771 PM                                                  ,         2
              1,23-DEC-18 01.29.46.017 PM                                                  ,         1
              1,23-DEC-18 01.29.51.027 PM                                                  ,         2
              1,23-DEC-18 01.29.57.027 PM                                                  ,         1
              1,23-DEC-18 01.30.03.037 PM                                                  ,         1
              1,23-DEC-18 01.30.09.037 PM                                                  ,         1
              1,23-DEC-18 01.30.15.047 PM                                                  ,         1
              1,23-DEC-18 01.30.21.057 PM                                                  ,         1
              1,23-DEC-18 01.30.27.067 PM                                                  ,         1
              1,23-DEC-18 01.30.28.067 PM                                                  ,         1
              1,23-DEC-18 01.30.33.077 PM                                                  ,         1
              1,23-DEC-18 01.30.38.077 PM                                                  ,         1
              1,23-DEC-18 01.37.08.690 PM                                                  ,         1
              1,23-DEC-18 01.37.15.690 PM                                                  ,         1
              1,23-DEC-18 01.37.21.700 PM                                                  ,         1
              1,23-DEC-18 01.37.25.710 PM                                                  ,         3
              1,23-DEC-18 01.37.28.782 PM                                                  ,         2
              1,23-DEC-18 01.37.29.802 PM                                                  ,         1
              1,23-DEC-18 01.37.30.802 PM                                                  ,         1
              1,23-DEC-18 01.37.38.812 PM                                                  ,         1
              1,23-DEC-18 01.37.44.842 PM                                                  ,         1
              1,23-DEC-18 01.37.51.862 PM                                                  ,         1
              1,23-DEC-18 01.37.57.872 PM                                                  ,         1
              1,23-DEC-18 01.38.04.872 PM                                                  ,         1
              1,23-DEC-18 01.38.11.892 PM                                                  ,         1
              1,23-DEC-18 01.38.52.942 PM                                                  ,         1

39 rows selected.

step 4) validate...

select inst_id,sample_time,session_id,session_serial#
from gv$active_session_history
where sample_Time between sysdate-1/24 and sysdate-.5/24
order by inst_id,sample_time,session_id,session_serial#
/

   INST_ID,SAMPLE_TIME                                                                ,SESSION_ID,SESSION_SERIAL#
----------,---------------------------------------------------------------------------,----------,---------------
...
         1,23-DEC-18 01.37.20.700 PM                                                  ,       133,            135
         1,23-DEC-18 01.37.21.700 PM                                                  ,       133,            137    << new record 1
         1,23-DEC-18 01.37.22.710 PM                                                  ,       133,            137
         1,23-DEC-18 01.37.23.710 PM                                                  ,       133,            137
         1,23-DEC-18 01.37.24.710 PM                                                  ,       133,            137
         1,23-DEC-18 01.37.25.710 PM                                                  ,        15,            136    << new record 1
         1,23-DEC-18 01.37.25.710 PM                                                  ,       132,              1    << new record 2
         1,23-DEC-18 01.37.25.710 PM                                                  ,       133,            137    << repeat record
         1,23-DEC-18 01.37.25.710 PM                                                  ,       142,            277    << new record 3
         1,23-DEC-18 01.37.26.720 PM                                                  ,       133,            137
         1,23-DEC-18 01.37.26.720 PM                                                  ,       142,            277
         1,23-DEC-18 01.37.27.762 PM                                                  ,       133,            137
         1,23-DEC-18 01.37.27.762 PM                                                  ,       142,            277
         1,23-DEC-18 01.37.28.782 PM                                                  ,        19,             13
         1,23-DEC-18 01.37.28.782 PM                                                  ,       132,              1
         1,23-DEC-18 01.37.28.782 PM                                                  ,       133,            137
         1,23-DEC-18 01.37.28.782 PM                                                  ,       142,            277
         1,23-DEC-18 01.37.29.802 PM                                                  ,       139,             47
         1,23-DEC-18 01.37.29.802 PM                                                  ,       142,            277
         1,23-DEC-18 01.37.30.802 PM                                                  ,        19,             17

so it works fine.
There is one use case of this method of printing the session count is - the sessions which are established locally from the server are also traced where as listener doesnt know them.

If your intrested in knowing the cost difference between version 1 & 2 of this blog is...

version 1:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2908163802

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |    61 |  5612 |   167   (2)| 00:00:03 |       |       |
|   1 |  SORT GROUP BY NOSORT          |                             |    61 |  5612 |   167   (2)| 00:00:03 |       |       |
|*  2 |   FILTER                       |                             |       |       |            |          |       |       |
|   3 |    NESTED LOOPS                |                             |    62 |  5704 |    13  (24)| 00:00:01 |       |       |

version 2:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 386194343

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |     2 |    98 |    16  (32)| 00:00:01 |       |       |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |       |       |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6610_42F9DE   |       |       |            |          |       |       |
|   3 |    SORT ORDER BY           |                             |     2 |   136 |     6  (17)| 00:00:01 |       |       |
|*  4 |     FILTER                 |                             |       |       |            |          |       |       |

There is a huge difference in cost, I am sure the run duration of version 2 will be far better than the first one.

Thank you & best wishes.