Oracle中有大量的sniped会话

SQL> select status ,count(*) from gv$session group by status;
STATUS     COUNT(*)
-------- ----------
KILLED            2
SNIPED         6365
ACTIVE          373
INACTIVE       3648

SQL> select USERNAME,status,count(*) from v$session where USERNAME in ('ABCE','ABC_ABC') group by USERNAME,status order by 3;
USERNAME                       STATUS     COUNT(*)
------------------------------ -------- ----------
ABCE                           KILLED            2
ABC_ABC                        ACTIVE            2
ABC_ABC                        SNIPED           22
ABC_ABC                        INACTIVE         40
ABCE                           ACTIVE           51
ABCE                           INACTIVE       1617
ABCE                           SNIPED         3117

SQL> select username,profile,initial_rsrc_consumer_group from dba_users where account_status='OPEN' and USERNAME in ('ABCE','ABC_ABC');
USERNAME                       PROFILE                        INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------ ------------------------------
ABCE                           DEFAULT                        DEFAULT_CONSUMER_GROUP
ABC_ABC                        ABC_PROF                       DEFAULT_CONSUMER_GROUP

SQL> select RESOURCE_NAME,LIMIT from dba_profiles where profile='ABC_PROF' and RESOURCE_NAME='IDLE_TIME';
RESOURCE_NAME                    LIMIT

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

IDLE_TIME                        600

SQL> select RESOURCE_NAME,LIMIT from dba_profiles where profile='DEFAULT' and RESOURCE_NAME='IDLE_TIME';
RESOURCE_NAME                    LIMIT

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

IDLE_TIME                        50

这里idle_time的单位是分钟。

SNIPED状态的含义：

如果用户的profiles,或者在默认的profile中定义了idle_time，则以该用户登录的session在空闲一定时间后会变成sniped。即一个会话是inactive的，且inactive的时长超过了某个限制，比如profile中指定的idle_time时，这个会话的状态就会从inactive变为sinped。

数据库会kill掉这些会话(在v$session中状态显示为sniped)，这些会话会逐渐被断开连接。但并不总是清理掉unix会话(即LOCAL=NO会话)。oracle资源会被释放，但是产生的shadow进程仍会保留(shadow进程仍然占用参数文件的进程总数的配额)，操作系统资源不会被释放。直到用户再次尝试登录，数据库才会彻底清理掉该会话及操作系统上的连接。也就是说，如果客户端不发出SQL，则不能清理掉的SNIPED的会话及其使用的连接，它们仍然会占用着资源，这可能引起资源不足的报错，比如连接数达到最大的问题。

另一种方法就是强制断开连接(前提是通过sql*net连接进来的)。在sqlnet.ora文件中设置sqlnet.expire_time。设置后会强制关闭sql*net建立的会话。sqlnet.expire_time其实是另一种机制，主要目的用来检测dead的连接，而不是用于断开sniped的连接。不过expire_time是全局层面发挥作用，也就可以用于断开sniped的连接。(profile中的idle_time是针对特定用户的)

可以手工来清理这些SINPED会话及其所使用的连接。数据库连接方式为共享连接时，要小心不要把分配器进程或共享服务器进程也一并杀掉了。

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='SNIPED' ;

操作系统上kill进程：

select to_char(a.logon_time, 'yyyy-mm-dd hh24:mi') logon_time,
       a.sql_id,
       a.event,
       a.username,
       a.osuser,
       a.process,
       a.machine,
       a.program,
       a.module,
       b.sql_text,
       b.LAST_LOAD_TIME,
       to_char(b.last_active_time, 'yyyy-mm-dd hh24:mi:ss') last_active_time,
       c.owner,
       c.object_name,
       a.last_call_et,
       a.sid,
       a.SQL_CHILD_NUMBER,
       c.object_type,
       p.PGA_ALLOC_MEM,
       a.p1,
       a.p2,
       a.p3,
       'kill -9 ' || p.spid killstr,
       'ps -ef|grep ' || p.spid ||
       '|grep LOCAL=NO|awk ''{print $2}''|xargs kill -9' kill_sh
  from v$session a, v$sql b, dba_objects c, v$process p
 where a.status = 'SNIPED' --杀死会话状态，还可以是INACTIVE
   and p.addr = a.paddr
   and a.sql_id = b.sql_id(+)
   and a.wait_class = 'Idle'
   and a.sql_child_number = b.CHILD_NUMBER(+)
   and a.row_wait_obj# = c.object_id(+)
   and a.type = 'USER'
 order by a.sql_id, a.event;

也可以用一下脚本，在OS层面kill进程：

#!/bin/sh
tmpfile=/tmp/.kill_sniped
sqlplus system/manager <<EOF
spool $tmpfile
select p.spid from v\$process p,v\$session s
where s.paddr=p.addr
and s.status='SNIPED';
spool off
EOF

for x in `cat $tmpfile | grep "^[0123456789]"`
do 
	kill -9 $x

done

rm $tmpfile

创建job来kill会话：

CREATE OR REPLACE PROCEDURE "KILL_SESSION"
AS
	v_sid number;
	v_serial number;
	killer varchar2(1000);
	CURSOR cursor_session_info IS
		SELECT sid, serial#
		FROM v$session
		WHERE type != 'BACKGROUND'
			AND status = 'INACTIVE'
			AND last_call_et > 3600
			AND username = 'ABCE'
			AND machine = 'test';
BEGIN
	OPEN cursor_session_info;
	LOOP
		FETCH cursor_session_info INTO v_sid, v_serial;
		EXIT WHEN cursor_session_info%notfound;
		killer := 'alter system disconnect session ''' || v_sid || ',' || v_serial || ''' post_transaction immediate';
		EXECUTE IMMEDIATE killer;
	END LOOP;
	dbms_output.PUT_LINE(cursor_session_info % rowcount || ' users with idle_time>2700s have been killed!');
	CLOSE cursor_session_info;
END;
/

这样做其实还是治标不治本,最好能够解决连接池自动释放idle进程的问题