Too Many Inactive Session in Oracle 19C

Question

Our application usage c3p0-0.9.5.5.jar and ojdbc8-12.2.0.1.jar to connect with Oracle 19C.

Here are the flags being passed

com.mchange.v2.c3p0.PoolBackedDataSource@a07b9a4b [ connectionPoolDataSource ->com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@74b02295 [ acquireIncrement -> 1, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, debugUnreturnedConnectionStackTraces -> false, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, identityToken -> 31qzq3ay1xf5c0jx94505|6f4d2294, idleConnectionTestPeriod -> 5, initialPoolSize -> 0, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 45, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 0, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@d898aa34 [ description -> null, driverClass -> null, factoryClassLocation -> null, forceUseNamedDriverClass -> false, identityToken -> 31qzq3ay1xf5c0jx94505|6d9428f3, jdbcUrl -> jdbc:oracle:thin:@x.x.x.x:1521:orcl, properties -> {user=******, password=******} ], preferredTestQuery -> null, privilegeSpawnedThreads -> false, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null, extensions -> {}, factoryClassLocation -> null, identityToken -> 31qzq3ay1xf5c0jx94505|900649e, numHelperThreads -> 3 ]]

Executed the following query:

select status, program, username, last_call_et from v$session WHERE STATUS = 'INACTIVE';

USERNAME	STATUS	PROGRAM	LAST_CALL_ET
EMPDB	INACTIVE	JDBC Thin Client	1727905
EMPDB	INACTIVE	JDBC Thin Client	1727904

I see that there are more than 250 inactive session created in Oracle 19c.

Does inactive session causes CPU Spike where Oracle DB Server is hosted?

How to find the root cause of why the inactive session is getting created?

Is there any way we can mitigate this from Application Server by setting up some properties?

I tried to investigate the root cuase but no luck .

I'm looking for the solution to find the exact root casue of these inactives session (from Oracle DB Server end and from Application End).

Answer 1

An inactive status only means that a session isn't doing anything at the exact moment you checked v$session. It is idle, awaiting its next SQL command. Most sessions are inactive at any given time, including background sessions generated by Oracle to perform internal database operations. This is in itself is not a resource issue or cause for concern: this is normal, expected behavior for most sessions.

A total of several hundred sessions to support background processes and user operations is not unusual, depending on your exact setup. For most configurations, each session maps to a dedicated process with reserved memory on the DB server, so if there's any cause for concern over resources it would be for memory usage.

If your application is generating more user sessions (for empdb?) than prescribed in the session pool parameters, you may have a session "leak" of some kind, which you would need to troubleshoot the root cause from the application side. Monitor the number of application sessions over time (not their status), and it it continues to grow unchecked this could be your issue. If the number remains constant over time then you're probably ok, assuming the application is working as expected.