SQL QUERY TO FIND PROCESS AND SESSION RESOURCE LIMIT ORACLE ORA-00020

SQL QUERY TO FIND PROCESS AND SESSION RESOURCE LIMIT ORACLE ORA-00020

If there are concurrent sessions and process connections overflow in your database,then you might get into trouble of customer complaint because of performance or connectivity issues. This query will help you to get limit compared with current and max utilization of processes and sessions

SQL Query:

col RESOURCE_NAME format a20
col LIMIT_VALUE format 9999999
col CURRENT_UTILIZATION format 9999999
col MAX_UTILIZATION format 9999999
select RESOURCE_NAME,LIMIT_VALUE,CURRENT_UTILIZATION,MAX_UTILIZATION from v$resource_limit where RESOURCE_NAME in ('processes','sessions');

Output:

If current util reaches near limit value column of v$resource_limit, then you need to increase the number of processes in your database system

kish@exdbx<>col RESOURCE_NAME format a20
col LIMIT_VALUE format 9999999
col CURRENT_UTILIZATION format 9999999
col MAX_UTILIZATION format 9999999
select RESOURCE_NAME,LIMIT_VALUE,CURRENT_UTILIZATION,MAX_UTILIZATION from v$resource_limit where RESOURCE_NAME in ('processes','sessions');kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>

RESOURCE_NAME        LIMIT_VALUE                              CURRENT_UTILIZATION MAX_UTILIZATION
-------------------- ---------------------------------------- ------------------- ---------------
processes                   150                                                53              57
sessions                    247                                                58              67

Example: limit value=150 and current utilization >= 145 then execute the below command

Note: Choose the processes value based on the below calculation.You have to analyse the historical trends on the number of sessions and processes connected statistics and decide the value accordingly. It would be recommended to set double the processes as per the number of sessions. Based on the process parameter ,the sessions would be adjusted automatically by oracle

sessions = (1.5 * processes) +22 ==> processes = (sessions – 22)/1.5

SQL> select count(*) from v$session;

  COUNT(*)
----------
       342  --> there are more sessions than current session parameter(247)

--derive the value of sessions based on the parameters processes(150)

SQL> select (1.5*150)+22 as optimum_sessions from dual;

OPTIMUM_SESSIONS
----------------
             247

-- As we can see that the number of sessions exceeded the parameter value 'sessions'(247)

SQL> select count(*) from v$process;

  COUNT(*)
----------
        50



--derive the value of sessions based on the parameters sessions(247)

SQL> select (247-22)/1.5 as optimum_processes from dual;  -- so we have to increase the process parameter accordingly to 342 sessions

OPTIMUM_PROCESSES
-----------------
              150

-- processes parameter to be adjusted as per max sessions (342-22)/1.5 ~ 214 processes parameter value 

-- it is ok to increase the parameter to 300 as a buffer value instead of 214 
SQL> alter system set processes=300 scope=spfile;

System altered.

Work with application team to determine the right value for parallel_max_servers parameter

Another solution would be to reduce the number of non critical concurrent connections by notifying the application owner so that they can perform the low priority tasks during offpeak hours