监控并处理一直不释放资源的会话

最新推荐文章于 2021-09-17 00:31:30 发布

cuilin2183

最新推荐文章于 2021-09-17 00:31:30 发布

阅读量159

点赞数

文章标签：数据库 awk shell

最近数据库出问题：隔段时间（没有什么规律）有几个进程就占用了绝大部分的CPU，且一直运行的相同的SQL（不同的进程运行的sql可能会不一样）。如果不强行中止的话，这些进程会运行这个sql几天几夜也不会停止。
我把运行的sql单独拿出来运行，运行速度并不慢，最多不会超过5s。
数据库后台也没有什么相关的报错信息。

多方查找资料无果，只能考虑临时解决方法。

初步考虑解决方法有两种：

1、通过profile限制

可用profile限制的是用某个用户登录的所有session的连接时间、可使用最大CPU等，但不太适合实际情况。因为出问题的数据库经常需要导入数据、同步数据等，这些操作可能需要几个小时的处理时间。

2、自己写脚本解决

主要思路：
1、通过top找出可疑进程，并把进程号写到文件中
2、通过oracle外部表结束读取上一步的文件内容，找出相关会话信息作相关处理
3、如果某些会话满足设定的一系列条件，则把kill process的脚本通过存储过程写到一个sh文件中
4、在OS中执行上一步得到的sh文件

详细步骤如下：

一、创建找出top进程的shell
[root@test top_session]# more top_session.sh
#-----------------------------------------bof------------------------------------
#!/bin/sh

TOP_SESSION_FILE=`dirname $0`/top_session.txt
TOP_SESSION_PID=`dirname $0`/top_session.pid
KILL_TOP_SPID=`dirname $0`/kill_spid.sh
PCT_CPU=50
OCCR_NUM=5

#set env
DIRNAME=`dirname $0`
source ${DIRNAME}/../pub/init.sh

#+----------------------------------------------+
#+ d 2表示每个两秒刷新一次top +
#+ n 10表示刷新10次 +
#+ b 表示批量模式，其显示格式比较便于放到文件中 +
#+ i 表示忽略不占用资源的进程 +
#+----------------------------------------------+
top d 2 n 10 b i > $TOP_SESSION_FILE

#+----------------------------------+
#+ 过滤条件： +
#+ 1、是oracle用户的进程 +
#+ 2、CPU使用率大于等于PCT_CPU +
#+ 3、排序后合并相同的进程号 +
#+ 4、进程号出现5次以上 +
#+----------------------------------+
cat $TOP_SESSION_FILE |grep -i oracle |awk {'if ($9 >= "'$PCT_CPU'") print $1'}|sort|uniq -c|awk {'if ($1 >= 5) print $2'} > $TOP_SESSION_PID

#clear KILL_TOP_SPID
> $KILL_TOP_SPID

chmod 777 $KILL_TOP_SPID

# execute store procedure
if [ -z "$TOP_SESSION_PID" ]
then
exit 10
fi

sqlplus -S ${ORACLE_USER}/${ORACLE_PASSWORD} <set serverout on
exec souchang2.p_top_session;
exit
EOF

if [ -s "$KILL_TOP_SPID" ]
then
echo "kill process"
# kill process
sh $KILL_TOP_SPID
fi

---------------------------------eof-----------------------------------

二、创建外部表
1、对系统目录授权，使oracle用户可以访问目录
chmod -R 777 top_session/

2、创建directory
以souchang2用户执行：
create or replace directory TOP_SESSION
as '/monitor/top_session';

用户需要有相应的权限，如果没有权限，则需要授权：
grant create any directory to xxx;

3、创建外部表
以souchang2用户执行：
--建表
SQL> create table top_spid (spid number)
2 organization external (
3 type oracle_loader
4 default directory top_session
5 access parameters (
6 records delimited by newline
7 nobadfile
8 nodiscardfile
9 nologfile
10 )
11 location('top_session.pid')
12 ) reject limit unlimited
13 ;

Table created

--测试
SQL> select * from top_session;

SPID
----------
18449
18451
18449
18451

三、创建会话信息表
以souchang2用户执行：
create table TOP_SESSION
(
SID NUMBER,
SERIAL# NUMBER,
SPID NUMBER,
PADDR VARCHAR2(50),
SQL_ADDRESS VARCHAR2(100),
SQL_HASH_VALUE VARCHAR2(100),
CNT NUMBER default 0,
OCCR_DATE DATE DEFAULT SYSDATE
);

--为spid字段创建唯一性约束
SQL> create unique index idx_top_session_spid on top_session(spid);

Index created

四、创建存储过程

以souchang2用户添加存储过程：
create or replace procedure p_top_session is
/*
作用：鉴定并清除长时间占用CPU的无效oracle进程
流程：
1、根据ip排除，只处理从web、web2过来的链接，
2、只处理oracle的userame='SOUCHANG2'的会话
3、只处理COMMAND=3(查询)的会话
4、STATUS='active'
5、根据sql_address/sql_hash_value确定唯一的sql，加上进程号/进程地址、SID/序号
6、每5分钟运行一次，如果超过15分钟还运行就kill进程

*/
l_cnt_session int;
l_cnt_session1 int;
l_cnt_spid int;
l_times int;
l_username varchar2(32) := 'SOUCHANG2';
l_command int := 3;
l_status varchar2(20) := 'ACTIVE';
l_ip1 varchar2(15) := '192.168.0.101';
l_ip2 varchar2(15) := '192.168.0.103';

---------------
l_procedure_name varchar2(100):='p_top_session';
l_sqlcode number;
l_sqlerrm varchar2(4000);

-------------------------------------
--------写文件存储过程--------------
procedure p_put_text(spid number) is
handle utl_file.file_type;
buffer varchar2(100);
file_name varchar2(100):='kill_spid.sh';
dir varchar2(100):='TOP_SESSION';
begin
handle:=utl_file.fopen(dir,file_name,'a',200);
--a:向文件追加内容
--w:向文件写内容,会覆盖原有内容
--r:打开为只读
buffer:='kill -9 '||to_char(spid);
utl_file.put_line(handle,buffer,true);
utl_file.fclose(handle);
end ;
begin
execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
dbms_output.put_line('<<<<<<< begin processing >>>>>>>');
dbms_output.put_line(sysdate);
/* 如果top_session中某个spid在新采集的spid中不存在，则表示这个spid不会是有问题进程号，
从top_session中删除这个spid对应的信息
*/
delete from top_session where spid not in (select spid from top_spid);
--遍历新采集到的所有进程
for c_spid in (select spid from top_spid) loop
select count(1) into l_cnt_spid
from top_session
where spid = c_spid.spid;

if l_cnt_spid = 0 then--如果spid在top_session中不存在
--判断可疑进程是否满足条件
select count(1) into l_cnt_session1
from v$session
where username = l_username
and command = l_command
and status = l_status
and CLIENT_INFO in (l_ip1, l_ip2)
and paddr =(select addr from v$process where spid = c_spid.spid);
if l_cnt_session1>0 then
--满足条件，则插入记录，否则不管
dbms_output.put_line('questionable process id: '||c_spid.spid);
insert into top_session select sid, SERIAL#, c_spid.spid,paddr,SQL_ADDRESS,sql_hash_value,1,sysdate
from v$session
where paddr =(select addr from v$process where spid = c_spid.spid);
end if;
else
--如果spid在top_session中存在，则分两种情况
select count(1) into l_cnt_session
from v$session
where username = l_username
and command = l_command
and status = l_status
and CLIENT_INFO in (l_ip1, l_ip2)
and paddr =(select addr from v$process where spid = c_spid.spid);

--如果当前的进程符合条件，表明进程可能有问题，判断是否达到kill的标准
if l_cnt_session > 0 then
select cnt into l_times from top_session where spid = c_spid.spid;
--如果次数标准，删除会话
if l_times > 1 then
--打印信息
dbms_output.put_line('kill '||c_spid.spid||'!');
dbms_output.put_line('the running SQL is :');
for c_sql in (select sql_text from v$sqltext where (address,hash_value) in (select sql_address,sql_hash_value from top_session where spid=c_spid.spid) order by piece) loop
dbms_output.put_line(c_sql.sql_text);
end loop;
--调用写文件过程，把命令写到文件中
p_put_text(c_spid.spid);
--删除已经处理的信息
delete from top_session where spid=c_spid.spid;
else
--如果次数没有达到要求，则把次数加1
update top_session set cnt = cnt + 1 where spid = c_spid.spid;
end if;
else
--如果当前进程不符合条件，则把该进程对应的信息从top_session中移除
delete from top_session where spid = c_spid.spid;
end if; --l_cnt_session
end if; --l_cnt_spid
end loop;
commit;
dbms_output.put_line('<<<<<<<< end processing >>>>>>>');
dbms_output.put_line(rpad('*',60,'*'));
exception
when others then
rollback;
l_sqlcode:=sqlcode;
l_sqlerrm:=sqlerrm;
insert into PROCEDURE_ERR_RECORD values(seq_PROCEDURE_ERR_RECORD.Nextval,l_procedure_name,l_sqlcode,l_sqlerrm,sysdate);
commit;
dbms_output.put_line(sqlcode||':'||sqlerrm);
end p_top_session;