【DBA笔记2】Oracle之定期清理inactive会话

目录

背景

例子

1、超60秒会话

2、建存储过程

2.1、直接kill

2.2、完成当前事务后kill

---

背景

ORACLE数据库会话有ACTIVE、INACTIVE、KILLED、 CACHED、SNIPED五种状态.INACTIVE表示会话处于空闲状态.如PL/SQL Developer连接到数据库，执行一条SQL语句后，如果不继续执行SQL语句，那此会话就处于INACTIVE状态.一般少量INACTVIE会话对数据库没影响，但如果由于程序设计等原因导致数据库出现大量会话长时间处于INACTIVE状态，那将消耗大量系统资源，造成会话数超过系统session最大值，出现ORA-00018:maximum number of sessions exceeded错误.

此时就需要清理那些长时间处于INACTIVE状态的会话.通过Job来解决此问题.那如何定义长时间处于INACTIVE状态的会话呢？

a、会话须长时间处于INACTIVE状态.如处于INACTIVE超两小时的会话才考虑Kill.视具体业务决定，有可能超过半小时就可以杀掉会话进程.

   通过V$SESSION的LAST_CALL_ET字段来判断，查询条件S.LAST_CALL_ET >= 7200.

b、连接会话的程序.如某特定应用程序产生的INACTIVE会话才要清理.如Toad工具、PL/SQL Developer、W3WP.EXE工具.

例子

   关于PROGRAM需根据当前项目的具体情况设置，下面使用TOAD.EXE、W3WP.EXE以及会话状态INCATIVE超过1分钟举例说明.

1、超60秒会话

找出状态为INACTIVE超过60秒的session信息.

单实例.

select sid, serial#, module, status

  from v$session s

 where s.username is not null

   and upper(s.program) in ('TOAD.EXE', 'W3WP.EXE', 'PLSQLDEV.EXE')

   and s.last_call_et >= 60

   and s.status = 'INACTIVE'

 order by sid desc;

集群.

select sid, serial#, inst_id, module, status

  from gv$session s

 where s.username is not null

   and upper(s.program) in ('TOAD.EXE', 'W3WP.EXE', 'PLSQLDEV.EXE')

   and s.last_call_et >= 60

   and s.status = 'INACTIVE'

 order by inst_id desc;

2、建存储过程

2.1、直接kill

接下来创建存储过程sys.db_kill_idle_clients，方便调用该功能执行kill inactive会话.

CREATE OR REPLACE PROCEDURE SYS.DB_KILL_IDLE_CLIENTS AUTHID DEFINER AS

  job_no       number;

  num_of_kills number := 0;

BEGIN

  FOR REC IN (SELECT SID, SERIAL#, INST_ID, MODULE, STATUS

                FROM gv$session S

               WHERE S.USERNAME IS NOT NULL

                 AND UPPER(S.PROGRAM) IN ('TOAD.EXE', 'W3WP.EXE', 'PLSQLDEV.EXE')

                 AND S.LAST_CALL_ET >= 60

                 AND S.STATUS = 'INACTIVE'

               ORDER BY INST_ID ASC) LOOP

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

    -- kill inactive sessions immediately

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

    DBMS_OUTPUT.PUT('LOCAL SID ' || rec.sid || '(' || rec.module || ')');

    execute immediate 'alter system kill session ''' || rec.sid || ', ' ||

                      rec.serial# || '''immediate';

  

    DBMS_OUTPUT.PUT_LINE('. killed locally ' || job_no);

    num_of_kills := num_of_kills + 1;

  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Number of killed xxxx system sessions: ' ||

                       num_of_kills);

END DB_KILL_IDLE_CLIENTS;

/

2.2、完成当前事务后kill

由于kill session是直接将session kill，可能会出现导致事物回滚的现象，实际可以使用disconnect session完成当前事务后终止session.此方法比alter system kill session安全可靠.

CREATE OR REPLACE PROCEDURE SYS.DB_KILL_IDLE_CLIENTS AUTHID DEFINER AS

  job_no       number;

  num_of_kills number := 0;

BEGIN

  FOR REC IN (SELECT SID, SERIAL#, INST_ID, MODULE, STATUS

                FROM gv$session S

               WHERE S.USERNAME IS NOT NULL

                 AND UPPER(S.PROGRAM) IN ('TOAD.EXE', 'W3WP.EXE', 'PLSQLDEV.EXE')

                 AND S.LAST_CALL_ET >= 60

                 AND S.STATUS <> 'KILLED'

               ORDER BY INST_ID ASC) LOOP

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

    -- kill inactive sessions immediately

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

    DBMS_OUTPUT.PUT('LOCAL SID ' || rec.sid || '(' || rec.module || ')');

    execute immediate 'alter system disconnect session ''' || rec.sid || ', ' ||

                      rec.serial# || '''immediate';

    DBMS_OUTPUT.PUT_LINE('. killed locally ' || job_no);

    num_of_kills := num_of_kills + 1;

  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Number of killed system sessions: ' ||

                       num_of_kills);

END DB_KILL_IDLE_CLIENTS;

/

可以在作业JOB或Schedule定期调用该存储过程，也可以通过后台作业结合shell脚本实现定期清理空闲会话.

如下创建killSession.sh脚本，调用该存储过程SYS.DB_KILL_IDLE_CLIENTS.

#!/bin/bash

source /home/oracle/.bash_profile

logfile=/home/oracle/session/logs/killSession.log

echo " " >> $logfile 2>&1

echo "START ----`date`" >> $logfile 2>&1

$ORACLE_HOME/bin/sqlplus -S sys/oracle_4U@heal as sysdba <<EOF

exec sys.db_kill_idle_clients;

exit;

EOF

echo "END ------`date`" >> $logfile 2>&1

在crontab里面配置后台作业，此处测试每隔1分钟运行一次，清理满足条件的空闲会话.

*/1 * * * * /home/oracle/session/scripts/killSession.sh &>/dev/null