Tag Archives: Find timing and snapshot of maximum session connected to database

Check the resource limit of session and processes in Oracle

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 = 'sessions';

select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name = '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;

Advertisements