问题描述

现场同事反馈应用卡顿的厉害，让其导出awr报告发现负载并不高，就是会话数有些高，库里查询活动会话才2位数，就和现场反馈非数据库问题，也是自己不专业，后面现场同事反馈昨天资深DBA分析的结果是非活动会话占用服务器资源不释放怀疑是爬虫，感觉瞬间被打脸，赶紧接着百度分析，真是基础不扎实。下面是分析过程：

分析过程

分析AWR报告

查看会话数和参数

通过以下查询发现并未超过最大进程数和最大会话数

--查看当前的数据库连接数
select count(*) from v$process;  --返回30
--查看数据库允许的最大连接数
select value from v$parameter where name ='processes';   --返回3000
--查看数据库允许的最大会话数
select value from v$parameter where name ='sessions';  --返回4544

检查数据库中存在的非活动会话

--查看当前并发连接数
select status, count(1) from v$session group by status;

select username, program, count(1) from v$session where status='INACTIVE' group by username, program;

select p.username "OSUSERNAME", p.terminal,p.program,s.username "DBUSERNAME",s.command,s.status,s.server,s.process,s.machine,s.port,s.terminal,s.program,s.sid,s.serial#,p.spid FROM v$session s,v$process p WHERE p.addr=s.paddr and s.status='INACTIVE'order by 1,4;

查询是否有死锁

查询结果为no rows selected，说明数据库中没有死锁。

select * from v$locked_object;

查看内存

内存不释放

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

last_call_et：单位秒

单实例

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','DBEAVER.EXE')
   and s.last_call_et >= 60
   and s.status = 'INACTIVE'
 order by sid desc;
 
 
 select DISTINCT(module) from 
 (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','DBEAVER.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;

产生原因

当会话处于INACTIVE状态过久可能导致资源浪费和ORA-00018错误，该场景中未出现超过最大会话数引发的ORA-00018错误。

解决办法

法一

sqlnet.ora位置在$ORACLE_HOME/dbs目录下

vi sqlnet.ora 
sqlnet.expire_time=60

参数说明：
sqlnet.expire_time：单位分钟，视业务情况设置时长

单点：alter system set resource_limit=true  scope=both;
      alter profile customers_profiles limit idle_time 60;


集群：alter system set resource_limit=true sid='*' scope=both;
      alter profile customers_profiles limit idle_time 60;

法二

--手动杀会话
select A.SID,B.SPID,A.SERIAL#,a.lockwait,A.USERNAME,A.OSUSER,a.logon_time,a.last_call_et/3600 LAST_HOUR,A.STATUS, 
'orakill '||sid||' '||spid HOST_COMMAND,
'alter system kill session '''||A.sid||','||A.SERIAL#||'''' SQL_COMMAND
from v$session A,V$PROCESS B where A.PADDR=B.ADDR AND SID>6

--批量杀会话
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>2700 and username='ICWEB' and machine='orc';
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;
/

法三

创建存储过程

场景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：创建完成当前事务后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;
/

最后通过shell脚本清理空闲会话

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

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

cd /home/oracle/scripts/
cat killSession.sh
#!/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

chmod +x /home/oracle/scripts/killSession.sh

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

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

补充：会话状态

ORACLE数据库会话有ACTIVE、INACTIVE、KILLED、 CACHED、SNIPED五种状态：

• active 处于此状态的会话，表示正在执行，处于活动状态。
• killed 处于此状态的会话，表示出现了错误，正在回滚，当然，也是占用系统资源的。还有一点就是，killed的状态一般会持续较长时间，而且用windows下的工具pl/sql developer来kill掉，是不管用的，要用命令：alter system kill session 'sid,serial#' immediate;
• INACTIVE表示会话处于空闲状态(等待操作可理解为等待需要执行的SQL语句)，如PL/SQL Developer连接到数据库，执行一条SQL语句后，如果不继续执行SQL语句，那此会话就处于INACTIVE状态.一般少量INACTVIE会话对数据库没影响，但如果由于程序设计等原因导致数据库出现大量会话长时间处于INACTIVE状态，那将消耗大量系统资源，造成会话数超过系统session最大值，出现ORA-00018:maximum number of sessions exceeded错误。此时就需要清理那些长时间处于INACTIVE状态的会话，最好的就是在oracle中直接设置超时时间，也可通过Job来解决此问题。

影响长时间处于INACTIVE状态的会话因素：

• Dead Connection或IDLE Connection造成。DEAD 连接由 SQLNET.ORA 文件处理，通过配置参数 SQLNET.EXPIRE_TIME=minutes 打开 SQLNET ORA 文件并设置参数。它是客户端文件，所以在客户端设置它。SQLNET.EXPIRE_TIME=60 (1 hour)；IDLE 连接设置为 USER PROFILES，定义具有 IDLE_TIME 限制的配置文件，以便在达到时间限制后终止 INACTIVE SESSION。

--需要启用资源限制，以便它自动终止会话
alter system set resource_limit=true scope=both;

--检查用户使用哪个配置文件
SELECT USERNAME, USER_ID, PROFILE FROM DBA_USERS;

--检查 IDLE_TIME 的配置文件设置
select profile, limit from DBA_PROFILES where resource_name = 'IDLE_TIME';

--将 IDLE TIME 更改为断开连接后 30 分钟  
alter profile customers_profiles limit idle_time 30;

参数说明：idle_time 参数将在30分钟不活动后断开会话，单位分钟

• 会话须长时间处于INACTIVE状态，如处于INACTIVE超两小时的会话才考虑Kill，可视具体业务决定，有可能超过半小时就可以杀掉会话进程。
• 通过V$SESSION的LAST_CALL_ET字段(单位是秒)来判断，查询条件S.LAST_CALL_ET >= 7200。

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

参考链接：https://blog.csdn.net/m0_55928215/article/details/128550223

https://www.cnblogs.com/lkj371/p/17024709.html

https://blog.csdn.net/tylz04/article/details/9041775