Oracle中有大量的sniped会话

2021-12-16 20:50 abce 阅读(752) 评论(0) 编辑收藏举报

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会话及其所使用的连接。数据库连接方式为共享连接时，要小心不要把分配器进程或共享服务器进程也一并杀掉了。

1	`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;
/