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
Database : 11.2.0.4
Related Posts
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Certificates
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (63)
- Account (2)
- AWR (2)
- Cipher Suites (1)
- Database Errors (5)
- Database Performance (9)
- Datapump (3)
- Enterprise Manager (24)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Temporary Tablespace (2)