【Oracle】バインド変数完全ガイド｜ハードパース削減・CURSOR_SHARING・アダプティブカーソル共有まで解説

-- NG: リテラルを埋め込む（実行のたびにハードパースが走る）
-- 以下の3つは Oracle には別々の SQL として扱われる
SELECT * FROM orders WHERE customer_id = 101;
SELECT * FROM orders WHERE customer_id = 102;
SELECT * FROM orders WHERE customer_id = 103;
-- → 毎回ハードパース発生。1000件のリクエストで1000回のハードパース

-- OK: バインド変数を使う（ライブラリキャッシュをヒットさせる）
SELECT * FROM orders WHERE customer_id = :cust_id;
-- → 最初の1回だけハードパース。以降はソフトパースでヒット

-- V$SQL でハードパースの状況を確認する
SELECT sql_text, parse_calls, executions, loads
FROM   V$SQL
WHERE  sql_text LIKE '%orders%customer_id%'
  AND  command_type = 3   -- SELECT
ORDER  BY parse_calls DESC;
-- loads: ハードパース回数（parse_calls - loadsがソフトパース数）
-- LOADS が多い = バインド変数未使用のリテラル SQL が原因の可能性が高い

-- 暗黙バインド: PL/SQL 変数は自動的にバインド変数として処理される
DECLARE
    v_cust_id orders.customer_id%TYPE := 101;
    v_count   NUMBER;
BEGIN
    -- v_cust_id はバインド変数として扱われる（Oracle が自動でバインドする）
    SELECT COUNT(*) INTO v_count
    FROM   orders
    WHERE  customer_id = v_cust_id;  -- ← 暗黙バインド（ハードパースは1回）

    DBMS_OUTPUT.PUT_LINE(v_cust_id || '件: ' || v_count);

    v_cust_id := 102;
    SELECT COUNT(*) INTO v_count
    FROM   orders
    WHERE  customer_id = v_cust_id;  -- ← 同じ SQL テキスト → ソフトパース

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

-- カーソルでも同様（バインド変数として扱われる）
DECLARE
    CURSOR c_orders(p_cust_id NUMBER) IS
        SELECT order_id, order_date, total_amount
        FROM   orders
        WHERE  customer_id = p_cust_id;   -- ← バインド変数
BEGIN
    FOR rec IN c_orders(101) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.order_id || ': ' || rec.total_amount);
    END LOOP;
    FOR rec IN c_orders(102) LOOP        -- 同じ SQL → ソフトパース
        DBMS_OUTPUT.PUT_LINE(rec.order_id || ': ' || rec.total_amount);
    END LOOP;
END;
/

-- EXECUTE IMMEDIATE + USING: 動的 SQL でもバインド変数を使う
DECLARE
    v_sql     VARCHAR2(500);
    v_count   NUMBER;
    v_cust_id NUMBER := 101;
    v_year    NUMBER := 2024;
BEGIN
    -- プレースホルダは :名前 形式で書く
    v_sql := 'SELECT COUNT(*) FROM orders WHERE customer_id = :cust_id AND EXTRACT(YEAR FROM order_date) = :yr';

    -- USING で値を渡す（順序は SQL テキスト内のプレースホルダの順序）
    EXECUTE IMMEDIATE v_sql INTO v_count USING v_cust_id, v_year;
    DBMS_OUTPUT.PUT_LINE('件数: ' || v_count);

    -- IN/OUT パラメータも使える
    -- IN OUT は USING に IN OUT または IN/OUT で指定する（デフォルトは IN）
END;
/

-- EXECUTE IMMEDIATE + DML でのバインド変数
DECLARE
    v_sql       VARCHAR2(500);
    v_order_id  NUMBER := 9999;
    v_new_total NUMBER := 12500;
BEGIN
    v_sql := 'UPDATE orders SET total_amount = :new_total WHERE order_id = :oid';
    EXECUTE IMMEDIATE v_sql USING v_new_total, v_order_id;
    COMMIT;
END;
/

-- BULK COLLECT との組み合わせ
DECLARE
    TYPE num_tab IS TABLE OF NUMBER;
    v_sql   VARCHAR2(500);
    v_ids   num_tab;
    v_dept  NUMBER := 10;
BEGIN
    v_sql := 'SELECT employee_id FROM employees WHERE department_id = :dept_id';
    EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_ids USING v_dept;
    DBMS_OUTPUT.PUT_LINE('部門 ' || v_dept || ' の社員数: ' || v_ids.COUNT);
END;
/

-- 現在の CURSOR_SHARING 設定を確認する
SHOW PARAMETER cursor_sharing;
-- または
SELECT name, value FROM V$PARAMETER WHERE name = 'cursor_sharing';

-- セッション単位で変更する（テスト・調査目的）
ALTER SESSION SET CURSOR_SHARING = FORCE;

-- システム全体で変更する（本番での応急措置）
ALTER SYSTEM SET CURSOR_SHARING = FORCE SCOPE = BOTH;

-- ライブラリキャッシュにリテラルが大量にある SQL を確認する
-- FORCE_MATCHING_SIGNATURE が同じ = CURSOR_SHARING=FORCE なら共有できる SQL
SELECT force_matching_signature, COUNT(*) AS cursor_count,
       MAX(sql_text) AS sample_sql
FROM   V$SQL
WHERE  force_matching_signature != 0
GROUP  BY force_matching_signature
HAVING COUNT(*) > 100               -- 100 個以上同じ SQL が溜まっているもの
ORDER  BY cursor_count DESC;
-- カーソル数が多いほどバインド変数化の効果が大きい

-- バインド変数に対してどの計画が使われているかを確認する
SELECT sql_id, plan_hash_value, executions, rows_processed,
       elapsed_time / 1000 AS elapsed_ms,
       is_bind_sensitive,    -- バインド値によって最適計画が変わりうるかどうか
       is_bind_aware         -- ACS が有効（適応型カーソル共有が動作中）かどうか
FROM   V$SQL
WHERE  sql_text LIKE '%orders%status%'
ORDER  BY executions DESC;

-- SQL_ID から実行計画を確認する
SELECT * FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        sql_id      => '3abc1defg234x',
        cursor_child_no => 0,
        format      => 'ALLSTATS LAST'
    )
);
-- child_no が複数ある場合 → Adaptive Cursor Sharing が動いて複数の計画が保持されている

-- ACS の動作状況を確認する
SELECT sql_id, child_number, plan_hash_value, executions,
       is_bind_sensitive,     -- YES: バインド値に応じて計画が変わりうる
       is_bind_aware,         -- YES: ACS が有効でバインド値ごとに計画を使い分け中
       is_shareable           -- NO: このカーソルは使われなくなった（無効化済み）
FROM   V$SQL
WHERE  sql_id = '3abc1defg234x'
ORDER  BY child_number;
-- child_number が 0, 1, 2 ... と複数あれば ACS が複数の計画を保持している

-- 各子カーソルのバインド値の範囲を確認する
SELECT *
FROM   V$SQL_CS_SELECTIVITY
WHERE  sql_id = '3abc1defg234x';
-- LOW / HIGH: このカーソルが適用されるバインド値の選択率の範囲
-- SELECTIVITY: このカーソルが最適となる選択率レンジ

-- ACS が有効になっている SQL を探す
SELECT sql_id, sql_text, child_number, plan_hash_value
FROM   V$SQL
WHERE  is_bind_aware = 'Y'
  AND  executions > 1000     -- 高頻度 SQL に絞る
ORDER  BY executions DESC;