Oracle定期请理INACTIVE会话

导语:生产系统在大升级之后(11g到19c)出现了一个问题,大量空闲会话不自己释放,会慢慢的到达session限制的值,导致数据库问题。
经过观察,这些空闲的会话都出自一个用户:AOX_USER。与应用系统的人联系,他们也查不出什么原因导致的。所以只能在数据库端处理。也就是定时对空闲的会话进行请理。

要求:

  1. 请理INACTIVE状态,并超过3小时的会话。清理语句参考:oracle killed session
  2. 将请理的会话信息收集到一个表中,作为凭据或者日志。

方法

清理的方法有两种:

  1. 用resource_limit的idle_time限制,在profile中设置。参考:用户管理之profile

    Oracle的idle限制机制优点是设置10分钟,空闲会话绝对不会活过11分钟。缺点是kill的会话信息不会存储。只能在alert日志中找到sid和serial number的信息。
  2. 用手动的alter system kill session语句,然后写存储过程定时执行。
    优点是手动的kill可以将准确的信息同时收集起来。缺点是因为是定时任务不会像idle限制一样那么的即时。

最后,领导觉得用第一种方法更好,只不过收集的会话信息可能会有重复的。

实现第一种方法

  1. 修改idletime参数
    首先查找
SYS@CSP> select username,profile from dba_users where username = 'AOX_USER';

USERNAME	PROFILE
-------------
AOX_USER   AOXUSER_IDLE

SYS@CSP> select * from dba_profiles where PROFILE = 'AOXUSER_IDLE' and RESOURCE_NAME= 'IDLE_TIME';

PROFILE  RESOURCE_NAME  RESOURCE   LIMIT COM INH IMP
---------------------------------------------------------
AOXUSER_IDLE IDLE_TIME  KERNEL     180 NO  NO  NO
--IDLE_TIME的值是180,单位是分钟,换算也就是3个小时

--如果修改语句如下
ALTER PROFILE AOXUSER_IDLE LIMIT IDLE_TIME 180;
  1. 创建新表,用于存放会话信息,这个表的设计是因为我对v$session和v$sqlarea的sql语句
CREATE TABLE session_kill_log (
		SID NUMBER,
		SERIAL# NUMBER,
		USERNAME VARCHAR2(128),
		STATUS VARCHAR2(8),
		OSUSER VARCHAR2(128),
		MACHINE VARCHAR2(64),
		PROGRAM VARCHAR2(48),
		LOGON_TIME DATE,
		LAST_CALL_ET NUMBER,
		SQL_TEXT VARCHAR2(1000),
		CREATE_TIME    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) TABLESPACE USERS;
  1. 添加job
BEGIN
    DBMS_SCHEDULER.create_job(
        job_name        => 'INSERT_SESSION_SNAPSHOT_JOB', 
        job_type        => 'PLSQL_BLOCK',                  
        job_action      => 'BEGIN
			     INSERT INTO session_kill_log (SID, SERIAL#, USERNAME, STATUS, OSUSER, MACHINE, PROGRAM, LOGON_TIME, LAST_CALL_ET, SQL_TEXT)
			     select sid,SERIAL#,a.username,a.STATUS,a.OSUSER,a.MACHINE,a.PROGRAM,a.LOGON_TIME,a.LAST_CALL_ET,b.SQL_TEXT 
			     from V$SESSION a join v$sqlarea b on a.PREV_SQL_ID=b.SQL_ID where a.status = ''INACTIVE''  and a.LAST_CALL_ET>10800;
			     COMMIT;
                            END;', 
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=HOURLY; BYMINUTE=0;',
        enabled         => TRUE,
        comments        => 'Job to insert session snapshot every hour'
    );
END;
/

说明:

  1. job每个小时执行一次.
  2. 任务是收集会话状信息的,条件是会话状态是INACTIVE,并且空闲时间超过3小时(60603=10800)
  3. 表session_kill_log最后一个字段是时间戳,代表插入的时间
  4. 插入的数据是v$session和v$sqlarea的复合查询,所以可以查到sql信息

关于job的调度请参考:调度任务(Job Scheduling)

实现第二种方法

  1. 创建新表,用于存放会话信息,这个表的设计是因为我对v$session和v$sqlarea的sql语句
CREATE TABLE session_kill_log (
		SID NUMBER,
		SERIAL# NUMBER,
		USERNAME VARCHAR2(128),
		STATUS VARCHAR2(8),
		OSUSER VARCHAR2(128),
		MACHINE VARCHAR2(64),
		PROGRAM VARCHAR2(48),
		LOGON_TIME DATE,
		LAST_CALL_ET NUMBER,
		SQL_TEXT VARCHAR2(1000),
		CREATE_TIME    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) TABLESPACE USERS;
  1. 创建存储过程
CREATE OR REPLACE PROCEDURE pro_kill_sess_log AS
    CURSOR cut_sess IS
        select sid,SERIAL#,a.username,a.STATUS,a.OSUSER,a.MACHINE,a.PROGRAM,a.LOGON_TIME,a.LAST_CALL_ET,b.SQL_TEXT
		from V$SESSION a
		join v$sqlarea b
		on a.PREV_SQL_ID=b.SQL_ID
		where a.status = 'INACTIVE' and a.LAST_CALL_ET>10800;   
    v_session_id NUMBER;
    v_serial_number NUMBER;
    v_username VARCHAR2(128);
	v_status VARCHAR2(8);
	v_osuser VARCHAR2(128);
	v_machine VARCHAR2(255);
	v_program VARCHAR2(48);
    v_logon_time DATE;
	v_last_call_et NUMBER;
	v_sql_text VARCHAR2(1000);	
BEGIN
    FOR rec IN cut_sess LOOP
        -- 获取当前会话的信息
        v_session_id := rec.sid;
        v_serial_number := rec.serial#;
        v_username := rec.username;
		v_status := rec.status;
		v_osuser := rec.osuser;
        v_machine := rec.machine;
        v_program := rec.program;
        v_logon_time := rec.logon_time;
		v_last_call_et := rec.last_call_et;
		v_sql_text := rec.sql_text;
        -- 将会话信息插入日志表
        INSERT INTO session_kill_log (SID, SERIAL#, USERNAME, STATUS, OSUSER, MACHINE, PROGRAM, LOGON_TIME, LAST_CALL_ET, SQL_TEXT)
        VALUES (v_session_id, v_serial_number, v_username, v_status, v_osuser, v_machine, v_program, v_logon_time, v_last_call_et, v_sql_text);
        -- 执行 KILL 操作
        EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || v_session_id || ',' || v_serial_number || ''' IMMEDIATE';       
        -- 提交插入操作
        COMMIT;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        -- 异常处理
        ROLLBACK;
        RAISE;
END pro_kill_sess_log;
/

说明:
(1). 用游标遍历我设计的sql查询语句,也就是空闲时间超过10800秒的会话。
(2). 循环遍历这些查到的信息,先将信息收集,再手动kill session

  1. 定时任务
BEGIN
    DBMS_SCHEDULER.create_job (
        job_name        => 'JOB_KILL_SESSION',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN pro_kill_sess_log; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=HOURLY; INTERVAL=1',  -- 每小时执行一次
        enabled         => TRUE
    );
END;
/

关于job的调度请参考:调度任务(Job Scheduling)

补充

另一个博主的文章,写的很好:ORACLE定期清理INACTIVE会话

posted @   老牛的田  阅读(18)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示