3 Replies Latest reply: Feb 15, 2013 4:10 AM by Osama_Mustafa RSS

    hundreds of inactive sessions

    jmft2012
      there are hundred sessions with v$session's status INACTIVE for hours. These sessions are application proxy user's.
      Question, could these inactive sessions cause the performance issue of the database?
        • 1. Re: hundreds of inactive sessions
          sb92075
          IDM99 wrote:
          there are hundred sessions with v$session's status INACTIVE for hours. These sessions are application proxy user's.
          Question, could these inactive sessions cause the performance issue of the database?
          NO!
          INACTIVE sessions do not consume CPU or disk I/O.
          While INACTIVE session do consume RAM & if RAM was scarce; then they would be paged out.


          Handle:     IDM99
          Status Level:     Explorer (65)
          Registered:     Nov 13, 2009
          Total Posts:     270
          Total Questions:     58 (45 unresolved)

          Why so many unanswered questions?
          • 2. Re: hundreds of inactive sessions
            Billy~Verreynne
            IDM99 wrote:
            there are hundred sessions with v$session's status INACTIVE for hours. These sessions are application proxy user's.
            Question, could these inactive sessions cause the performance issue of the database?
            Sessions, even idle, consume resources. And could own database resources (e.g. locks) that can force other working sessions to queue up and wait.

            There is little default protection for a database against a badly behaved application server ito open sessions. Which is why I usually insist that a shared server pool be configured, and that this be used by the application server. So when it does something stupid, it can only create maxpool shared server sessions - and not 100's, or even 1000's, of session processes. And when it does mess up, is it a simple ps|grep|awk combination on the o/s command line for swinging the lead pipe and killing the session mess made by the application server.

            I've lost count how many times this approach saved production processing for the databases I look after - as the app layer would have caused the max processes per db instance to be reached. This would caused no new sessions/jobs/processes to be created and no production processing to happen. Instead the app layer runs into the max shared server process count. And production stuff using dedicated processes are safe.
            • 3. Re: hundreds of inactive sessions
              Osama_Mustafa
              Also Check
              http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:914029001168