錯誤現象
java.sql.SQLException: ORA-01000: maximum open cursors exceeded
表示單一進程開啟的游標數超過 OPEN_CURSORS 參數限制 (預設 300)。
根因深度剖析
| 程式碼模式 | 問題本質 | 為何導致游標洩漏 |
| 循環中 createStatement()/prepareStatement() | 每次迴圈建立新 Statement | 每個 Statement 佔用一個伺服器端游標,未關閉即累積 |
| 執行查詢後未關閉 ResultSet/Statement | 資源未釋放 | 游標在伺服器端保持開啟,直到客戶端關閉或超時 |
| 連線池未正確配置 | 物理連線重用邏輯缺陷 | 邏輯關閉未觸發實際資源釋放 |
| 批次處理未分批提交 | 長事務累積大量游標 | 單一事務內開啟過多游標 |
錯誤程式碼 vs 正確程式碼
❌ 典型錯誤模式
for (int i=0; i<10000; i++) {
Statement stmt = conn.createStatement(); // 每次新建
ResultSet rs = stmt.executeQuery("SELECT * FROM t WHERE id=" + i);
// 處理 rs...
// 缺少 rs.close(); stmt.close();
}
✅ 正確模式:循環外建立,循環內重用
// 1. 準備階段:循環外建立 PreparedStatement
String sql = "SELECT * FROM t WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
for (int i=0; i<10000; i++) {
pstmt.setInt(1, i);
try (ResultSet rs = pstmt.executeQuery()) {
// 處理結果集
} // rs 自動關閉
}
} // pstmt 自動關閉
連線池配置關鍵參數
| 參數 | 建議值 | 說明 |
| maxOpenPreparedStatements | 50-200 | 單連線快取 PreparedStatement 數,-1 關閉快取 |
| poolPreparedStatements | true | 啟用 Statement 快取 |
| validationQuery | SELECT 1 FROM dual | 驗證連線有效性 |
| removeAbandoned | true | 自動回收棄用連線 |
| removeAbandonedTimeout | 300 | 秒,超時視為棄用 |
為何單純調大 OPEN_CURSORS 是治標不治本
- 掩蓋程式碼缺陷,延遲暴露嚴重資源洩漏;
- 伺服器端 PGA 記憶體持續增長,最終導致 OOM 或效能雪崩;
- 掩蓋邏輯錯誤 (如無限循環、死鎖) 的偵測時機。
監控與診斷 SQL
-- 當前會話開啟游標數
SELECT a.value, s.username, s.sid, s.serial#
FROM v$sesstat a, v$statname b, v$session s
WHERE a.statistic# = b.statistic#
AND b.name = 'opened cursors current'
AND a.sid = s.sid
ORDER BY a.value DESC;
-- 找出高游標 SQL
SELECT sql_text, COUNT(*) cnt
FROM v$open_cursor
GROUP BY sql_text
ORDER BY cnt DESC;
最佳實踐總結
- PreparedStatement 提升至循環外,配合繫結變數重用;
- try-with-resources (Java 7+) 確保資源確定性釋放;
- 啟用連線池 Statement Cache,減輕伺服器端解析壓力;
- 批次操作使用 addBatch/executeBatch,單次提交;
- 定期審計代碼,靜態分析工具 (SonarQube, SpotBugs) 掃描資源洩漏模式;
- OPEN_CURSORS 設定 500-1000 足矣,重點在代碼質量而非參數調大。