I'm not sure that there is a problem.
A session in V$SESSION has a status of "INACTIVE" if it is not executing a SQL statement at that particular instant in time. If a user opens a dedicated session in a client-server application and is using the application intensively, it would not be at all unusual for the session to be "INACTIVE" 99% of the time because the vast majority of the time is spent waiting for the human to read and process the data on the screen. The fact that a session is "INACTIVE" doesn't imply that it wasn't "ACTIVE" a few seconds ago or that it won't be "ACTIVE" again a few seconds from now. The LAST_CALL_ET column in V$SESSION shows you the number of seconds since a session was last in an "ACTIVE" status if the session is currently in an "INACTIVE" status. That may give you some idea whether you are dealing with sessions that have been active for a while or not.
If you have a three-tier application, your middle tier servers will generally maintain a pool of database connections. The middle tier opens a number of connections and keeps them open more or less indefinitely. When users need to interact with the database, they get a connection from the pool, run a query, and return the connection to the pool. At peak workloads, connection pooling can concentrate a large number of application users into a relatively smaller number of database connections which will mean that the sessions spend a larger fraction of their time in an "ACTIVE" status. But during periods of low usage, the middle tier probably maintains a bunch of "INACTIVE" sessions.
In either of these cases, having a number of "INACTIVE" sessions is not a bad thing. And "cleaning up" those inactive sessions will cause errors in the applications that have opened them.
If you have a large number of inactive sessions because the client processes have failed (i.e. the client application crashed and the database is unaware of that fact), you could use the sqlnet.expire_time parameter on the server's sqlnet.ora file to periodically send a probe packet to the client machine. That may negatively impact performance but it will allow the database to more quickly discover that the client process has failed.
If you have a large number of inactive sessions because your middle tier servers have created too many connections to the database in their connection pools, you would need to talk with the middle tier admins to ask them to reduce the number of connections they open in their connection pools. If they have recently added more servers to the middle tier farm, they may need to reduce the number of connections each server maintains in its connection pool.
If your problem is that too much RAM is being allocated to all the dedicated server connections that need to be open (particularly common if you've got a lot of client server applications), you could configure Oracle to support shared server connections since that reduces the amount of memory that each connection requires.
Looks like we found the culprit. Following the recent app server upgrade, we inadvertently included both ojdbc14 and ojdbc6 jars into our deployment, and evidently, the jvm picked up ojdbc14 for its Oracle DB driver. Since we removed ojdbc14 manually, this problem hasn't come up again in the past 24 hours. I assume ojdbc14 is no longer officially supported, so it could cause all sorts of funny problems.
Our next step is to find out how ojdbc14 sneaked into the deployment in the first place.
Best Answer
The Active Session Pool allows a limit to be place on the number of sessions that can be active on your instance. It is a part of the Resource Manager. The number possible is defined according to your current resource plan. The default plan allows an unlimited number of sessions. It is activated if resource management is performed in the instance. To find out if this is so then check the
resource_manager_planparameter:If there is a value then it is activated. You may see something like
DEFAULT_PLANas theVALUE. You can then queryDBA_RSRC_PLAN_DIRECTIVESfor the number of active sessions possible:You'll get a few rows back relating to the number of groups/subplans that are part of this plan. NULL will indicate unlimited. I prefer using Enterprise Manager to query and amend this. To find it in EM go to the Server tab then under the Resource Manager heading is Plans.
More information on the Resource Manager can be found here.