ITPub博客

首页 > 数据库 > Oracle > oracle遭遇大量SNIPED会话

oracle遭遇大量SNIPED会话

原创 Oracle 作者:myownstars 时间:2013-07-17 17:35:02 0 删除 编辑

如果userprofile设置了non-zeroidle time,则以该用户登录的session在空闲一定时间后会变成SNIPED.

今天生产库上碰到一个案例,版本10205processes=1200

客户说登录不上,以sysdba本地登录,

sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jul 17 01:41:30 2013

 

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

 

Connected.

SQL>

 

典型的processes满了,杀死几个local=no的进程后才成功登陆

SQL> select status,count(*) from v$session group by status order by 2;

 

STATUS     COUNT(*)

-------- ----------

ACTIVE           24

INACTIVE         49

SNIPED         1124

 

SQL> select USERNAME,status,count(*) from v$session group by USERNAME,status order by 3;

 

USERNAME                       STATUS     COUNT(*)

------------------------------ -------- ----------

……

SYNCIT                         SNIPED         1120

 

12 rows selected.

 

SQL> select profile from dba_users where username='SYNCIT';

 

PROFILE

------------------------------

PROFILE_IDLE_TIMEOUT

 

SQL> select RESOURCE_NAME,LIMIT from dba_profiles where profile='PROFILE_IDLE_TIMEOUT';

 

RESOURCE_NAME                    LIMIT

-------------------------------- ----------------------------------------

IDLE_TIME                        1

 

IDLE_TIME设置为1分钟

 

 

What does 'SNIPED' status in v$session mean?

            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

sqlnet.expire_time

            sqlnet.expire_time actually works on a different principle and is used to detect dead connections as opposed to disconnecting(actually 'sniping') a session based on idle_time which the profile accomplishes.

            But again, as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates.

 

 

也可写个shell脚本定时kill sniped session

http://askmeapps.blogspot.co.uk/2008/06/sniped-sessions-shadow-processes.html

 

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

上一篇: mysql 的thread pool
请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3086340