【PL/SQL】DBMS_APPLICATION_INFOで運用観測性を底上げ｜ラッパー設計・進捗バー・AWR集計・分散トレース・監視SQL7種

CREATE OR REPLACE PROCEDURE process_order(
  p_order_id IN NUMBER,
  p_user_id  IN NUMBER
) AS
BEGIN
  -- ① 機能名と最初のステップを設定
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'OrderService',
    action_name => 'validate'
  );

  -- ② エンドユーザーを識別
  DBMS_APPLICATION_INFO.SET_CLIENT_IDENTIFIER(
    client_id => 'user_' || p_user_id
  );

  -- ③ 処理対象の具体情報
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(
    client_info => 'ORDER_ID=' || p_order_id
  );

  pkg_validator.run(p_order_id);

  -- ④ ステップ切り替え
  DBMS_APPLICATION_INFO.SET_ACTION('persist');
  pkg_repo.save(p_order_id);

  DBMS_APPLICATION_INFO.SET_ACTION('notify');
  pkg_notify.send(p_order_id);

  -- ⑤ 終了時にクリア（重要！）
  DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(NULL);
  -- CLIENT_IDENTIFIERは継続セッションなら次の処理でも使える
END;
/

CREATE OR REPLACE PACKAGE pkg_app_info AS
  -- スコープに入る（前のコンテキストを退避し新しい値を設定）
  PROCEDURE enter(p_module VARCHAR2, p_action VARCHAR2);

  -- スコープから出る（前のコンテキストへ復元）
  PROCEDURE leave;

  -- ステップ切り替え（actionだけ更新）
  PROCEDURE step(p_action VARCHAR2);
END pkg_app_info;
/

CREATE OR REPLACE PACKAGE BODY pkg_app_info AS
  TYPE t_ctx IS RECORD(module VARCHAR2(48), action VARCHAR2(32));
  TYPE t_stack IS TABLE OF t_ctx INDEX BY PLS_INTEGER;
  g_stack t_stack;

  PROCEDURE enter(p_module VARCHAR2, p_action VARCHAR2) AS
    v_idx PLS_INTEGER := NVL(g_stack.LAST, 0) + 1;
    v_cur t_ctx;
  BEGIN
    -- 現在のコンテキストを退避（スタックにpush）
    DBMS_APPLICATION_INFO.READ_MODULE(v_cur.module, v_cur.action);
    g_stack(v_idx) := v_cur;

    -- 新しいコンテキストを設定
    DBMS_APPLICATION_INFO.SET_MODULE(p_module, p_action);
  END;

  PROCEDURE leave AS
    v_idx PLS_INTEGER := g_stack.LAST;
    v_prev t_ctx;
  BEGIN
    IF v_idx IS NULL THEN
      DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
    ELSE
      v_prev := g_stack(v_idx);
      g_stack.DELETE(v_idx);
      DBMS_APPLICATION_INFO.SET_MODULE(v_prev.module, v_prev.action);
    END IF;
  END;

  PROCEDURE step(p_action VARCHAR2) AS
  BEGIN
    DBMS_APPLICATION_INFO.SET_ACTION(p_action);
  END;
END pkg_app_info;
/

-- 利用例：例外時もleaveが必ず呼ばれる
CREATE OR REPLACE PROCEDURE main_batch AS
BEGIN
  pkg_app_info.enter('BillingBatch', 'load');
  BEGIN
    pkg_extract.run;
    pkg_app_info.step('transform');
    pkg_transform.run;
    pkg_app_info.step('insert');
    pkg_load.run;
  EXCEPTION
    WHEN OTHERS THEN
      pkg_app_info.leave;   -- 必ずクリア
      RAISE;
  END;
  pkg_app_info.leave;
END;
/

CREATE OR REPLACE PROCEDURE process_huge_data AS
  v_rindex     BINARY_INTEGER := DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT;
  v_slno       BINARY_INTEGER;
  v_total      NUMBER;
  v_processed  NUMBER := 0;
BEGIN
  -- 全件数を取得（進捗バーの分母）
  SELECT COUNT(*) INTO v_total FROM input_data;

  pkg_app_info.enter('DataLoad', 'process');

  FOR rec IN (SELECT * FROM input_data) LOOP
    -- 業務処理
    pkg_processor.handle(rec);
    v_processed := v_processed + 1;

    -- 進捗報告（毎100件ごとに更新）
    IF MOD(v_processed, 100) = 0 THEN
      DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
        rindex      => v_rindex,
        slno        => v_slno,
        op_name     => 'DataLoad processing',     -- 操作名
        target_desc => 'rows',                     -- 単位
        context     => 0,
        sofar       => v_processed,                -- 現在進捗
        totalwork   => v_total,                    -- 総量
        units       => 'rows'
      );
    END IF;
  END LOOP;

  pkg_app_info.leave;
END;
/

-- 監視側：V$SESSION_LONGOPSをポーリング
SELECT sid, opname, target,
       sofar, totalwork,
       ROUND(sofar*100/NULLIF(totalwork,0), 1) AS pct,
       elapsed_seconds AS elapsed_sec,
       time_remaining  AS remaining_sec,
       message
  FROM v$session_longops
 WHERE time_remaining > 0
 ORDER BY start_time DESC;

-- ① ASHから過去1時間のモジュール別CPU時間TOP10
SELECT
  module,
  action,
  COUNT(*) AS samples,
  ROUND(COUNT(*)/(60*60)*10, 2) AS estimated_active_seconds
FROM v$active_session_history
WHERE sample_time > SYSTIMESTAMP - INTERVAL '1' HOUR
  AND module IS NOT NULL
GROUP BY module, action
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

-- ② AWRから日次でモジュール別の負荷ランキング
SELECT
  module,
  SUM(elapsed_time)/1e6 AS total_sec,
  SUM(executions) AS execs,
  ROUND(SUM(elapsed_time)/SUM(executions)/1e3, 2) AS avg_ms,
  SUM(disk_reads) AS disk_reads,
  SUM(buffer_gets) AS buffer_gets
FROM dba_hist_sqlstat ss
JOIN dba_hist_snapshot s ON s.snap_id = ss.snap_id
WHERE s.begin_interval_time > SYSDATE - 1
  AND ss.module IS NOT NULL
GROUP BY module
ORDER BY total_sec DESC;

-- ③ 特定モジュール内のアクション別ボトルネック
SELECT
  action,
  COUNT(*) AS samples,
  COUNT(DISTINCT session_id) AS sessions,
  COUNT(*) FILTER(WHERE session_state = 'WAITING') AS waiting,
  ROUND(SUM(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE 0 END)*100.0
        /COUNT(*), 1) AS on_cpu_pct
FROM v$active_session_history
WHERE module = 'BillingBatch'
  AND sample_time > SYSTIMESTAMP - INTERVAL '6' HOUR
GROUP BY action
ORDER BY samples DESC;

-- ④ 時系列でモジュールの負荷変動を可視化
SELECT
  TO_CHAR(TRUNC(sample_time, 'MI'), 'HH24:MI') AS minute,
  module,
  COUNT(*) AS samples
FROM v$active_session_history
WHERE sample_time > SYSTIMESTAMP - INTERVAL '1' HOUR
  AND module IS NOT NULL
GROUP BY TRUNC(sample_time, 'MI'), module
ORDER BY minute, samples DESC;

-- アプリ層からトレースIDを渡す
CREATE OR REPLACE PROCEDURE api_create_order(
  p_trace_id IN VARCHAR2,
  p_user_id  IN NUMBER,
  p_payload  IN CLOB
) AS
BEGIN
  -- ① 相関IDを設定（W3C Trace ContextやAWS X-Ray IDなど）
  DBMS_APPLICATION_INFO.SET_CLIENT_IDENTIFIER(p_trace_id);
  DBMS_APPLICATION_INFO.SET_MODULE('OrderAPI', 'create');

  -- ② 業務処理
  pkg_order_service.create_order(p_user_id, p_payload);

  -- ③ クリア
  DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
  DBMS_APPLICATION_INFO.SET_CLIENT_IDENTIFIER(NULL);
END;
/

-- 監視側：特定の相関IDのトラブルシュート
SELECT s.sid, s.serial#, s.module, s.action,
       q.sql_id, q.sql_text
  FROM v$session s
  LEFT JOIN v$sqlarea q ON q.sql_id = s.sql_id
 WHERE s.client_identifier = '550e8400-e29b-41d4-a716-446655440000'
 ORDER BY s.last_call_et DESC;

-- ASHで該当リクエストの全SQLとボトルネックを抽出
SELECT sample_time, sql_id, event,
       module, action, blocking_session
  FROM v$active_session_history
 WHERE client_id = '550e8400-e29b-41d4-a716-446655440000'
 ORDER BY sample_time;

import oracle.jdbc.OracleConnection;

// 接続を取得
Connection conn = DriverManager.getConnection(URL, USER, PASS);
OracleConnection ora = (OracleConnection) conn;

// セッション情報をクライアント側から設定
String[] metrics = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
metrics[OracleConnection.END_TO_END_MODULE_INDEX] = "OrderAPI";
metrics[OracleConnection.END_TO_END_ACTION_INDEX] = "createOrder";
metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = traceId;
ora.setEndToEndMetrics(metrics, (short) 0);

// この接続で実行されるすべてのSQL/PL/SQLは
// V$SESSION の MODULE/ACTION/CLIENT_IDENTIFIER に上記が設定された状態で実行される

try (CallableStatement cs = conn.prepareCall("BEGIN pkg_order.create(?); END;")) {
  cs.setLong(1, orderId);
  cs.execute();
}

// 接続返却前にクリア
metrics[OracleConnection.END_TO_END_MODULE_INDEX] = null;
metrics[OracleConnection.END_TO_END_ACTION_INDEX] = null;
metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = null;
ora.setEndToEndMetrics(metrics, (short) 0);

-- ① 現在動いている全モジュール・アクションを俯瞰
SELECT s.username, s.sid, s.serial#,
       s.module, s.action, s.client_identifier,
       s.client_info, s.status,
       s.last_call_et AS sec_in_call,
       s.event AS wait_event
FROM v$session s
WHERE s.module IS NOT NULL
ORDER BY s.last_call_et DESC;

-- ② 進行中の長時間処理の進捗バー
SELECT sid, opname, target,
       sofar, totalwork,
       ROUND(sofar*100/NULLIF(totalwork,0), 1) AS pct,
       elapsed_seconds AS elapsed_sec,
       time_remaining  AS eta_sec,
       message
FROM v$session_longops
WHERE time_remaining > 0
ORDER BY start_time DESC;

-- ③ 特定モジュールの全実行を集計
SELECT module, action, COUNT(*) AS exec_cnt,
       SUM(elapsed_time)/1e6 AS total_sec,
       AVG(elapsed_time)/1e3 AS avg_ms,
       MAX(elapsed_time)/1e3 AS max_ms
FROM v$sqlstats
WHERE module = 'BillingBatch'
GROUP BY module, action
ORDER BY total_sec DESC;

-- ④ 特定相関IDの追跡（過去24時間）
SELECT sample_time, module, action,
       sql_id, event, session_state
FROM v$active_session_history
WHERE client_id = '&trace_id'
  AND sample_time > SYSTIMESTAMP - INTERVAL '24' HOUR
ORDER BY sample_time;

-- ⑤ ロックで止まっているモジュールを特定
SELECT b.sid AS blocker_sid, b.module AS blocker_module,
       w.sid AS waiter_sid, w.module AS waiter_module,
       w.event, w.seconds_in_wait
FROM v$session w
JOIN v$session b ON b.sid = w.blocking_session
WHERE w.blocking_session IS NOT NULL;

-- ⑥ モジュール別の待機イベントランキング
SELECT module, event, COUNT(*) AS samples
FROM v$active_session_history
WHERE sample_time > SYSTIMESTAMP - INTERVAL '1' HOUR
  AND module IS NOT NULL
  AND session_state = 'WAITING'
GROUP BY module, event
ORDER BY samples DESC
FETCH FIRST 20 ROWS ONLY;

-- ⑦ クライアントID別のリソース消費（マルチテナント分析）
SELECT client_id,
       COUNT(*) AS samples,
       COUNT(DISTINCT session_id) AS sessions,
       COUNT(DISTINCT sql_id) AS sql_count
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1
  AND client_id IS NOT NULL
GROUP BY client_id
ORDER BY samples DESC
FETCH FIRST 20 ROWS ONLY;