【Oracle】PL/SQL プロファイラ完全ガイド｜DBMS_PROFILER・DBMS_HPROF でボトルネックを特定する方法まで解説

-- DBMS_PROFILER が結果を書き込むテーブルを作成する
-- Oracle が提供するスクリプトを実行する（DBA が実行）
-- パスは Oracle バージョンによって異なる

-- UNIX/Linux の場合
-- @$ORACLE_HOME/rdbms/admin/profload.sql  -- パッケージのロード
-- @$ORACLE_HOME/rdbms/admin/proftab.sql   -- 結果テーブルの作成

-- Windows の場合（バックスラッシュはエスケープ不要で sqldeveloper から実行）
-- @%ORACLE_HOME%/rdbms/admin/profload.sql
-- @%ORACLE_HOME%/rdbms/admin/proftab.sql

-- テーブルが作成されたか確認する
SELECT table_name FROM USER_TABLES
WHERE table_name LIKE 'PLSQL_PROFILER%'
ORDER BY table_name;
-- PLSQL_PROFILER_DATA    : 各行の実行時間・呼び出し回数
-- PLSQL_PROFILER_RUNS    : プロファイリングの実行単位
-- PLSQL_PROFILER_UNITS   : プロファイリング対象のプログラムユニット

-- DBMS_PROFILER の EXECUTE 権限を付与する
GRANT EXECUTE ON DBMS_PROFILER TO hr_user;

-- プロファイリングを開始する
DECLARE
    v_ret NUMBER;
BEGIN
    -- START_PROFILER でプロファイリングを開始する
    -- run_comment: このプロファイリング実行に付ける名前
    v_ret := DBMS_PROFILER.START_PROFILER(
        run_comment => '月次集計バッチ プロファイリング 2026-04-09'
    );

    IF v_ret != 0 THEN
        DBMS_OUTPUT.PUT_LINE('PROFILER 開始失敗: ' || v_ret);
        RETURN;
    END IF;
END;
/

-- プロファイリング対象のプロシージャを実行する
EXEC process_monthly_sales;

-- プロファイリングを停止して結果を書き込む
DECLARE
    v_ret NUMBER;
BEGIN
    -- FLUSH_DATA で途中経過を書き込む（長時間バッチの中間確認に使える）
    v_ret := DBMS_PROFILER.FLUSH_DATA;

    -- STOP_PROFILER でプロファイリングを終了する
    v_ret := DBMS_PROFILER.STOP_PROFILER;
    IF v_ret != 0 THEN
        DBMS_OUTPUT.PUT_LINE('PROFILER 停止失敗: ' || v_ret);
    END IF;
END;
/

-- 最新のプロファイリング実行 ID を確認する
SELECT runid, run_comment, run_date, run_total_time / 1e9 AS total_sec
FROM PLSQL_PROFILER_RUNS
ORDER BY runid DESC
FETCH FIRST 5 ROWS ONLY;

-- 特定の実行 ID で、実行時間が多い行 TOP 20 を確認する
SELECT
    u.unit_owner,
    u.unit_name,
    u.unit_type,
    d.line#,
    d.total_time / 1e9         AS total_sec,   -- ナノ秒 → 秒
    d.min_time  / 1e9          AS min_sec,
    d.max_time  / 1e9          AS max_sec,
    d.total_occur,                              -- 実行回数
    ROUND(d.total_time / NULLIF(d.total_occur, 0) / 1e6) AS avg_ms  -- 平均ms
FROM PLSQL_PROFILER_DATA d
JOIN PLSQL_PROFILER_UNITS u
    ON d.runid = u.runid AND d.unit_number = u.unit_number
WHERE d.runid = (SELECT MAX(runid) FROM PLSQL_PROFILER_RUNS)
  AND d.total_time > 0
  AND u.unit_name NOT LIKE 'SYS.%'
ORDER BY d.total_time DESC
FETCH FIRST 20 ROWS ONLY;

-- ソースコードと実行時間を照合する
SELECT
    s.line,
    d.total_time / 1e6 AS ms,
    d.total_occur      AS calls,
    s.text
FROM PLSQL_PROFILER_DATA d
JOIN PLSQL_PROFILER_UNITS u ON d.runid = u.runid AND d.unit_number = u.unit_number
JOIN ALL_SOURCE s ON u.unit_owner = s.owner AND u.unit_name = s.name
                  AND u.unit_type = s.type AND d.line# = s.line
WHERE d.runid  = (SELECT MAX(runid) FROM PLSQL_PROFILER_RUNS)
  AND u.unit_name = 'PROCESS_MONTHLY_SALES'
  AND d.total_time > 0
ORDER BY d.line#;

-- DBMS_HPROF が生成するプロファイルファイルの保存先ディレクトリを作成する
CREATE OR REPLACE DIRECTORY hprof_dir AS '/tmp/hprof';
GRANT READ, WRITE ON DIRECTORY hprof_dir TO hr_user;

-- 分析結果を格納するテーブルを作成する（プロビジョニングスクリプト）
-- @$ORACLE_HOME/rdbms/admin/dbmshptab.sql

-- テーブルが作成されたか確認する
SELECT table_name FROM USER_TABLES
WHERE table_name LIKE 'DBMSHP%'
ORDER BY table_name;
-- DBMSHP_FUNCTION_INFO : サブプログラムごとの実行情報
-- DBMSHP_PARENT_CHILD_INFO : 呼び出し関係
-- DBMSHP_RUNS : 実行単位

-- プロファイリングを開始する
BEGIN
    DBMS_HPROF.START_PROFILING(
        location  => 'HPROF_DIR',          -- ディレクトリ名（大文字）
        filename  => 'monthly_batch.trc',   -- 出力ファイル名
        max_depth => 30                       -- 最大コール深度（省略可）
    );
END;
/

-- プロファイリング対象の処理を実行する
EXEC process_monthly_sales;

-- プロファイリングを停止する
BEGIN
    DBMS_HPROF.STOP_PROFILING;
END;
/

-- プロファイリングデータを分析してテーブルに格納する
DECLARE
    v_run_id NUMBER;
BEGIN
    v_run_id := DBMS_HPROF.ANALYZE(
        location    => 'HPROF_DIR',
        filename    => 'monthly_batch.trc',
        run_comment => '月次集計バッチ 2026-04-09'
    );
    DBMS_OUTPUT.PUT_LINE('Run ID: ' || v_run_id);
END;
/

-- 実行時間の多いサブプログラム TOP 20 を確認する（最新実行分）
SELECT
    fi.owner,
    fi.module,
    fi.type,
    fi.function                AS func_name,
    fi.calls,
    ROUND(fi.elapsed_time / 1e6)   AS total_ms,
    ROUND(fi.function_elapsed_time / 1e6) AS self_ms,   -- 自身のみの時間（子呼び出しを除く）
    fi.line#
FROM DBMSHP_FUNCTION_INFO fi
WHERE fi.runid = (SELECT MAX(runid) FROM DBMSHP_RUNS)
  AND fi.owner NOT IN ('SYS', 'SYSTEM')
ORDER BY fi.elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

-- 呼び出し関係（コールチェーン）を確認する
SELECT
    pfi.function   AS parent_func,
    cfi.function   AS child_func,
    pc.calls,
    ROUND(pc.elapsed_time / 1e6) AS elapsed_ms
FROM DBMSHP_PARENT_CHILD_INFO pc
JOIN DBMSHP_FUNCTION_INFO pfi ON pc.runid = pfi.runid AND pc.parentsymid = pfi.symbolid
JOIN DBMSHP_FUNCTION_INFO cfi ON pc.runid = cfi.runid AND pc.childsymid  = cfi.symbolid
WHERE pc.runid = (SELECT MAX(runid) FROM DBMSHP_RUNS)
ORDER BY pc.elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;