7 Ответы Последний ответ: 25.05.2011 18:49, автор: Billy~Verreynne

    What is a stale session

    843926
      Hi,

      Can anybody explain me about stale sessions..How to identify them and also how to kill them..

      Thanks in advance....
        • 1. Re: What is a stale session
          666352
          Hi,,
          I hope this link help you

          http://www.oracle-base.com/articles/misc/KillingOracleSessions.php

          And this link

          http://www.dba-oracle.com/tips_killing_oracle_sessions.htm
          • 2. Re: What is a stale session
            Azhar Husain
            Stale sessions are those who are inactive since long time.

            A sample query for know the stale sessions
            SELECT 
              sid,
              serial#,
              ROUND(LAST_CALL_ET/60) wait_mins
            FROM 
              v$session a
            WHERE 
              status='INACTIVE' AND
              TYPE='USER' AND
             LAST_CALL_ET>300
            • 3. Re: What is a stale session
              Peter Gjelstrup
              That's nice. You googled stale session and copied something from the first hit.

              Personally, I have no idea about what a "stale session" is, have never heard of it, and no mention of it in Oracle Concepts.

              According to v$session documentation a session can be:

              - ACTIVE - Session currently executing SQL

              - INACTIVE

              - KILLED - Session marked to be killed

              - CACHED - Session temporarily cached for use by Oracle*XA

              - SNIPED - Session inactive, waiting on the client

              http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/dynviews_3016.htm#REFRN30223




              @OP, where did you come across that term, and do you have any idea what it is supposed to mean?

              Regards
              Peter
              • 4. Re: What is a stale session
                William Robertson
                Stale sessions are those who are inactive since long time.
                Really? I have never heard of that.
                • 5. Re: What is a stale session
                  843926
                  Hi Peter,

                  This is where I come across the word 'stale session'

                  Can't deploy mappings, jobs just hang in the control center

                  Thanks
                  • 6. Re: What is a stale session
                    John Spencer
                    user12657832 wrote:
                    Hi Peter,

                    This is where I come across the word 'stale session'

                    Can't deploy mappings, jobs just hang in the control center

                    Thanks
                    It is a little hard to say given the little context in that, but based on the symptoms and implied resolution, it looks to me like what were called "stale" sessions were database sessions that had lost the connection to the front-end client. That is, the database session was still there, but there was no client on the other end of it, one or more of those sessions were holding locks (probably due to uncommitted transactions), but since there was no client to tell them to commit, the transactions could not be ended. The DBA's probably identified those session and killed them (at least hta's my guess as to the meaning of the DBA's fished them out).

                    John
                    • 7. Re: What is a stale session
                      Billy~Verreynne
                      Yeah - a more correct term would be "+orphaned sessions+" as the session is there to serve a client that no longer exists.

                      @user12657832:

                      I would not call the term "+stale+" sessions relevant to Oracle. There are 2 basic session states in Oracle.
                      - active: server process is busy servicing (executing) a client request
                      - idle: the server process is waiting for a client request

                      A session can last a few seconds.This is typical in a thin-client architecture where the clients are stateless.

                      A session can last several days. For example, a developer using TOAD and simply keeping the client open and running on his PC for several days, without closing it down and reconnecting.

                      There is however an issue at the wire protocol level. When the client crashes, it does not send a FIN packet down the wrire to tear down its network connection with the server. So the server session is unaware that the client crashed. The IP stack on its side does not inform the server session that the network socket handle it owns is no longer valid. It will only discover that when it attempts to use that socket handle to communicate with the missing client.

                      It has no reason to do that however, as it is waiting for the next request from the client.

                      So in such a case, one gets orphaned sessions. An idle session that waits on a non-existing client.

                      There's no way to accurately identify such sessions in Oracle. You can determine the life span of a session and if it is older than 2 days for example, consider it as an orphan and kill it. But its client may still exist. Its network connection between client and server may still be valid.

                      There are ways to deal with such server sessions (and this is a problem with all tcp based servers). In Oracle it is called DCD or Dead Connection Detection. This forces the server session to send a ping over its socket handle to the client at regular intervals. And the client to respond with a pong. Should this fail, then the network connection is no longer valid and the server session can terminate.

                      However, due to the very robust nature of the tcp protocol (designed by the US military to still provide communication over large pieces of broken communication infrastructure in a post nuclear scenario), it can in some cases take many minutes for the network layer to discover that the client is not responding and no longer reachable.