3 Replies Latest reply: Jan 28, 2013 12:20 PM by Osama_Mustafa RSS

    Oracle idle sessions

      Hi Gurus,

      I need to learn more about Oracle idle sessions, why/how they are created and why some of the idle sessions does not terminate easily.
      Is there any ways to determine which SQLs are responsible for idle sessions. Also SQL way to identify and destroy idle sessions.

      Does this make sense.

        • 1. Re: Oracle idle sessions
          Sessions are idle because they don't execute SQL or PL/SQL statements: it is the application code that you should investigate especially in 3 tier application where database sessions are managed by some application server/web server connection pool.

          V$SESSION.LAST_CALL_ET should help identify idle sessions:
          LAST_CALL_ET      NUMBER      If the session STATUS is currently ACTIVE, then the value represents the elapsed time (in seconds) since the session has become active.

          If the session STATUS is currently INACTIVE, then the value represents the elapsed time (in seconds) since the session has become inactive.

          You can create a profile with the right idle_time setting to ask database instance to automatically end idle session:
          • 2. Re: Oracle idle sessions
            First, why do you believe that idle sessions are a problem?

            A session is IDLE if it is not, at the instant you query the data dictionary, executing a SQL statement. Most sessions will be idle most of the time because most of the time your applications are waiting on humans to do something or spending time processing data in the application. Just because a session is idle now is no reason to believe that it won't be ACTIVE shortly.

            A modern three-tier application will generally create a pool of connections to the database on each middle tier server. Generally, those pools will be sized for at least reasonably busy conditions. If you happen to look at the database when the application isn't particularly busy, you're likely to see a bunch of idle sessions. That's not a problem. While you could force the application server admins to decrease the size of their connection pools, that may negatively affect the performance of the applications and introduce extra load on the database server for no real benefit.

            • 3. Re: Oracle idle sessions
              Check the below link