Cleaning Oracle In-Active Sessions

oracleoracle-10g

There a are huge number of in-active sessions persisting in our database.
How can we clean these in-active sessions?

Database – Oracle 10g R2 with ASM

  • DB Size – 5TB
  • SGA – 78GB
  • PGA – 10GB
  • Maximum Sessions allowed – 1200
  • Average number of In-Active sessions – 150
  • Maximum In-Active Sessions – 900

Best Answer

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.

Related Question