Tag: MGMT$METRIC_DAILY

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

Database Size History Report From OEM

DBA is always being asked about the database size growth trend for coming months by a client or reporting manager.

If you can present the following report, that would show your great service.

Now lets’ demonstrate how to get similar report for maximum up to two years period of time from OEM repository.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe