3

I need to know how many sessions was connected in a period of time for each snapshot. Is this possible in Oracle?

Thanks

6

You can find this information in DBA_HIST_RESOURCE_LIMIT. Using this view requires the Diagnostic Pack option.

For example, sessions in the last 1 day:

select
  s.instance_number,
  s.end_interval_time,
  rn.current_utilization
from
  dba_hist_snapshot s
  join dba_hist_resource_limit rn on (s.snap_id = rn.snap_id and s.instance_number = rn.instance_number)
where
  rn.resource_name = 'sessions'
  and s.end_interval_time > sysdate - 1
order by
  s.end_interval_time
;

INSTANCE_NUMBER END_INTERVAL_TIME         CURRENT_UTILIZATION
--------------- ------------------------- -------------------
              1 07-JUL-16 03.00.23.658 PM                  74
              1 07-JUL-16 04.00.25.330 PM                  74
              1 07-JUL-16 05.00.27.072 PM                  74
              1 07-JUL-16 06.00.28.807 PM                  73
              1 07-JUL-16 07.00.30.589 PM                  73
              1 07-JUL-16 08.00.32.354 PM                  73
              1 07-JUL-16 09.00.34.187 PM                  73
              1 07-JUL-16 10.00.36.100 PM                  79
              1 07-JUL-16 11.00.38.061 PM                  73
              1 08-JUL-16 12.00.39.854 AM                  73
              1 08-JUL-16 01.00.41.673 AM                  73
              1 08-JUL-16 02.00.43.523 AM                  75
              1 08-JUL-16 03.00.45.355 AM                  73
              1 08-JUL-16 04.00.47.141 AM                  73
              1 08-JUL-16 05.00.48.895 AM                  73
              1 08-JUL-16 06.00.50.639 AM                  73
              1 08-JUL-16 07.00.52.367 AM                  73
              1 08-JUL-16 08.00.54.128 AM                  73
              1 08-JUL-16 09.00.55.850 AM                  73
              1 08-JUL-16 10.00.57.572 AM                  73
              1 08-JUL-16 11.00.59.295 AM                  73
              1 08-JUL-16 12.00.00.949 PM                  73
              1 08-JUL-16 01.00.02.704 PM                  73
              1 08-JUL-16 02.00.04.485 PM                  73
|improve this answer|||||
  • It's worth mentioning that the required Diagnostic Pack also requires the enterprise edition. – a_horse_with_no_name Jul 8 '16 at 12:46
  • I have seen the MAX_UTILIZATION column and I have a question. The current and max utilization are distinct. What is the diference between them?. – estonolose Jul 8 '16 at 13:05
  • @estonolose MAX_UTILIZATION is the maximum value since the instance startup. – Balazs Papp Jul 8 '16 at 13:13
3

You can find this in v$session:

select count(*)
from   v$session;

It shows you how many sessions are there at this very moment. If you need it for statistical/historical reasons then you need to create a procedure, using this SQL, that runs every x-time and store the result in a table.

You can also install the Oracle Diagnostics Pack. This has the Active Session History feature that logs every second and 'dumps' the active sessions and relevant information about each of them. This is however an extra payable option.

|improve this answer|||||
  • I want a historical between two dates. I don't want the sessions now. – estonolose Jul 8 '16 at 12:29
  • You could have a schedule that runs an insert similar to that query and also includes the sysdate, inserting into a reporting table: insert into monitoring_id.session_counts (select sysdate, count(*) from v$session); – Mark Stewart Jul 8 '16 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.