【Oracle】バインド変数完全ガイド｜ハードパース削減・ピーキング問題・Adaptive Cursor Sharingまで解説

-- この3つは別々のSQL ID が割り当てられ、毎回ハードパースが走る
SELECT * FROM employees WHERE department_id = 10;
SELECT * FROM employees WHERE department_id = 20;
SELECT * FROM employees WHERE department_id = 30;

-- テキストが同一なので1つのSQL IDに集約。2回目以降はソフトパース
SELECT * FROM employees WHERE department_id = :dept_id;

-- バインド変数の宣言
VARIABLE dept_id NUMBER
VARIABLE emp_name VARCHAR2(50)

-- 値を設定
EXEC :dept_id := 10
EXEC :emp_name := 'SCOTT'

-- バインド変数を使って SELECT
SELECT employee_id, last_name, salary
FROM   employees
WHERE  department_id = :dept_id;

-- 現在の値を確認
PRINT dept_id

VARIABLE result_salary NUMBER

BEGIN
    SELECT salary
    INTO   :result_salary
    FROM   employees
    WHERE  employee_id = 100;
END;
/

PRINT result_salary

DECLARE
    v_dept_id  employees.department_id%TYPE := 10;
    v_count    NUMBER;
BEGIN
    -- v_dept_id は Oracle が自動的にバインド変数として処理する
    SELECT COUNT(*)
    INTO   v_count
    FROM   employees
    WHERE  department_id = v_dept_id;  -- バインド変数として扱われる

    DBMS_OUTPUT.PUT_LINE('件数: ' || v_count);
END;
/

DECLARE
    v_dept_id NUMBER := 10;
    v_sql     VARCHAR2(200);
BEGIN
    -- BAD: 文字列連結でリテラルを埋め込むとハードパースが毎回発生
    -- さらに SQL インジェクションリスクがある
    v_sql := 'SELECT COUNT(*) FROM employees WHERE department_id = ' || v_dept_id;
    -- EXECUTE IMMEDIATE v_sql の場合もバインド変数を使うべき
END;
/

// NG: Statement はリテラル埋め込みになりハードパースが毎回発生
// Statement stmt = conn.createStatement();
// stmt.executeQuery("SELECT * FROM employees WHERE department_id = " + deptId);

// OK: PreparedStatement でバインド変数を使用
String sql = "SELECT employee_id, last_name, salary FROM employees WHERE department_id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, deptId);   // 1番目の ? に値を設定
ResultSet rs = pstmt.executeQuery();

// ループで繰り返し使う場合（SQL の再利用が効く）
int[] deptIds = {10, 20, 30};
for (int id : deptIds) {
    pstmt.setInt(1, id);
    rs = pstmt.executeQuery();
    // 結果処理...
}
pstmt.close();

# NG: 文字列フォーマットでリテラルを埋め込む
# cursor.execute(f"SELECT * FROM employees WHERE department_id = {dept_id}")

# OK: 名前付きバインド変数を使用
sql = "SELECT employee_id, last_name FROM employees WHERE department_id = :dept_id"
cursor.execute(sql, dept_id=10)

# OK: 位置バインド変数を使用（:1 形式）
cursor.execute(
    "SELECT employee_id, last_name FROM employees WHERE department_id = :1 AND salary > :2",
    [10, 50000]
)

# 複数行 INSERT（executemany でバルク処理）
data = [(10, "Alice", 70000), (20, "Bob", 65000)]
cursor.executemany(
    "INSERT INTO employees (dept_id, name, salary) VALUES (:1, :2, :3)",
    data
)
conn.commit()

-- 現在の設定を確認
SELECT name, value FROM v$parameter WHERE name = 'cursor_sharing';

-- セッションレベルで変更（テスト用）
ALTER SESSION SET cursor_sharing = FORCE;

-- システムレベルで変更（本番変更は慎重に）
ALTER SYSTEM SET cursor_sharing = FORCE;

-- status = 'ACTIVE' のデータは全体の99%（フルスキャンが最適）
-- status = 'CLOSED' のデータは全体の0.1%（インデックスが最適）

-- 最初に CLOSED で実行 → インデックス使用の実行計画がキャッシュに保存される
SELECT * FROM orders WHERE status = :status;  -- :status = 'CLOSED'

-- 次に ACTIVE で実行 → キャッシュされたインデックス実行計画が使われ超低速
SELECT * FROM orders WHERE status = :status;  -- :status = 'ACTIVE' ← 遅い！

-- 同一 SQL の複数カーソルを確認（IS_BIND_SENSITIVE, IS_BIND_AWARE）
SELECT sql_id,
       child_number,
       executions,
       is_bind_sensitive,   -- 値によって実行計画が変わりうるか
       is_bind_aware,       -- ACS が有効化されているか
       plan_hash_value
FROM   v$sql
WHERE  sql_text LIKE '%orders%status%'
ORDER  BY sql_id, child_number;

-- バインド変数の使用状況と直近のバインド値を確認
SELECT s.sql_id,
       s.executions,
       s.elapsed_time / NULLIF(s.executions, 0) AS avg_elapsed_us,
       b.name          AS bind_name,
       b.datatype_string,
       b.value_string  AS last_value,
       b.last_captured
FROM   v$sql s
       JOIN v$sql_bind_capture b ON s.sql_id = b.sql_id
                                 AND s.child_number = b.child_number
WHERE  s.sql_text LIKE '%employees%'
ORDER  BY s.executions DESC
FETCH  FIRST 20 ROWS ONLY;

-- ライブラリキャッシュ内でハードパース多発・リテラル埋め込みの疑いがある SQL を特定
-- force_matching_signature が同じで SQL_ID が異なる → リテラル違いの同一構造 SQL
SELECT force_matching_signature,
       COUNT(*)           AS cursor_count,
       SUM(executions)    AS total_exec,
       MIN(sql_text)      AS sample_sql
FROM   v$sql
WHERE  force_matching_signature != 0
GROUP  BY force_matching_signature
HAVING COUNT(*) > 10        -- 同一構造で10種類以上のSQLがある場合が要注意
ORDER  BY cursor_count DESC
FETCH  FIRST 20 ROWS ONLY;