Oracle 死鎖進程快速排查與 Kill 實戰腳本

高併發環境下 Package/Procedure/Function 死鎖處理標準流程

現象與影響

• 熱門 PL/SQL 物件（套件、程序、函數）被長時間鎖定
• 相依物件編譯/執行卡住，報「open cursors exceeded」或直接 Hang 住
• 需 DBA 權限介入釋放

標準 Kill 腳本

-- 1. 查詢鎖定物件對應 Session 並產生 KILL 命令
SELECT DISTINCT 
  'ALTER SYSTEM KILL SESSION ''' || b.sid || ',' || b.serial# || ''';' AS kill_sql,
  b.username,
  b.logon_time
FROM v$locked_object a
JOIN v$session b ON a.session_id = b.sid
ORDER BY b.logon_time;

-- 2. 複製輸出的 ALTER SYSTEM KILL SESSION 陳述式執行
-- 範例：
ALTER SYSTEM KILL SESSION '123,45678';

進階診斷：鎖定鏈完整圖譜

-- 顯示阻塞鏈：誰阻塞誰、鎖定什麼物件、SQL 內容
SELECT 
  l1.sid blocker_sid, s1.username blocker_user, s1.sql_id blocker_sql,
  l2.sid waiter_sid,  s2.username waiter_user,  s2.sql_id waiter_sql,
  o.object_name locked_object, l1.type lock_type, l1.lmode, l2.request
FROM v$lock l1
JOIN v$lock l2 ON l1.id1 = l2.id1 AND l1.id2 = l2.id2
JOIN v$session s1 ON l1.sid = s1.sid
JOIN v$session s2 ON l2.sid = s2.sid
JOIN v$locked_object lo ON l1.sid = lo.session_id
JOIN dba_objects o ON lo.object_id = o.object_id
WHERE l1.block = 1 AND l2.request > 0;

預防措施

• 熱門 PL/SQL 物件採用 DETERMINISTIC、RESULT_CACHE 減少硬解析
• 設定 OPEN_CURSORS 足夠大（建議 1000+）
• 應用層連線池正確關閉游標，避免游標洩漏
• 定期 DBMS_SHARED_POOL.KEEP 核心 Package 防止被 aged out 導致重新載入鎖定

死鎖處理核心：快速識別阻塞 Session → 產生 KILL 命令 → 執行釋放 → 事後分析根因（熱門物件、游標洩漏、統計資訊過期）並預防復發。