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.

version 1: 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, but let us try this for fun.


step 1) Write a query to get the sample time.

select distinct(sample_time)
from dba_hist_active_sess_history
order by 1;

Step 2) Write a wrapper on top of step 1 to fetch sample time and its lag

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

Step 3) Write a wrapper on top of the step 2 to fetch sid,serial# from dba_hist_active_Sess_history for both lag and actual sample time and count the difference.

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;

==> works well.

to test its effectiveness... replaced dba_hist with v$ to query the current info for every second sample.

select a.sample_time
,count(1)
from v$active_session_history a
,(select samp_time,lag(samp_time,1) over (order by samp_time) lag_time
from
(
select distinct(sample_time) samp_time
from v$active_session_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 v$active_session_history where sample_time=c.lag_time)
group by a.sample_time
order by 1;


sample record from v$active_session_history...

21-DEC-18 11.31.39.436 AM                                                          136             188
21-DEC-18 11.31.40.436 AM                                                          136             188    << repeating all the time
21-DEC-18 11.31.41.436 AM                                                           12               3    << new 1
21-DEC-18 11.31.41.436 AM                                                           21               5    << new 2
21-DEC-18 11.31.41.436 AM                                                          128               1    << new 3
21-DEC-18 11.31.41.436 AM                                                          136             188    << repeating
21-DEC-18 11.31.42.446 AM                                                          136             188
21-DEC-18 11.31.43.446 AM                                                          136             188
21-DEC-18 11.31.44.446 AM                                                          136             188
21-DEC-18 11.31.45.446 AM                                                          136             188
21-DEC-18 11.31.46.446 AM                                                          136             188
21-DEC-18 11.31.47.446 AM                                                          136             188
21-DEC-18 11.31.48.446 AM                                                          136             188
21-DEC-18 11.31.49.446 AM                                                          136             188
21-DEC-18 11.31.50.446 AM                                                          136             188
21-DEC-18 11.31.51.446 AM                                                          136             188
21-DEC-18 11.31.52.446 AM                                                          136             188
21-DEC-18 11.31.53.456 AM                                                          136             188
21-DEC-18 11.31.54.456 AM                                                          136             188
21-DEC-18 11.31.55.456 AM                                                          136             188
21-DEC-18 11.31.56.456 AM                                                          136             188
21-DEC-18 11.31.57.456 AM                                                          136             188
21-DEC-18 11.31.58.456 AM                                                          136             188
21-DEC-18 11.31.59.456 AM                                                          136             188
21-DEC-18 11.32.00.456 AM                                                          136             188
21-DEC-18 11.32.01.466 AM                                                          136             188
21-DEC-18 11.32.02.476 AM                                                          136             188
21-DEC-18 11.32.03.476 AM                                                          136             188
21-DEC-18 11.32.04.486 AM                                                          136             188
21-DEC-18 11.32.05.486 AM                                                          136             188
21-DEC-18 11.32.06.486 AM                                                          136             188
21-DEC-18 11.32.07.486 AM                                                          136             188
21-DEC-18 11.32.08.486 AM                                                          136             188
21-DEC-18 11.32.09.486 AM                                                          136             188
21-DEC-18 11.32.10.486 AM                                                          136             188
21-DEC-18 11.32.11.496 AM                                                          136             188
21-DEC-18 11.32.41.556 AM                                                           12               3  << new 1
21-DEC-18 11.35.00.897 AM                                                           14              20    << new 1


so ideally we should have 21-DEC-18 11.31.41.436 AM (3) and 21-DEC-18 11.32.41.556 AM  & 21-DEC-18 11.35.00.897 AM (1) each. Other samples had repeating value, so should be skipped.

SAMPLE_TIME                                                                   COUNT(1)
--------------------------------------------------------------------------- ----------
21-DEC-18 10.47.14.345 AM                                                            2
21-DEC-18 10.47.17.365 AM                                                            2
..
21-DEC-18 11.30.27.236 AM                                                            1
21-DEC-18 11.30.31.256 AM                                                            1
21-DEC-18 11.30.39.266 AM                                                            1
21-DEC-18 11.30.41.276 AM                                                            1
21-DEC-18 11.30.42.276 AM                                                            1
21-DEC-18 11.31.21.396 AM                                                            1
21-DEC-18 11.31.41.436 AM                                                            3    << 3 as expected
21-DEC-18 11.32.41.556 AM                                                            1    << 1 as expected
21-DEC-18 11.35.00.897 AM                                                            1    << 1 as expected

So this works as expected.

Thanks for paying a visit.
All the best.