分类: Oracle
2011-12-06 17:30:09
用户反映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:
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 allresources 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