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

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 )

Connecting to %s

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