Check the resource limit of session and processes in Oracle
With the V$RESOURCE_LIMIT view, we can see the current and maximum resource usage since the last startup.
DESC V$RESOURCE_LIMIT
Meaning of columns:
RESOURCE_NAME: Name of the resource
CURRENT_UTILIZATION : Number of (resources, locks, or processes) currently being used
MAX_UTILIZATION : Maximum consumption of this resource since the last instance start-up
INITIAL_ALLOCATION : Initial allocation. This will be equal to the value specified for the resource in the initialization parameter file (UNLIMITED for infinite allocation).
LIMIT_VALUE : Unlimited for resources and locks. This can be greater than the initial allocation value (UNLIMITED for infinite limit)Monitoring Oracle Session and Process Limits
Check the processes current, max utilization in Oracle
select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name = 'sessions';
select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name = 'processes';Check the history table DBA_HIST_RESOURCE_LIMIT for check session and process values in Oracle.
Check the old value of history table according to snapshot id.
select * from DBA_HIST_RESOURCE_LIMIT where resource_name in ('sessions','processes');How to check the old value of process and session with timestamp from DBA_HIST_RESOURCE_LIMIT table in Oracle
col snapshottime for a20
col resource_name for a20
select s.SNAP_ID,TO_CHAR(s.END_INTERVAL_TIME, 'DD-MON HH24:MI:SS') Snapshottime,resource_name,current_utilization,max_utilization,limit_value from DBA_HIST_SNAPSHOT s, DBA_HIST_RESOURCE_LIMIT b
where s.snap_id = b.snap_id and resource_name in ('sessions') order by 2;Find timing and snapshot of maximum session connected to database
To find the peak usage time when I have 800 sessions connected, I check the snapshot ID and timing by using a query that defines the current_utilization column in the where clause.
col snapshottime for a20
col resource_name for a20
select s.SNAP_ID,TO_CHAR(s.END_INTERVAL_TIME, 'DD-MON HH24:MI:SS') Snapshottime,resource_name,current_utilization,max_utilization,limit_value from DBA_HIST_SNAPSHOT s, DBA_HIST_RESOURCE_LIMIT b
where s.snap_id = b.snap_id and resource_name in ('sessions') and current_utilization > 600 order by 2;