監控並處理一直不釋放資源的會話

最近資料庫出問題：隔段時間（沒有什麼規律）有幾個程式就佔用了絕大部分的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;

五、新增定時任務

#check top session
*/5 * * * * /monitor/top_session/top_session.sh >> /monitor/top_session/log.log