This content has been marked as final. Show 7 replies
I hope this link help you
And this link
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
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
- KILLED - Session marked to be killed
- CACHED - Session temporarily cached for use by Oracle*XA
- SNIPED - Session inactive, waiting on the client
@OP, where did you come across that term, and do you have any idea what it is supposed to mean?
Stale sessions are those who are inactive since long time.Really? I have never heard of that.
user12657832 wrote: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).
This is where I come across the word 'stale session'
Can't deploy mappings, jobs just hang in the control center
Yeah - a more correct term would be "+orphaned sessions+" as the session is there to serve a client that no longer exists.
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.