session状态变为sniped

　　用户反映webmethods报错，maximum number of processes (150) exceeded。经查看，server process在260左右，oracle session數為153,發現有大量session狀態為sniped。正常情況下，该DB process在150左右，session在60以內。

原来是因为ＡＳＵＳ用户profile中设置了IDLE_TIME为２０分钟，当session idletime超过此值时，oracle会将其终止，状态会变成sniped。在snipe期間，session hold的鎖和資源將得到釋放，但並不會從v$session中清除，而且OS PROCESS和OS資源也不會釋放的。只有當用戶收到Oracle發送會話已經終止消息後確認了，oracle才會將其從v$session中清除。手动将这些session和ＯＳ上的process清理，并通知开发人员检查程式中DB CONNECTION開啟後是否有正常關閉，事務是否有commit或rollback，为什么有这些多idle session。

网络相关资料：

 Status   of   the   session:  

 ACTIVE   (currently   executing   SQL),INACTIVE,   KILLED   (marked   to   be   killed),   CACHED   (temporarily   cached   for   use   by   Oracle*XA),   SNIPED   (session   inactive,   waiting   on   the   client)  

here:  
  http://safari.oreilly.com/main.asp?bookname=oracleperl&snode=76  
   
  A   sniped   session   occurs   when   a   user   has   exceeded   his   idle   time.   The   situation   has   been   noted   in   the   database,   and   the   user's   actual   database   session   has   been   suspended.   However,   the   user   is   still   consuming   a   dedicated   server   resource   that   has   not   yet   been   allocated   to   someone   else.   This   situation   can   have   an   adverse   effect   on   overall   database   performance.  

 When IDLE_TIME is set in the users' profiles or the default profile. This will kill the sessions in the database (status in v$session now becomes SNIPED) and they will eventually disconnect. It does not always clean up the Unix session (LOCAL=NO sessions). At this time all oracle resources are released but the shadow processes remains and OS resources are not released. This shadow process is still counted towards the parameters of init.ora.

This process is killed and entry from v$session is released only when user again tries to do something. Another way of forcing disconnect (if your users come in via SQL*Net) is to put the file sqlnet.ora on every client machine and include the parameter "SQLNET.EXPIRE_TIME" in it to force the close of the SQL*Net session.

A status of SNIPED in v$session for a session indicates that the session has been terminated by Oracle, because it exceeded the IDLE_TIME set in the profile assigned to the user. For a session to be terminated in such a manner, the RESOURCE_LIMIT parameter should be set to true. All locks and
resources held by the session get released during this SNIPE.
 
The problem with this encountered by yours truly in the past, is that, this session does not get deleted from v$sesssion. This could build up until you get a Number of sessions exceeded Oracle error, in which case the only solution is to bounce the database. This entry will get deleted from v$session only if Oracle responds back to the user saying his or her session has been terminated, which in most cases, happens the next morning

   

   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10640532/viewspace-555211/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10640532/viewspace-555211/