Oracle ROWNUM 綁定變數分頁在 RAC 環境異常案例分析

現象描述

同一支使用 PreparedStatement 綁定 ROWNUM <= ? 的分頁查詢：

• 環境 1：Windows + Oracle 10g 單機 → 正常，回傳指定筆數。
• 環境 2：RedHat 5.2 64-bit + Oracle 10g RAC → 異常，固定回傳 10 筆，無論綁定值為何。

問題 SQL

SELECT *
FROM (SELECT * FROM entity_hotlink
      WHERE hotlink_sort_id = ?
      AND   hotlink_status  = ?
      ORDER BY hotlink_order ASC, last_modified_date DESC)
WHERE ROWNUM <= ?

Java 程式碼：pstmt.setInt(3, count);

變通方案：改為字串拼接

String sql = "SELECT * FROM (SELECT * FROM entity_hotlink ... ORDER BY ...) WHERE ROWNUM <= " + count;
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, hotlinkSortId);
pstmt.setInt(2, hotlinkStatus);
-- 不再綁定第 3 參數

此寫法在兩環境均正常。

可能原因假設

1. 游標共享與綁定窺探：RAC 環境下 CURSOR_SHARING 或 _OPTIM_PEEK_USER_BINDS 導致首次執行計畫錯誤固定。
2. 驅動程式/JDBC 版本差異：Oracle JDBC Thin 驅動對綁定變數處理在 RAC 下有已知 Bug (如 10.2.0.1~10.2.0.3)。
3. 隱含參數：_FIX_CONTROL 相關修正在 RAC 節點間不一致。

建議排查步驟

1. 確認 JDBC 驅動版本，升級至 10.2.0.5 或 11.2.0.4 以上。
2. 檢查 V$SQL_BIND_CAPTURE 實際綁定值是否正確傳遞。
3. 嘗試 ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS"=FALSE; 關閉窺探測試。
4. 對比單節點與 RAC 的 V$SQL_PLAN 差異。
5. 若無法根因，生產環境採用字串拼接 (注意 SQL 注入) 或改用 ROW_NUMBER() OVER + OFFSET/FETCH (12c+)。

最佳實踐提醒

• 分頁參數盡量使用 字串拼接 (經過嚴格數值驗證) 避免綁定變數窺探問題。
• Oracle 12c+ 直接使用 OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY，綁定變數穩定可靠。
• RAC 環境部署前務必進行實際負載測試，驗證分頁、綁定變數、游標共享行為。