用户反映webmethods报错,maximum number of processes (150) exceeded。经查看,server process在260左右,oracle session數為153,發現有大量session狀態為sniped。正常情況下,该DB process在150左右,session在60以內。
原来是因为ASUS用户profile中设置了IDLE_TIME为20分钟,当session idletime超过此值时,oracle会将其终止,状态会变成sniped。在snipe期間,session hold的鎖和資源將得到釋放,但並不會從v$session中清除,而且OS PROCESS和OS資源也不會釋放的。只有當用戶收到Oracle發送會話已經終止消息後確認了,oracle才會將其從v$session中清除。手动将这些session和OS上的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/