【Oracle】Result Cache完全ガイド｜RESULT_CACHEヒント・PL/SQL関数キャッシュ・管理・V$RESULT_CACHE_STATISTICSまで解説

-- /*+ RESULT_CACHE */ ヒントでクエリ結果をキャッシュ
SELECT /*+ RESULT_CACHE */
    d.department_id,
    d.department_name,
    COUNT(e.employee_id)      AS emp_count,
    ROUND(AVG(e.salary), 0)  AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_id;

-- 2回目の実行: Result Cache からデータが返る（物理 I/O なし）
-- ベーステーブル（departments/employees）が更新されると自動で無効化される

-- 全体設定でキャッシュが有効な環境でも特定 SQL はキャッシュしない
SELECT /*+ NO_RESULT_CACHE */ *
FROM orders
WHERE order_date >= SYSDATE - 1;  -- SYSDATE を含む → キャッシュ不適切

-- 現在のモードを確認（デフォルト: MANUAL）
SHOW PARAMETER result_cache_mode;

-- セッションレベルで FORCE に変更（HINT なしでも全クエリをキャッシュしようとする）
ALTER SESSION SET result_cache_mode = FORCE;

-- システムレベルで変更（通常は推奨しない）
ALTER SYSTEM SET result_cache_mode = FORCE SCOPE=BOTH;
-- MANUAL に戻す
ALTER SYSTEM SET result_cache_mode = MANUAL SCOPE=BOTH;

-- 部門名を返すルックアップ関数（頻繁に呼ばれるが DB 更新頻度が低い）
CREATE OR REPLACE FUNCTION get_department_name(
    p_dept_id IN departments.department_id%TYPE
)
RETURN VARCHAR2
RESULT_CACHE RELIES_ON (departments)   -- 依存テーブルを明示（Oracle 11g）
-- Oracle 12c 以降は RELIES_ON を省略しても自動検出
AS
    v_name departments.department_name%TYPE;
BEGIN
    SELECT department_name
    INTO v_name
    FROM departments
    WHERE department_id = p_dept_id;

    RETURN v_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END get_department_name;
/

-- 使用例: 同じ引数で2回目以降はキャッシュから返る
SELECT employee_id, get_department_name(department_id) AS dept_name
FROM employees
WHERE department_id IN (10, 20, 50);

-- Result Cache の全体統計を確認
SELECT name, value
FROM V$RESULT_CACHE_STATISTICS
ORDER BY name;
-- 主要な統計:
-- Block Count Maximum: キャッシュの最大ブロック数
-- Block Count Current: 現在使用中のブロック数
-- Result Size Maximum (Blocks): 1エントリの最大サイズ（ブロック）
-- Create Count Success: キャッシュ作成成功件数
-- Find Count: キャッシュヒット件数
-- Invalidation Count: 無効化件数

-- キャッシュされている SQL とその統計を確認
SELECT
    id,
    type,          -- RESULT（SQL キャッシュ）または DEPENDENCY（依存テーブル）
    status,        -- PUBLISHED（有効）/ INVALID（無効）
    name,          -- SQL テキストまたはオブジェクト名
    scan_count,    -- スキャン（ヒット）回数
    invalidations, -- 無効化された回数
    ROUND(lru_number)  AS lru_priority
FROM V$RESULT_CACHE_OBJECTS
WHERE type = 'RESULT'
  AND status = 'PUBLISHED'
ORDER BY scan_count DESC
FETCH FIRST 10 ROWS ONLY;

-- 全 Result Cache を無効化する（メンテナンス時など）
BEGIN
    DBMS_RESULT_CACHE.FLUSH;
END;
/

-- キャッシュの状態レポートを表示する
BEGIN
    DBMS_RESULT_CACHE.MEMORY_REPORT;
END;
/

-- Result Cache に割り当てられたメモリサイズを確認
SHOW PARAMETER result_cache_max_size;
-- デフォルト: SGA の 0.5%（ASMM モード）または1MB

-- Result Cache のサイズを増やす（例: 64MB）
ALTER SYSTEM SET result_cache_max_size = 64M SCOPE=BOTH;

-- 1エントリの最大サイズを確認
SHOW PARAMETER result_cache_max_result;
-- デフォルト: result_cache_max_size の 5%