SQL> select * from v$resource_limit;
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------- ------------------- --------------- ------------------ -----------
processes 88 150 150 150
sessions 100 174 248 248
Assuming that all the sessions
are active and doesn't share processes
with each other, is it okay to keep the processes
limit significantly lower than sessions
limit? In all the resources I've read, it's suggested / calculated to keep sessions
as processes * 1.5
. But what if the active processes
reaches its limit and sessions
could not able to assign a process to it?
We faced an exception intermittently while establishing a database connection on a successfully running application.
INFO | jvm 1 | main | 2020/07/30 13:35:13 | java.sql.SQLException: Listener refused the connection with the following error:
INFO | jvm 1 | main | 2020/07/30 13:35:13 | ORA-12519, TNS:no appropriate service handler found
INFO | jvm 1 | main | 2020/07/30 13:35:13 |
INFO | jvm 1 | main | 2020/07/30 13:35:13 | at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)
INFO | jvm 1 | main | 2020/07/30 13:35:13 | at oracle.bc.driver.PhysicalConnection.<init>(PhysicalCoConnection.java:546)
INFO | jvm 1 | main | 2020/07/30 13:35:13 | at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
INFO | jvm 1 | main | 2020/07/30 13:35:13 | at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
INFO | jvm 1 | main | 2020/07/30 13:35:13 | at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
INFO | jvm 1 | main | 2020/07/30 13:35:13 | ...
INFO | jvm 1 | main | 2020/07/30 13:35:13 | Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
INFO | jvm 1 | main | 2020/07/30 13:35:13 | ORA-12519, TNS:no appropriate service handler found
INFO | jvm 1 | main | 2020/07/30 13:35:13 |
INFO | jvm 1 | main | 2020/07/30 13:35:13 | at oracle.net.ns.NSProtocol.connect(NSProtocol.java:395)
INFO | jvm 1 | main | 2020/07/30 13:35:13 | at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)
INFO | jvm 1 | main | 2020/07/30 13:35:13 | at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320)
INFO | jvm 1 | main | 2020/07/30 13:35:13 | ... 54 more
Considering that the MAX_UTILIZATION
of processes
reached its limit of 150
at some point (and sessions
reached upto 174
), I am suspecting that there isn't enough processes
to assign it to a session which led me to think that processes
should always be higher than or equal to sessions
parameter.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Best Answer
The value of
sessions
can not be lower thanprocesses
by definition. Even if you try to set it, the database will just ignore it:SESSIONS
You simply hit the process limit for that instance. Increase the limit accordingly, or investigate what application opened/opens these sessions.
Also the number of sessions for users should be limited by their profile, not the instance level
sessions
parameter.CREATE PROFILE - look for
SESSIONS_PER_USER
.