How to Check Oracle Database Current Number of Sessions / Processes and Maximum Utilization

1)From V$LICENSE View. It shows USER sessions only excluding database system sessions.

SQL> select SESSIONS_CURRENT,SESSIONS_HIGHWATER from v$license;

SESSIONS_CURRENT SESSIONS_HIGHWATER 
---------------- ------------------ 
 771             1346 

-- SESSIONS_CURRENT   :Current number of concurrent user sessions 
-- SESSIONS_HIGHWATER :Highest number of concurrent user sessions 
 since the instance started.

2) From v$resource_limit view. It shows both users and database system sessions.

SQL> select 
            RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE 
     from 
            v$resource_limit 
     where 
            RESOURCE_NAME in ('processes','sessions');

RESOURCE_NAME  CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
-------------- ------------------- --------------- ---------------
processes      842                 1414            4040
sessions       865                 1439            6096

-- CURRENT_UTILIZATION : Number of (resources, locks, or processes) 
                         currently being used.
-- MAX_UTILIZATION : Maximum consumption of this resource since 
                     the last instance start-up

3)From AWR.

col STARTT format a20
col FINISHH format a20
col RESOURCE_NAME format a13

SQL>select     
           a.snap_id SNAP_ID,
           to_char(b.begin_interval_time,'YYYYMMDD-HH24:MI:SS')  STARTT,
           to_char(b.end_interval_time,'YYYYMMDD-HH24:MI:SS') FINISHH,
           a.resource_name,
           a.CURRENT_UTILIZATION,
           a.max_utilization ,
           a.INITIAL_ALLOCATION,
           a.LIMIT_VALUE
from       
           DBA_HIST_RESOURCE_LIMIT a, 
           DBA_HIST_SNAPSHOT b
where      
          ( a.resource_name like '%process%' or a.resource_name like '%session%')
and        a.snap_id=b.snap_id
and         a.instance_number=b.instance_number
and         a.instance_number=1
and         b.begin_interval_time > sysdate - 2
order by
         a.snap_id,
         a.resource_name;

SNAP_ID    STARTT               FINISHH              RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
---------- -------------------- -------------------- ------------- ------------------- --------------- ---------- ----------
 63141     20171214-10:00:13    20171214-11:00:15    processes     845 1414 4040 4040
 63141     20171214-10:00:13    20171214-11:00:15    sessions      869 1439 6096 6096
 63142     20171214-11:00:15    20171214-12:00:18    processes     847 1414 4040 4040
 63142     20171214-11:00:15    20171214-12:00:18    sessions      871 1439 6096 6096
 63143     20171214-12:00:18    20171214-13:00:21    processes     845 1414 4040 4040
 63143     20171214-12:00:18    20171214-13:00:21    sessions      870 1439 6096 6096
 63144     20171214-13:00:21    20171214-14:00:23    processes     842 1414 4040 4040
 63144     20171214-13:00:21    20171214-14:00:23    sessions      866 1439 6096 6096
 63145     20171214-14:00:23    20171214-15:00:26    processes     843 1414 4040 4040
 63145     20171214-14:00:23    20171214-15:00:26    sessions      869 1439 6096 6096
 63146     20171214-15:00:26    20171214-16:00:29    processes     852 1414 4040 4040
 63146     20171214-15:00:26    20171214-16:00:29    sessions      879 1439 6096 6096
 63147     20171214-16:00:29    20171214-17:00:32    processes     849 1414 4040 4040
 63147     20171214-16:00:29    20171214-17:00:32    sessions      873 1439 6096 6096
 63148     20171214-17:00:32    20171214-18:00:35    processes     848 1414 4040 4040
 63148     20171214-17:00:32    20171214-18:00:35    sessions      872 1439 6096 6096
 63149     20171214-18:00:35    20171214-19:00:02    processes     840 1414 4040 4040
 63149     20171214-18:00:35    20171214-19:00:02    sessions      864 1439 6096 6096
 63150     20171214-19:00:02    20171214-20:00:05    processes     841 1414 4040 4040
 63150     20171214-19:00:02    20171214-20:00:05    sessions      865 1439 6096 6096
 63151     20171214-20:00:05    20171214-21:00:08    processes     845 1414 4040 4040
 63151     20171214-20:00:05    20171214-21:00:08    sessions      871 1439 6096 6096
 63152     20171214-21:00:08    20171214-22:00:12    processes     850 1414 4040 4040
 63152     20171214-21:00:08    20171214-22:00:12    sessions      875 1439 6096 6096
 63153     20171214-22:00:12    20171214-23:00:15    processes     854 1414 4040 4040
 63153     20171214-22:00:12    20171214-23:00:15    sessions      880 1439 6096 6096
 63154     20171214-23:00:15    20171215-00:00:17    processes     849 1414 4040 4040
 63154     20171214-23:00:15    20171215-00:00:17    sessions      873 1439 6096 6096
 63155     20171215-00:00:17    20171215-01:00:21    processes     851 1414 4040 4040
 63155     20171215-00:00:17    20171215-01:00:21    sessions      877 1439 6096 6096
 63156     20171215-01:00:21    20171215-02:00:24    processes     837 1414 4040 4040
 63156     20171215-01:00:21    20171215-02:00:24    sessions      861 1439 6096 6096
 63157     20171215-02:00:24    20171215-03:00:27    processes     847 1414 4040 4040
 63157     20171215-02:00:24    20171215-03:00:27    sessions      872 1439 6096 6096
 63158     20171215-03:00:27    20171215-04:00:01    processes     846 1414 4040 4040
 63158     20171215-03:00:27    20171215-04:00:01    sessions      872 1439 6096 6096
 63159     20171215-04:00:01    20171215-05:00:04    processes     841 1414 4040 4040
 63159     20171215-04:00:01    20171215-05:00:04    sessions      865 1439 6096 6096
 63160     20171215-05:00:04    20171215-06:00:06    processes     839 1414 4040 4040
 63160     20171215-05:00:04    20171215-06:00:06    sessions      864 1439 6096 6096
 63161     20171215-06:00:06    20171215-07:00:09    processes     847 1414 4040 4040
 63161     20171215-06:00:06    20171215-07:00:09    sessions      871 1439 6096 6096
 63162     20171215-07:00:09    20171215-08:00:12    processes     843 1414 4040 4040
 63162     20171215-07:00:09    20171215-08:00:12    sessions      866 1439 6096 6096
 63163     20171215-08:00:12    20171215-09:00:14    processes     852 1414 4040 4040
 63163     20171215-08:00:12    20171215-09:00:14    sessions      878 1439 6096 6096

46 rows selected.

4) From OEM Repository.

 col COLUMN_LABEL format a30
 col MINIMUM format 99.99
 col MAXIMUM format 99.99
 col AVERAGE format 99.99

SQL>select 
           to_char(ROLLUP_TIMESTAMP,'YYYY-MM-DD'), COLUMN_LABEL, 
           MINIMUM,MAXIMUM,AVERAGE 
    from   MGMT$METRIC_DAILY 
   where   TARGET_NAME='RACTEST' 
     and   COLUMN_LABEL in ('Session Limit Usage (%)') 
   order by 
           ROLLUP_TIMESTAMP,
           COLUMN_LABEL;

TO_CHAR(RO COLUMN_LABEL                   MINIMUM MAXIMUM AVERAGE
---------- ------------------------------ ------- ------- -------
2017-07-15 Session Limit Usage (%)        2.87    6.40    5.22
2017-07-16 Session Limit Usage (%)        6.45   14.57   13.67
2017-07-17 Session Limit Usage (%)        7.96   14.40   14.07
2017-07-18 Session Limit Usage (%)       13.99   14.71   14.34
2017-07-19 Session Limit Usage (%)       14.14   14.94   14.63
2017-07-20 Session Limit Usage (%)       14.55   15.04   14.80
...
..
.
2017-12-04 Session Limit Usage (%)       14.88   15.88   15.47
2017-12-05 Session Limit Usage (%)       14.45   15.16   14.80
2017-12-06 Session Limit Usage (%)       13.99   14.98   14.58
2017-12-07 Session Limit Usage (%)       13.86   14.32   14.09
2017-12-08 Session Limit Usage (%)       13.88   14.37   14.12
2017-12-09 Session Limit Usage (%)       13.80   14.40   14.04
2017-12-10 Session Limit Usage (%)       13.91   14.44   14.06
2017-12-11 Session Limit Usage (%)       13.88   14.35   14.07
2017-12-12 Session Limit Usage (%)       13.96   14.30   14.12
2017-12-13 Session Limit Usage (%)       13.98   14.44   14.18
2017-12-14 Session Limit Usage (%)       14.09   14.42   14.24

--
--

SQL> select 
            to_char(ROLLUP_TIMESTAMP,'YYYY-MM-DD'), COLUMN_LABEL, 
            MINIMUM,MAXIMUM,AVERAGE 
       from MGMT$METRIC_DAILY 
      where TARGET_NAME='RACTEST' 
        and COLUMN_LABEL in ('Process Limit Usage (%)') 
   order by ROLLUP_TIMESTAMP,
            COLUMN_LABEL;

TO_CHAR(RO COLUMN_LABEL                   MINIMUM MAXIMUM AVERAGE
---------- ------------------------------ ------- ------- -------
2017-07-15 Process Limit Usage (%)        3.76    9.16    7.33
2017-07-16 Process Limit Usage (%)        9.21   21.31   20.00
2017-07-17 Process Limit Usage (%)       11.34   21.09   20.60
2017-07-18 Process Limit Usage (%)       20.50   21.49   21.00
2017-07-19 Process Limit Usage (%)       20.74   21.83   21.44
2017-07-20 Process Limit Usage (%)       21.36   22.00   21.71
...
..
.
2017-12-09 Process Limit Usage (%)       20.25   20.99   20.58
2017-12-10 Process Limit Usage (%)       20.40   21.16   20.61
2017-12-11 Process Limit Usage (%)       20.37   21.01   20.63
2017-12-12 Process Limit Usage (%)       20.47   20.97   20.70
2017-12-13 Process Limit Usage (%)       20.52   21.06   20.78
2017-12-14 Process Limit Usage (%)       20.72   21.11   20.89

 

Advertisements
This entry was posted in OEM12c, OEM13c, SQL Scripts For DBA and tagged , , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.