Hi. Have a situation I've never encountered before. we have a continual increase in the # of processes running in our Oracle 19c database that no longer have a correlation to sessions.
For example: (this was captured at 11:30 a.m. so at the height of user activity).
SQL> SELECT RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION, LIMIT_VALUE
FROM V$RESOURCE_LIMIT
WHERE RESOURCE_NAME IN ( 'sessions', 'processes');
2 3
RESOURCE_NAME
--------------------------------------------------------------------------------
CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------------- --------------- ----------------------------------------
processes
1904 3634 4000
sessions
904 922 6024
We have had to bump up the limit of processes from 1k to 2k, then to 4k. As you can see we gradually get to where the # of processes is close to the max, but the # of sessions never goes above 1k.
Before I joined the group, the sysadmin team did some testing and narrowed down where all of these “lingering” processes are coming from by shutting down individual application servers. They are coming from our dot Net (.Net) application servers where a lot of “ancillary” applications run, separate from our primary ERP application.
Once the .Net servers are restarted, the OS processes drop back down to approximately the same # of sessions. After that, they continue to increase. In the query results above you can see that the utilization max of processes since last restart of the DB was 3634. We have restarted the .Net app servers 2-3 times since then as the process count gets up close to that level to prevent the db from not accepting any more connections to the DB. Yes we can continue increasing the limit for processes but that doesn't solve the actual problem.
There was a discussion with the .Net development group about how they close connections to the Oracle database. Not much progress on that front yet.
What I am looking for is how to proactively clean up these ‘ghost’ (for lack of a better term) OS processs that are still registered as procesess in the Oracle database. I've read many many forum discussions but have yet to come across this particular issue.
They don't appear to fall into the category of ‘zombie’ processes or anything like that.
Thanks in advance for any suggestions.
Remo.