This discussion is archived
3 Replies Latest reply: Jan 28, 2013 10:20 AM by Osama_Mustafa RSS

Oracle idle sessions

amitavachatterjee1975 Newbie
Currently Being Moderated
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.

Thanks
Amitava.
  • 1. Re: Oracle idle sessions
    P.Forstmann Guru
    Currently Being Moderated
    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:
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6010.htm#SQLRF01310.
  • 2. Re: Oracle idle sessions
    Justin Cave Oracle ACE
    Currently Being Moderated
    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.

    Justin
  • 3. Re: Oracle idle sessions
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Check the below link
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:891186000346166866
    http://www.runningoracle.com/product_info.php?products_id=318

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points