WHY MY DATABASE HAS SO MANY PROCESSES CONNECTED

 
REQUEST

 
DETAILS

The following query shows that my instance has almost reached the number of permited processes.

SET LINES 300
SET PAGES 999
COL RESOURCE_NAME FOR A15
COL CURRENT FOR 999999
COL MAX FOR 99999
COL LIMIT FOR 99999

SELECT RESOURCE_NAME,
       CURRENT_UTILIZATION "CURRENT",
       MAX_UTILIZATION "MAX_REACHED", 
       LIMIT_VALUE "LIMIT",
       ROUND(CURRENT_UTILIZATION/LIMIT_VALUE*100,1) PCT_USED
FROM 
       V$RESOURCE_LIMIT
WHERE
       RESOURCE_NAME IN ( 'sessions', 'processes');

RESOURCE_NAME   CURRENT MAX_REACHED     LIMIT    PCT_USED
--------------- ------- ----------- ---------- ----------
processes           799         800        800       99.9
sessions            815         822       1224       66.6

 
Where

CURRENT is a current number of processes in a database right now.
MAX_REACHED is a maximum number of processes reached since an instance was started.
LIMIT is what you cannot go higher than.
PCT_USED is how many processes in percentage of permitted limit.

 
This query shows how many processes are in database grouped by username and status

SET LINES 300
SET PAGES 999
COL DATABASE_USER FOR A30 
COL STATUS FOR A10 
COL COUNT FOR 9999999

WITH SESSIONS_COUNT AS (
SELECT NVL(S.USERNAME,'BACKGROUND PROCESS') DATABASE_USER,
       S.STATUS,
       COUNT(S.STATUS) "COUNT"
FROM
       V$SESSION S, V$PROCESS P
WHERE
      S.PADDR=P.ADDR
GROUP BY S.USERNAME,S.STATUS ORDER BY 1
),
TOTAL AS (
SELECT 'TOTAL','',COUNT(*) FROM V$PROCESS
)
SELECT * FROM SESSIONS_COUNT
UNION ALL 
SELECT * FROM TOTAL;

DATABASE_USER                  STATUS        COUNT
------------------------------ ---------- --------
BACKGROUND PROCESS             ACTIVE           33
DBSNMP                         INACTIVE          3
STARLIMS_DATA                  INACTIVE        390
STARLIMS_DATA                  ACTIVE            1
STARLIMS_DICT_PROD             INACTIVE        371
SYS                            ACTIVE            1
TOTAL                                          799

7 rows selected.

 
In this example most of all allocated processes are consumed by 2 users STARLIMS_DATA and STARLIMS_DICT_PROD and most of sessions for those users are inactive.

 
Now let’s find out how long those sessions are inactive

SET LINES 300
SET PAGES 999
COL OSUSER FOR A20
COL DATABASE_USER FOR A20
COL MACHINE FOR A20
COL MODULE FOR A10
COL PROGRAM FOR A40
COL INACTIVE_TIME FOR A15

------------------------------------------------------------
-- Victor Khalturin
-- Version 16:10 12.08.2020 (001)
--
-- Find out all inactive sessions/processes and their idle time.
------------------------------------------------------------
SELECT S.OSUSER,S.USERNAME DATABASE_USER,S.STATUS,S.MACHINE,
       S.PORT,S.PROGRAM,
	   CASE  
	        WHEN LAST_CALL_ET< 60 THEN LAST_CALL_ET || ' Seconds'
            WHEN LAST_CALL_ET< 3600 THEN ROUND(LAST_CALL_ET/60) || ' Minutes'
            WHEN LAST_CALL_ET< 86400 THEN ROUND(LAST_CALL_ET/60/60,1) || ' Hour(s)'
            ELSE ROUND(LAST_CALL_ET/60/60/24,1) || ' Day(s)'
       end INACTIVE_TIME
FROM
       V$SESSION S, V$PROCESS P
WHERE
      S.PADDR=P.ADDR AND S.STATUS = 'INACTIVE'
      -- To find all session that are inactive more than 1 hour
      -- AND S.LAST_CALL_ET  >= 3600
ORDER BY LAST_CALL_ET DESC;

OSUSER               DATABASE_USER        STATUS     MACHINE                    PORT PROGRAM                                  INACTIVE_TIME
-------------------- -------------------- ---------- -------------------- ---------- ---------------------------------------- ---------------
EliseevON            STARLIMS_DATA        INACTIVE   s000as-DBPILOT             60078 SQL Developer                            43.4 Day(s)
s000as-DBPILOT       STARLIMS_DICT_PROD   INACTIVE   APPS\s000as-DBPILOT        55785 Starlims.Server.WorkerProcess.exe        1.5 Day(s)
s000as-DBPILOT       STARLIMS_DICT_PROD   INACTIVE   APPS\s000as-DBPILOT        55872 Starlims.Server.WorkerProcess.exe        1.4 Day(s)
s000as-DBPILOT       STARLIMS_DATA        INACTIVE   APPS\s000as-DBPILOT        55879 Starlims.Server.WorkerProcess.exe        1.4 Day(s)

... OTHER ROWS HERE

s000as-DBPILOT       STARLIMS_DATA        INACTIVE   APPS\s000as-DBPILOT        49233 Starlims.Server.WorkerProcess.exe        23.9 Hour(s)
s000as-DBPILOT       STARLIMS_DATA        INACTIVE   APPS\s000as-DBPILOT        49224 Starlims.Server.WorkerProcess.exe        23.9 Hour(s)

... OTHER ROWS HERE

s000as-DBPILOT       STARLIMS_DATA        INACTIVE   APPS\s000as-DBPILOT        57998 Starlims.Server.WorkerProcess.exe        3.3 Hour(s)
s000as-DBPILOT       STARLIMS_DICT_PROD   INACTIVE   APPS\s000as-DBPILOT        60437 w3wp.exe                                 1.4 Hour(s)
s000as-DBPILOT       STARLIMS_DATA        INACTIVE   APPS\s000as-DBPILOT        60454 w3wp.exe                                 1.4 Hour(s)


764 rows selected.

 
So, by reviewing the INACTIVE_TIME column from the output above I can see lots of sessions that are inactive more than several hours or even days.

There are 2 possible reasons for that:

1) IDLE SESSIONS – A user or an application logged in, did some work and didn’t logout. The session in this case is still connected (consuming resources) but do nothing for a long period of time.
2) DEAD SESSIONS – It could be an abnormaly terminated session(s). For instance, A user reboots their machine without logging off or disconnecting from the database.

For the first case you might want to enable resource limits. It will be applied for new connections only.
For the second case you must configure SQLNET.EXPIRE_TIME parameter in your sqlnet.ora file.

 
REFERENCES

 
ENABLING DEAD CONNECTION DETECTION(DCD) IN AN ORACLE DATABASE
HOW TO TERMINATE ALL IDLE SESSIONS/PROCESSES IN AN ORACLE DATABASE AUTOMATICALLY