Check the resource limit of session and processes in Oracle: In this article we will see about how to Check the resource limit of session and processes in Oracle. This view displays information about global resource use for some of the system resources. Use this view to monitor the consumption of resources so that you can take corrective action, if necessary.
Describe V$RESOURCE_LIMIT
SQL>
SQL> desc v$RESOURCE_LIMIT
Name Null? Type
----------------------------------------- -------- ----------------------------
RESOURCE_NAME VARCHAR2(128)
CURRENT_UTILIZATION NUMBER
MAX_UTILIZATION NUMBER
INITIAL_ALLOCATION VARCHAR2(10)
LIMIT_VALUE VARCHAR2(10)
CON_ID NUMBER
SQL>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 :
SQL> set lines 400 pages 400
SQL>
SQL> col RESOURCE_NAME for a30
SQL> col LIMIT_VALUE for a30
SQL>
SQL> select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name = 'sessions';
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------------------------ ------------------- --------------- ------------------------------
sessions 61 69 472
SQL>
SQL>
SQL> select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name = 'processes';
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------------------------ ------------------- --------------- ------------------------------
processes 53 59 300
SQL>
Check the history table of Resource Limit :
SQL>
SQL> select * from DBA_HIST_RESOURCE_LIMIT where resource_name in ('sessions','processes');
no rows selected
SQL>
Get history of resource utilization with time :
SQL>
SQL> set lines 400 pages 400
SQL> col snapshottime for a20
SQL> col resource_name for a20
SQL> select s.SNAP_ID,TO_CHAR(s.END_INTERVAL_TIME, 'DD-MON HH24:MI:SS') Snapshottime,resource_name,current_utilization,max_utilization,limit_value
2 from DBA_HIST_SNAPSHOT s, DBA_HIST_RESOURCE_LIMIT b
3 where s.snap_id = b.snap_id and resource_name in ('sessions') order by 2;
no rows selected
SQL>
Find timing and snapshot of maximum session connected to the 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.
SQL>
SQL> set lines 400 pages 400
SQL> col snapshottime for a20
SQL> col resource_name for a20
SQL> select s.SNAP_ID,TO_CHAR(s.END_INTERVAL_TIME, 'DD-MON HH24:MI:SS') Snapshottime,resource_name,current_utilization,max_utilization,limit_value
2 from DBA_HIST_SNAPSHOT s, DBA_HIST_RESOURCE_LIMIT b
3 where s.snap_id = b.snap_id and resource_name in ('sessions') and current_utilization > 600 order by 2;
no rows selected
SQL>
I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You could write to mail me through : orakldba@gmail.com.