Check the resource limit of session and processes in Oracle
With help of V$RESOURCE_LIMIT view, we can see the current utilization and MAX utilization of resources from last startup.
DESC V$RESOURCE_LIMIT
Name Type
--------------------- --------------
RESOURCE_NAME VARCHAR2(30)
CURRENT_UTILIZATION NUMBER
MAX_UTILIZATION NUMBER
INITIAL_ALLOCATION VARCHAR2(40)
LIMIT_VALUE VARCHAR2(40)
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)
Check session and process limit from Oracle Startup
select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name in( 'sessions', 'processes' ;
Check the history table of Resource Limit
select * from DBA_HIST_RESOURCE_LIMIT where resource_name in ('sessions','processes');
Get history of resource utilization with time
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
Suppose my max utilization show me the value of 800 session connected then i want to check when & what time i am getting maximum session or my peak time of application.
Then i use the query to find the snapshot id and timing by defining current_utilization column in where clause to see that timing.
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;