Oracle Shared Pool 異常 SQL 掃描與綁定變數最佳實踐指南

問題背景：Shared Pool 被劣質 SQL 塞爆

生產環境常見現象：ORA-04031: unable to allocate XXX bytes of shared memory，Shared Pool 耗盡。根因多為應用程式產生大量字面值硬編碼 SQL，每組參數產生一個唯一 Cursor，佔用大量 Shared Pool、引發大量硬解析、CPU 飆高、甚至資料庫掛起。

快速掃描佔用過大的 SQL

-- 掃描 Sharable Mem > 100KB 的 SQL
SELECT sql_text, sharable_mem
FROM v$sql
WHERE sharable_mem > 100000
ORDER BY sharable_mem DESC;

• sharable_mem：該 Cursor 在 Shared Pool 佔用的記憶體 (bytes)
• 經驗法則：絕大多數問題 SQL 都會出現在此清單
• 持續執行此腳本、修復 SQL、再掃描，直到清單乾淨

Shared Pool 結構與記憶體分配原理

• Library Cache：Shared SQL Area (解析樹、執行計劃)、PL/SQL Area (程序/函數/套件)
• Data Dictionary Cache：字典資訊快取
• 複雜 SQL (多層嵌套、巨大視圖展開) → 巨大 Shared SQL Area
• 字面值 SQL 產生大量 Parent/Child Cursor → 碎片化 Shared Pool

-- 檢視 SGA 記憶體分配細目
SELECT * FROM V$SGASTAT;

經典反模式：字面值拼接 SQL

PHP 範例

// ❌ 錯誤：每次拼接產生新 SQL 文本
$sql = "SELECT * FROM table_name WHERE id = ";
$id = 1;
$stmt = ociparse($conn, $sql . $id);
ociexecute($stmt);

$id = 2;
$stmt = ociparse($conn, $sql . $id);  // 又是新 SQL！
ociexecute($stmt);

Java 範例

// ❌ 錯誤：Statement + 字串拼接
String sql = "SELECT * FROM table_name WHERE id = ";
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(sql + "1");  // SQL 1
// ...
rset = stmt.executeQuery(sql + "2");  // SQL 2 (完全不同的 Cursor)

後果分析

• Oracle 視為完全不同的 SQL：SELECT ... WHERE id = 1 vs SELECT ... WHERE id = 2
• 每個都要：硬解析 → 配置 Shared SQL Area → 產生執行計劃
• 10 萬個不同 ID → 10 萬個 Cursor → Shared Pool 耗盡 → ORA-04031
• 唯一解法：重啟資料庫 (清空 Shared Pool)

正確模式：綁定變數

PHP OCI8 正確寫法

// ✅ 正確：Prepare 一次，Bind 變數，多次 Execute
$stmt = ociparse($conn, "SELECT * FROM table_name WHERE id = :id");
ocibindbyname($stmt, ":id", $id, 12);  // 12 = 最大長度

$id = 1;
ociexecute($stmt);

$id = 2;
ociexecute($stmt);  // 同一 Cursor，零硬解析

Java JDBC 正確寫法

// ✅ 正確：PreparedStatement + 綁定變數
PreparedStatement pstmt = conn.prepareStatement(
    "SELECT * FROM table_name WHERE id = ?");
pstmt.setInt(1, 1);
ResultSet rset = pstmt.executeQuery();

pstmt.setInt(1, 2);
rset = pstmt.executeQuery();  // 同一 Cursor

Oracle 內部處理

-- 綁定變數 SQL 內部呈現
SELECT * FROM table_name WHERE id = :1

-- 解析一次，執行計劃快取，後續僅替換綁定值 → 瞬間執行

綁定變數雙重收益

1. 效能：消除重複硬解析，CPU 降低 50-90%，回應時間穩定
2. 記憶體：單一 Cursor 服務所有參數值，Shared Pool 佔用降低 90%+
3. 安全：原生防 SQL 注入，字面值拼接是注入溫床

為什麼開發者仍用字面值拼接？

• 「動態構造 SQL 靈活」：WHERE 條件可選、ORDER BY 動態、IN 列表長度不定
• 「PreparedStatement 麻煩」：參數位置固定、動態條件需動態構建 SQL 字串再 Prepare

動態 SQL 的正確處理模式

-- 動態 WHERE 條件：用 CASE/DECODE 或動態構建但最終用綁定變數
-- 範例：可選條件查詢
StringBuilder sql = new StringBuilder("SELECT * FROM t WHERE 1=1");
List