Oracle清除閒置未關閉的連結
手上的一個的系統，因為使用Oracle Database Express Edition，加上Client端使用ASP.NET 1.1的System.Data.OracleClient，一直遭遇一個嚴重的偶發問題：使用者連結數超過系統的上限。
主要是發生在1.1版的OracleClient似乎會造成雖明確地關閉資料庫連結，但pooling的機制會讓Oracle誤判連結並未結束（閒置中），然後在部份功能使用AJAX後，更使得這問題顯得相當嚴重，甚至超過Oralc XE的上限（Enterprise版的上限比較高）。
不過，因為該系統屬於迷你系統，不需要也不可能使用Enterprise版（除非依附到別人的DB），而這段的程式也因為卡在有將系統升級到ASP.NET 2.0的打算，且主管不願意放資源（人力/工時）在這系統，所以改用暴力法去「減少問題發生的機率」。
主要概念是將閒置超過一定時間的連結session，直接在Oracle DB裡面把它砍掉（不過因為Oracle運作機制的緣故，實際上會是標示那些連結可以在下一次觸發清理機制的時候被刪除，但該機制並不沒有辦法強制立即執行）。
我在有限時間的作法是：作一個暫存的Table，建立一支Procedure將應刪除的連結兜成執行刪除的SQL Commands紀錄到該Table再讀出來執行，最後建立Job排程每隔依小段時間執行乙次。
這樣的作法有許多可以改進的地方，但礙於現實問題，也只能先這樣。Procedure如后：
主要是發生在1.1版的OracleClient似乎會造成雖明確地關閉資料庫連結，但pooling的機制會讓Oracle誤判連結並未結束（閒置中），然後在部份功能使用AJAX後，更使得這問題顯得相當嚴重，甚至超過Oralc XE的上限（Enterprise版的上限比較高）。
不過，因為該系統屬於迷你系統，不需要也不可能使用Enterprise版（除非依附到別人的DB），而這段的程式也因為卡在有將系統升級到ASP.NET 2.0的打算，且主管不願意放資源（人力/工時）在這系統，所以改用暴力法去「減少問題發生的機率」。
主要概念是將閒置超過一定時間的連結session，直接在Oracle DB裡面把它砍掉（不過因為Oracle運作機制的緣故，實際上會是標示那些連結可以在下一次觸發清理機制的時候被刪除，但該機制並不沒有辦法強制立即執行）。
我在有限時間的作法是：作一個暫存的Table，建立一支Procedure將應刪除的連結兜成執行刪除的SQL Commands紀錄到該Table再讀出來執行，最後建立Job排程每隔依小段時間執行乙次。
這樣的作法有許多可以改進的地方，但礙於現實問題，也只能先這樣。Procedure如后：
CREATE OR REPLACE PROCEDURE SYS.SP_CLEAR_SESSIONS
AS
CURSOR c1 IS
SELECT KILL_STATEMENT
FROM A_IDLE_SESSIONS;
BEGIN
DELETE FROM A_IDLE_SESSIONS;
INSERT INTO A_IDLE_SESSIONS (KILL_STATEMENT)
(
SELECT 'ALTER SYSTEM KILL SESSION '' ' substr(b.sid,1,5) ', '
substr(b.serial#,1,5) '''' AS KILL_STATEMENT
FROM v$process a, v$session b
WHERE b.paddr = a.addr AND b.type='USER'
AND b.program IN ('aspnet_wp.exe', 'SQL Developer')
AND b.status IN ('INACTIVE')
AND last_call_et > 1200
);
COMMIT;
FOR r1 IN c1
LOOP
EXECUTE IMMEDIATE r1.KILL_STATEMENT;
END LOOP;
END SP_CLEAR_SESSIONS;
留言