【PL/SQL】DBMS_APPLICATION_INFOで処理状況を監視に出力する方法

【PL/SQL】DBMS_APPLICATION_INFOで処理状況を監視に出力する方法 PL/SQL

DBMS_APPLICATION_INFOは、実行中のPL/SQLやアプリケーション処理の「いま何をやっているか」をデータベース側へ明示するための標準パッケージです。V$SESSIONやAWR、ASHの画面にモジュール名やアクション名、長時間処理の進捗を出力できるため、性能分析や運用監視で非常に有効です。ここでは基本の使い方から、長時間処理の進捗表示、バッチでの設計パターン、計測ツールとの併用までを解説します。

基本：モジュール名とアクション名を設定する

モジュール名とアクション名はDBMS_APPLICATION_INFO.SET_MODULEで設定します。処理ブロックの入口で設定し、完了時にクリアするのが基本です。ユーザー側から見えるのはV$SESSIONのMODULE列とACTION列で、誰がどの機能を実行しているかを瞬時に把握できます。

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name  => 'MONTHLY_BILLING',
    action_name  => 'STEP1_LOAD');
  -- ここでSTEP1の処理
  DBMS_APPLICATION_INFO.SET_ACTION('STEP2_AGGREGATE');
  -- ここでSTEP2の処理
  DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL); -- 終了時にクリア
END;
/

アクションだけを切り替えたい場合はSET_ACTIONを逐次呼び出します。例外で中断する可能性がある処理では、例外節で必ずNULLへ戻すようにしておくと監視画面に残骸が残りません。

V$SESSIONから現在の状況を確認する

監視者はセッション識別子やログインユーザーでV$SESSIONを引き、MODULEとACTIONで状況を把握できます。トラブル時に「どのステップで止まっているか」を即座に特定できます。

SELECT sid, serial#, module, action, status, event
  FROM v$session
 WHERE username = 'APP_USER'
 ORDER BY logon_time DESC;

長時間処理の進捗を出す（SET_SESSION_LONGOPS）

数分から数時間に渡る処理は、DBMS_APPLICATION_INFO.SET_SESSION_LONGOPSを使って進捗バーのような情報をV$SESSION_LONGOPSへ出力します。トータル作業量と現在の進捗値を段階的に報告すると、残り時間の見積りが監視画面で可視化されます。

DECLARE
  r    BINARY_INTEGER;
  sofar NUMBER := 0;
  total NUMBER := 100000; -- 総レコード数などの目標値
BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('MONTHLY_BILLING', 'STEP3_EXPORT');
  DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT(r, NULL, NULL, 'Export rows', 0, sofar, total, 'rows');
  FOR i IN 1..total LOOP
    -- 明細1件の処理
    sofar := sofar + 1;
    IF MOD(sofar, 1000) = 0 THEN
      DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(r, NULL, NULL, 'Export rows', 0, sofar, total, 'rows');
    END IF;
  END LOOP;
  DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
END;
/

監視側はV$SESSION_LONGOPSを参照すれば、今の進捗率や経過時間、完了予測を確認できます。

SELECT sl.opname, sl.sofar, sl.totalwork, sl.units, 
       ROUND(sl.sofar*100/NULLIF(sl.totalwork,0),1) AS pct
  FROM v$session_longops sl
 WHERE sl.username = 'APP_USER'
   AND sl.totalwork > 0
 ORDER BY sl.start_time DESC;

バッチ処理でのテンプレート化

実務ではモジュールとアクション、長時間処理の更新、完了後のクリアを毎回正しく書くのが肝心です。共通ヘルパーを用意すると漏れが減ります。

CREATE OR REPLACE PACKAGE app_instr AS
  PROCEDURE enter(p_module VARCHAR2, p_action VARCHAR2 := NULL);
  PROCEDURE action(p_action VARCHAR2);
  PROCEDURE leave;
  PROCEDURE longops_init(p_opname VARCHAR2, p_total NUMBER, p_units VARCHAR2 := 'rows');
  PROCEDURE longops_step(p_inc NUMBER := 1);
END;
/
CREATE OR REPLACE PACKAGE BODY app_instr AS
  g_r      BINARY_INTEGER;
  g_total  NUMBER;
  g_sofar  NUMBER;
  PROCEDURE enter(p_module VARCHAR2, p_action VARCHAR2) IS
  BEGIN
    DBMS_APPLICATION_INFO.SET_MODULE(p_module, p_action);
  END;
  PROCEDURE action(p_action VARCHAR2) IS
  BEGIN
    DBMS_APPLICATION_INFO.SET_ACTION(p_action);
  END;
  PROCEDURE leave IS
  BEGIN
    DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
  END;
  PROCEDURE longops_init(p_opname VARCHAR2, p_total NUMBER, p_units VARCHAR2) IS
  BEGIN
    g_total := p_total; g_sofar := 0;
    DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT(g_r, NULL, NULL, p_opname, 0, 0, g_total, p_units);
  END;
  PROCEDURE longops_step(p_inc NUMBER) IS
  BEGIN
    g_sofar := g_sofar + NVL(p_inc,1);
    DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(g_r, NULL, NULL, NULL, 0, g_sofar, g_total, NULL);
  END;
END;
/

テンプレート化したら、業務処理側では簡潔に計器を埋め込めます。

BEGIN
  app_instr.enter('ORDER_IMPORT','READ_FILE');
  -- 入力読み込み
  app_instr.action('VALIDATE_ROWS');
  -- バリデーション
  app_instr.longops_init('Import rows', 500000, 'rows');
  FOR i IN 1..500000 LOOP
    -- 1件処理
    IF MOD(i, 2000) = 0 THEN app_instr.longops_step(2000); END IF;
  END LOOP;
  app_instr.leave;
EXCEPTION
  WHEN OTHERS THEN
    app_instr.leave; -- 例外時も必ずクリア
    RAISE;
END;
/

DBMS_PROFILERやAWRとの併用

モジュール名とアクション名はAWRやASHのレポートにも記録されます。期間全体の遅延を俯瞰するにはAWRを参照し、行粒度でのホットスポット特定にはDBMS_PROFILERを使い、どのモジュールのどのアクションが重いかを二軸で見ると原因切り分けが速くなります。アプリケーションログの相関IDをMODULEやACTIONに含める運用にすると、監視結果とアプリログを突き合わせやすくなります。

命名規約と運用上の注意

MODULEにはシステム名や機能ブロックを短く入れ、ACTIONには現在のステップやフェーズを時系列で分かる語で入れると読み取りやすくなります。例外の握り潰しでACTIONが古いまま残ると誤認の原因となるため、必ず例外節でleave相当のクリア処理を呼びます。短時間の微細な処理まで頻繁にSET_ACTIONを呼ぶとオーバーヘッドとラッチ競合のノイズになるため、数秒以上のまとまりで更新する方が安定します。長時間処理のSET_SESSION_LONGOPSは全件ごとではなくバッチ単位で更新し、V$SESSION_LONGOPSの行が無制限に増えないよう適度な更新間隔を守ります。

まとめ

DBMS_APPLICATION_INFOは、データベース視点で処理の現在地と進捗を可視化する計器です。SET_MODULEとSET_ACTIONで機能名とステップを掲示し、SET_SESSION_LONGOPSで長時間処理の進捗を報告すれば、監視と障害対応のスピードが向上します。共通ヘルパーで必ず設定とクリアを行う習慣を作り、プロファイラやAWRと組み合わせてボトルネックを客観的に特定する運用へつなげましょう。