DBMS_SCHEDULER で日次バッチや月次集計を運用すると、標準ビューの実行履歴だけでは足りない場面が出てきます。「業務上の処理単位で成功したか」「処理中に止まっていないか」「前回より遅くなっていないか」「失敗時にどこまで進んだか」を追うには、ジョブ実行履歴を自前の監視テーブルへ集約しておくと扱いやすくなります。
この記事では、DBMS_SCHEDULER の実行履歴、PL/SQL側のロギング、ハートビート、SLA監視、異常検知をまとめて設計します。標準の USER_SCHEDULER_JOB_RUN_DETAILS を補助情報として使いながら、業務処理ごとの相関IDや進捗を残す構成です。
- 標準ビューだけでなく、業務単位の
job_runとjob_heartbeatを持つと調査が速くなります。 - ハートビートは時刻だけを主キーにせず、
heartbeat_idを持たせると高頻度更新でも安全です。 - 監視SQLは必ず索引前提で設計します。
job_name, started_at、run_id, beat_atは最低限用意します。 AUTONOMOUS_TRANSACTIONでログを残す場合、業務データとログの整合は別管理になります。- 引数付きプロシージャをSchedulerから呼ぶ場合は、
number_of_argumentsとSET_JOB_ARGUMENT_VALUEを明示します。
Schedulerの基本操作は DBMS_SCHEDULER完全ガイド、リトライ設計は 例外設計と再試行パターン、ジョブ依存制御は DBMS_SCHEDULERでジョブ管理を極める と合わせて読むと整理しやすくなります。
集中管理テーブルの基本設計
まず、ジョブ定義、実行履歴、ハートビートを分けます。ジョブ定義は監視対象のメタ情報、実行履歴は1回の実行単位、ハートビートは処理中の進捗を表します。
CREATE TABLE job_master (
job_name VARCHAR2(128) PRIMARY KEY,
job_group VARCHAR2(64),
display_name VARCHAR2(200),
enabled_flag CHAR(1) DEFAULT 'Y' NOT NULL,
expected_minutes NUMBER(8),
sla_minutes NUMBER(8),
owner_team VARCHAR2(100),
alert_level VARCHAR2(20) DEFAULT 'WARN',
created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
updated_at TIMESTAMP
);
CREATE TABLE job_run (
run_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
job_name VARCHAR2(128) NOT NULL,
scheduler_job_name VARCHAR2(128),
correlation_id VARCHAR2(64),
status VARCHAR2(20) NOT NULL,
started_at TIMESTAMP NOT NULL,
ended_at TIMESTAMP,
duration_sec NUMBER(12,3),
target_date DATE,
target_key VARCHAR2(100),
rows_processed NUMBER,
error_code NUMBER,
error_message VARCHAR2(4000),
CONSTRAINT fk_job_run_master
FOREIGN KEY (job_name) REFERENCES job_master(job_name),
CONSTRAINT ck_job_run_status
CHECK (status IN ('RUNNING','SUCCESS','FAILED','CANCELED','TIMEOUT'))
);
CREATE TABLE job_heartbeat (
heartbeat_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
run_id NUMBER NOT NULL,
beat_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
phase VARCHAR2(100),
message VARCHAR2(1000),
rows_done NUMBER,
CONSTRAINT fk_job_heartbeat_run
FOREIGN KEY (run_id) REFERENCES job_run(run_id)
);
CREATE INDEX ix_job_run_name_started
ON job_run(job_name, started_at DESC);
CREATE INDEX ix_job_run_status_started
ON job_run(status, started_at DESC);
CREATE INDEX ix_job_run_correlation
ON job_run(correlation_id);
CREATE INDEX ix_job_heartbeat_run_beat
ON job_heartbeat(run_id, beat_at DESC);
job_heartbeat は (job_name, run_id, beat_at) のような時刻込み主キーにしたくなりますが、高頻度にbeatを打つと同一時刻扱いで衝突する可能性があります。監視ログは後から増え続けるため、サロゲートキーを持たせ、検索用の索引を別に作るほうが安定します。
ロギングAPIを作る
ジョブ本体から直接INSERT/UPDATEを書くと、処理ごとにログ仕様がばらつきます。開始、ハートビート、成功、失敗を共通パッケージ化しておくと、監視SQLや通知処理が単純になります。
下記のロギングAPIは、ジョブ本体がロールバックしてもログを残すために PRAGMA AUTONOMOUS_TRANSACTION を使います。障害調査には便利ですが、業務データがロールバックされても「途中まで処理したログ」は残ります。ログは証跡であり、業務データの確定状態とは別に読む前提にしてください。
CREATE OR REPLACE PACKAGE pkg_job_monitor AS
FUNCTION start_run(
p_job_name IN VARCHAR2,
p_scheduler_job_name IN VARCHAR2 DEFAULT NULL,
p_target_date IN DATE DEFAULT NULL,
p_target_key IN VARCHAR2 DEFAULT NULL
) RETURN NUMBER;
PROCEDURE heartbeat(
p_run_id IN NUMBER,
p_phase IN VARCHAR2,
p_message IN VARCHAR2 DEFAULT NULL,
p_rows_done IN NUMBER DEFAULT NULL
);
PROCEDURE finish_success(
p_run_id IN NUMBER,
p_rows_processed IN NUMBER DEFAULT NULL
);
PROCEDURE finish_failed(
p_run_id IN NUMBER,
p_error_code IN NUMBER,
p_error_message IN VARCHAR2
);
END pkg_job_monitor;
/
CREATE OR REPLACE PACKAGE BODY pkg_job_monitor AS
FUNCTION seconds_between(p_start TIMESTAMP, p_end TIMESTAMP) RETURN NUMBER IS
v_interval INTERVAL DAY TO SECOND := p_end - p_start;
BEGIN
RETURN EXTRACT(DAY FROM v_interval) * 86400
+ EXTRACT(HOUR FROM v_interval) * 3600
+ EXTRACT(MINUTE FROM v_interval) * 60
+ EXTRACT(SECOND FROM v_interval);
END;
FUNCTION start_run(
p_job_name IN VARCHAR2,
p_scheduler_job_name IN VARCHAR2 DEFAULT NULL,
p_target_date IN DATE DEFAULT NULL,
p_target_key IN VARCHAR2 DEFAULT NULL
) RETURN NUMBER IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_run_id NUMBER;
v_correlation_id VARCHAR2(64) := RAWTOHEX(SYS_GUID());
BEGIN
INSERT INTO job_run(
job_name,
scheduler_job_name,
correlation_id,
status,
started_at,
target_date,
target_key
)
VALUES(
p_job_name,
p_scheduler_job_name,
v_correlation_id,
'RUNNING',
SYSTIMESTAMP,
p_target_date,
p_target_key
)
RETURNING run_id INTO v_run_id;
COMMIT;
RETURN v_run_id;
END;
PROCEDURE heartbeat(
p_run_id IN NUMBER,
p_phase IN VARCHAR2,
p_message IN VARCHAR2 DEFAULT NULL,
p_rows_done IN NUMBER DEFAULT NULL
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO job_heartbeat(run_id, phase, message, rows_done)
VALUES(p_run_id, p_phase, SUBSTR(p_message, 1, 1000), p_rows_done);
COMMIT;
END;
PROCEDURE finish_success(
p_run_id IN NUMBER,
p_rows_processed IN NUMBER DEFAULT NULL
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_started_at TIMESTAMP;
v_ended_at TIMESTAMP := SYSTIMESTAMP;
BEGIN
SELECT started_at
INTO v_started_at
FROM job_run
WHERE run_id = p_run_id
FOR UPDATE;
UPDATE job_run
SET status = 'SUCCESS',
ended_at = v_ended_at,
duration_sec = seconds_between(v_started_at, v_ended_at),
rows_processed = p_rows_processed
WHERE run_id = p_run_id;
COMMIT;
END;
PROCEDURE finish_failed(
p_run_id IN NUMBER,
p_error_code IN NUMBER,
p_error_message IN VARCHAR2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_started_at TIMESTAMP;
v_ended_at TIMESTAMP := SYSTIMESTAMP;
BEGIN
SELECT started_at
INTO v_started_at
FROM job_run
WHERE run_id = p_run_id
FOR UPDATE;
UPDATE job_run
SET status = 'FAILED',
ended_at = v_ended_at,
duration_sec = seconds_between(v_started_at, v_ended_at),
error_code = p_error_code,
error_message = SUBSTR(p_error_message, 1, 4000)
WHERE run_id = p_run_id;
COMMIT;
END;
END pkg_job_monitor;
/
TIMESTAMP 同士の差は INTERVAL DAY TO SECOND です。単純に (ended_at - started_at) * 86400 と書くと、DATE 前提の計算と混ざって壊れやすくなります。秒数化するなら、上記のように EXTRACT で分解するか、明示的に DATE へCASTしてから計算します。
ジョブ本体からロギングAPIを呼ぶ
ジョブ本体では、開始時に run_id を取得し、区切りごとにハートビートを打ちます。失敗時は例外を握りつぶさず、ログへ残したうえで再送出します。
CREATE OR REPLACE PROCEDURE run_monthly_agg(
p_ym IN DATE
) IS
v_run_id NUMBER;
v_rows NUMBER := 0;
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(
module_name => 'BATCH_MONTHLY_AGG',
action_name => TO_CHAR(p_ym, 'YYYY-MM')
);
v_run_id := pkg_job_monitor.start_run(
p_job_name => 'MONTHLY_AGG',
p_scheduler_job_name => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') || '.MONTHLY_AGG_JOB',
p_target_date => p_ym,
p_target_key => TO_CHAR(p_ym, 'YYYYMM')
);
DBMS_SESSION.SET_IDENTIFIER('JOB_RUN_ID=' || v_run_id);
pkg_job_monitor.heartbeat(v_run_id, 'START', 'monthly aggregation started');
-- ここに実処理を書く。例では処理件数だけを模擬する。
v_rows := 1000;
pkg_job_monitor.heartbeat(v_run_id, 'AGGREGATE', 'aggregation completed', v_rows);
pkg_job_monitor.finish_success(v_run_id, v_rows);
EXCEPTION
WHEN OTHERS THEN
IF v_run_id IS NOT NULL THEN
pkg_job_monitor.finish_failed(v_run_id, SQLCODE, SQLERRM);
END IF;
RAISE;
END;
/
DBMS_APPLICATION_INFO と DBMS_SESSION.SET_IDENTIFIER を使うと、セッション監視やAWR/ASH調査時にもジョブ実行単位を追いやすくなります。性能診断と組み合わせる場合は、実行計画や実測値を見る記事も参考になります。
引数付きプロシージャをSchedulerから呼ぶ
run_monthly_agg(p_ym IN DATE) のように引数を持つプロシージャをSchedulerから呼ぶ場合、単に job_action => 'RUN_MONTHLY_AGG' と書くだけでは足りません。number_of_arguments を指定し、ジョブ作成後に引数値を設定します。
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'MONTHLY_AGG_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'RUN_MONTHLY_AGG',
number_of_arguments => 1,
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
enabled => FALSE,
comments => 'Monthly aggregation job with monitoring log'
);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => 'MONTHLY_AGG_JOB',
argument_position => 1,
argument_value => TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM'), 'YYYY-MM-DD')
);
DBMS_SCHEDULER.ENABLE('MONTHLY_AGG_JOB');
END;
/
SET_JOB_ARGUMENT_VALUE は文字列として値を渡します。DATE引数へ渡す場合は、プロシージャ側で文字列引数を受けて TO_DATE する設計にしたほうが、NLS設定の影響を避けやすいケースもあります。固定値ではなく毎回対象月を変えたい場合は、ジョブ本体側で対象月を計算するか、起動用ラッパープロシージャを用意します。
失敗・長時間化・停止疑いを検出する
監視SQLは、必ず索引が効く条件で書きます。全期間の job_run を毎回フルスキャンすると、監視そのものが重くなります。基本は直近期間、未完了、SLA超過、ハートビート停止を切り出します。
-- 直近24時間の失敗
SELECT job_name, run_id, started_at, ended_at, error_code, error_message
FROM job_run
WHERE status = 'FAILED'
AND started_at >= SYSTIMESTAMP - INTERVAL '1' DAY
ORDER BY started_at DESC;
-- RUNNINGのままSLA超過
SELECT r.job_name, r.run_id, r.started_at, m.sla_minutes
FROM job_run r
JOIN job_master m ON m.job_name = r.job_name
WHERE r.status = 'RUNNING'
AND m.sla_minutes IS NOT NULL
AND r.started_at < SYSTIMESTAMP - NUMTODSINTERVAL(m.sla_minutes, 'MINUTE')
ORDER BY r.started_at;
-- ハートビート停止疑い
SELECT r.job_name, r.run_id, r.started_at, MAX(h.beat_at) AS last_beat_at
FROM job_run r
LEFT JOIN job_heartbeat h ON h.run_id = r.run_id
WHERE r.status = 'RUNNING'
GROUP BY r.job_name, r.run_id, r.started_at
HAVING MAX(h.beat_at) IS NULL
OR MAX(h.beat_at) < SYSTIMESTAMP - INTERVAL '10' MINUTE
ORDER BY r.started_at;
ハートビート停止は「必ず異常」ではありません。長いSQLを1本実行しているだけでbeatが打てない場合もあります。その場合は、処理フェーズの切れ目、チャンク単位、外部API呼び出しの前後など、待ちが長くなりやすい箇所でbeatを打つ設計にします。
標準ビューと突き合わせる
自前ログだけを見ると、Scheduler側で起動に失敗したケースを見逃すことがあります。標準ビューの USER_SCHEDULER_JOB_RUN_DETAILS と突き合わせ、DBMS_SCHEDULER自体の失敗も確認します。
SELECT
d.job_name,
d.status AS scheduler_status,
d.actual_start_date,
d.run_duration,
d.error#,
d.additional_info
FROM user_scheduler_job_run_details d
WHERE d.job_name = 'MONTHLY_AGG_JOB'
AND d.actual_start_date >= SYSTIMESTAMP - INTERVAL '7' DAY
ORDER BY d.actual_start_date DESC;
-- 自前ログとの突き合わせ
SELECT
r.job_name,
r.run_id,
r.status AS app_status,
r.started_at,
r.ended_at,
d.status AS scheduler_status,
d.error# AS scheduler_error
FROM job_run r
LEFT JOIN user_scheduler_job_run_details d
ON d.job_name = r.scheduler_job_name
AND CAST(d.actual_start_date AS TIMESTAMP) BETWEEN r.started_at - INTERVAL '1' MINUTE
AND r.started_at + INTERVAL '5' MINUTE
WHERE r.started_at >= SYSTIMESTAMP - INTERVAL '7' DAY
ORDER BY r.started_at DESC;
突き合わせ条件は環境に合わせて調整します。Scheduler名、相関ID、開始時刻、対象年月など、安定して結びつけられるキーを持たせておくと、障害調査がかなり楽になります。
通知対象を絞り込む
失敗のたびに通知すると、運用者が通知を見なくなります。通知対象は、重要ジョブ、SLA超過、連続失敗、未実行、ハートビート停止などに分け、重要度を付けます。
WITH last_runs AS (
SELECT
r.*,
ROW_NUMBER() OVER(
PARTITION BY r.job_name
ORDER BY r.started_at DESC
) AS rn
FROM job_run r
WHERE r.started_at >= SYSTIMESTAMP - INTERVAL '3' DAY
)
SELECT
m.alert_level,
lr.job_name,
lr.run_id,
lr.status,
lr.started_at,
lr.ended_at,
lr.error_message
FROM last_runs lr
JOIN job_master m ON m.job_name = lr.job_name
WHERE lr.rn = 1
AND (
lr.status = 'FAILED'
OR (
lr.status = 'RUNNING'
AND m.sla_minutes IS NOT NULL
AND lr.started_at < SYSTIMESTAMP - NUMTODSINTERVAL(m.sla_minutes, 'MINUTE')
)
)
ORDER BY
CASE m.alert_level WHEN 'CRITICAL' THEN 1 WHEN 'WARN' THEN 2 ELSE 3 END,
lr.started_at;
通知はメール、Slack、Teamsなどに流せますが、DB側の責務は「通知候補を正しく抽出すること」までにしておくと保守しやすくなります。外部通知はアプリケーション、ジョブランナー、監視基盤に任せる構成も有効です。
運用で見るチェックリスト
- ジョブ定義、実行履歴、ハートビートを分けているか
- 監視SQLに必要な索引を作っているか
- ハートビート主キーが時刻依存になりすぎていないか
AUTONOMOUS_TRANSACTIONの意味を運用者が理解しているか- Scheduler引数を
number_of_argumentsとSET_JOB_ARGUMENT_VALUEで設定しているか - 秒数計算で
TIMESTAMPとDATEを混同していないか - 標準ビューと自前ログの両方を確認できるか
- 通知対象を絞り込み、通知疲れを防いでいるか
まとめ
DBMS_SCHEDULER の実行履歴を実務で使える形にするには、標準ビューだけに頼らず、業務単位の job_run、進捗を示す job_heartbeat、監視メタ情報の job_master を持つと扱いやすくなります。
特に重要なのは、再実行しやすいログ設計、索引を前提にした監視SQL、引数付きジョブの正しい作成、AUTONOMOUS_TRANSACTION の注意点です。ジョブが失敗した後に「どこまで進んだか」「再実行してよいか」「通知すべきか」をすぐ判断できるようにしておくと、夜間バッチや月次処理の運用品質が大きく上がります。
