この記事について
23c FREEでStatspackのスナップショット取得ジョブが期待通りに実行されなかったためDBMS_SCHEDULERで作り直しました。DBMS_JOBからDBMS_SCHEDULERへの置き換えを実践したという話です。
以下の構成を使用しています。
- Oracle Database 23c Free
- Oracle Linux Server release 8.2
spauto.sqlで作ったジョブ(DBMS_JOB)が動かないことについてサポート情報があるのであれば、それを参照するとよいでしょう。
ジョブが実行されない - スナップショットがない
Statspackをインストールし、spauto.sqlでスナップショット取得ジョブを設定しました。
3時間ほど後でStatspackレポートを出力しようとスナップショットを確認すると、スナップショットが一つしかありません。
SQL> select snap_id,to_char(snap_time,'yyyy-mm-dd hh24:mi:ss') snap_time,
2 snap_level from stats$snapshot order by snap_id;
SNAP_ID SNAP_TIME SNAP_LEVEL
---------- ------------------- ----------
1 2024-04-16 10:00:00 5
SQL>
ジョブの設定を確認するとNEXT_DATEは11:00になっています。インスタンス、PDBともに、ずっと働いているのですが、どうやら初回の後は実行できていないようです。
SQL> select JOB,LOG_USER,LAST_DATE,NEXT_DATE,WHAT from dba_jobs;
JOB LOG_USER LAST_DATE NEXT_DATE WHAT
---------- ---------------- -------------------- -------------------- ------------------------------
1 PERFSTAT 2024-04-16 10:00:00 2024-04-16 11:00:00 statspack.snap;
現在時刻を確認すると次のとおり13:36。もう永久に実行されない気がします。
SQL> select name,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from v$pdbs;
NAME TO_CHAR(SYSDATE,'YY
-------------------- -------------------
FREEPDB1 2024-04-16 13:36:35
あまり意味はないと思いつつstatspack.snapを手動実行してみます。
スナップショットを取得できることは確認できました。しかしその後のスケジュールに変化はなくジョブが動くことは確認できませんでした。
SQL> execute statspack.snap
PL/SQL procedure successfully completed.
SQL> select snap_id,to_char(snap_time,'yyyy-mm-dd hh24:mi:ss') snap_time,
2 snap_level from stats$snapshot order by snap_id;
SNAP_ID SNAP_TIME SNAP_LEVEL
---------- ------------------- ----------
1 2024-04-16 10:00:00 5
2 2024-04-16 13:39:51 5 ★手動実行したスナップショット
SQL>
スナップショット取得ジョブをDBMS_SCHEDULERで作り直す
さらにJOB_QUEUE_PROCESSES初期化パラメータやジョブ設定を確認したりしましたが特に問題は見当たりません。
とはいえ定期実行のジョブが動かない状態は好ましくないので、DBMS_SCHEDULERに置き換えることとしました。
Oracle Database データベース管理者ガイド 23c
spauto.sqlのジョブにこだわる理由もないうえ、何よりもDBMS_JOBは12cR2から非推奨になっています。DBMS_SCHEDULERへの置き換えについては11gR2のころから言及されていたと思います。
DBMS_JOBの定義を確認する
DBMS_SCHEDULERでの設定にあたって現在のDBMS_JOBのジョブを確認します。
SQL> select JOB,LOG_USER,LAST_DATE,NEXT_DATE,WHAT from dba_jobs;
JOB LOG_USER LAST_DATE NEXT_DATE WHAT
---------- ---------------- -------------------- -------------------- ------------------------------
1 PERFSTAT 2024-04-16 10:00:00 2024-04-16 11:00:00 statspack.snap;
DBA_SCHEDULER_JOBSでも確認してみる
管理者ガイドに「DBMS_JOBはDBMS_SCHEDULERジョブのインタフェースとして機能する」という文言があるので、spauto.sqlで作ったジョブがDBA_SCHEDULER_JOBSにどのように定義されているか確認してみます。
JOB_NAMEにDBMS_JOB$_1という名前で定義されています。
※ほかのジョブもあるためPERFSTATユーザーのものに絞っ他表示です。
SQL> select JOB_NAME,JOB_TYPE,JOB_ACTION,START_DATE,REPEAT_INTERVAL
from dba_scheduler_jobs
where owner='PERFSTAT';
JOB_NAME JOB_TYPE JOB_ACTION START_DATE REPEAT_INTERVAL
---------------- ---------------- -------------------------------- ---------------------------------------- ----------------------------------------
DBMS_JOB$_1 PLSQL_BLOCK statspack.snap; 16-APR-24 10.00.00.000000 AM +09:00 trunc(SYSDATE+1/24,'HH')
SQL> select JOB_NAME,LAST_START_DATE,NEXT_RUN_DATE
from dba_scheduler_jobs
where owner='PERFSTAT';
JOB_NAME LAST_START_DATE NEXT_RUN_DATE
---------------- ---------------------------------------- ----------------------------------------
DBMS_JOB$_1 16-APR-24 10.00.00.525703 AM +09:00 16-APR-24 11.00.00.000000 AM +00:00
DBMS_JOBのジョブを削除する
spauto.sqlで作成されたジョブを削除します。
DBMS_JOB.REMOVEプロシージャにJOB_IDを与えて実行します。JOB_IDは前項の「DBMS_JOBの定義を確認する」で得た値で、この例では「1」です。
SQL> BEGIN
2 DBMS_JOB.REMOVE(1);
3 END;
4 /
PL/SQL procedure successfully completed.
DBA_JOBSを確認するとジョブはなくなっています。
SQL> select job,log_user,last_date,next_date,what,interval from dba_jobs;
no rows selected
DBA_SCHEDULER_JOBSの内容も確認すると「DBMS_JOB$_1」はなくなっています。
SQL> select job_name,job_type,job_action,start_date,repeat_interval
from dba_scheduler_jobs
where owner='PERFSTAT';
no rows selected
DBMS_SCHEDULERジョブを作成する
あらためてDBMS_SCHEDULERでスナップショット実行ジョブを作成します。
作成するジョブの設定は「DBMS_JOBの定義を確認する」で確認した内容をもとにします。
この例での設定を下表に示します。
| パラメータ | 設定値 | 説明 | DBMS_JOB設定 |
|---|---|---|---|
| job_name | STATSPACK_SNAP | 任意のジョブ名。この例では左記の名称を設定する。 | (該当なし) |
| job_type | PLSQL_BLOCK | DBMS_SCHEDULERの仕様に従って設定。この例で作成するジョブはPL/SQLブロックであるため左記の値を設定。 | (該当なし) |
| job_action | statspack.snap; | 実行する処理。spauto.sqlの設定内容を拝借。 | what |
| start_date | TRUNC(SYSDATE+1/24,'HH') | ジョブの初回実行日時を設定。spauto.sqlによる設定を拝借。 | next_date |
| repeat_interval | FREQ = HOURLY; INTERVAL = 1 | 実行頻度。spauto.sqlによる毎時0分実行（trunc(SYSDATE+1/24,'HH')）をDBMS_SCHEDULERの表現で設定。※DBMS_JOBから登録された内ジョブでは trunc(SYSDATE+1/24,'HH') が設定されている。 | interval |
| enabled | TRUE | 作成と同時にジョブを有効化する。（デフォルト：FALSE） | (該当なし) |
DBMS_SCHEDULER.CREATE_JOBを実行してジョブを作成します。
SQL> BEGIN
2 DBMS_SCHEDULER.CREATE_JOB(
3 job_name => 'STATSPACK_SNAP'
4 ,job_type => 'PLSQL_BLOCK'
5 ,job_action => 'statspack.snap;'
6 ,start_date => TRUNC(SYSDATE+1/24,'HH')
7 ,repeat_interval => 'FREQ = HOURLY; INTERVAL = 1'
8 ,enabled => TRUE
10 );
11 END;
12 /
PL/SQL procedure successfully completed.
作成されたジョブの設定を確認します。
SQL> select job_name,job_type,job_action,start_date,repeat_interval
from dba_scheduler_jobs
where owner='PERFSTAT';
JOB_NAME JOB_TYPE JOB_ACTION START_DATE REPEAT_INTERVAL
---------------- ---------------- -------------------------------- ---------------------------------------- ----------------------------------------
STATSPACK_SNAP PLSQL_BLOCK statspack.snap; 17-APR-24 12.00.00.000000 AM +09:00 FREQ = HOURLY; INTERVAL = 1
SQL> select JOB_NAME,ENABLED from dba_scheduler_jobs where owner='PERFSTAT';
JOB_NAME ENABL
---------------- -----
STATSPACK_SNAP TRUE
実行LAST_START_DETE、NEXT_RUN_DATEを確認します。すでに初回の実行したようです。
SQL> select JOB_NAME,LAST_START_DATE,NEXT_RUN_DATE
from dba_scheduler_jobs
where owner='PERFSTAT';
JOB_NAME LAST_START_DATE NEXT_RUN_DATE
---------------- ---------------------------------------- ----------------------------------------
STATSPACK_SNAP 17-APR-24 12.00.00.078877 AM +09:00 17-APR-24 01.00.00.087405 AM +09:00
現在時刻を確認すると、ちょうどジョブを実行したタイミングでした。
SQL> select name,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from v$pdbs;
NAME TO_CHAR(SYSDATE,'YY
-------------------- -------------------
FREEPDB1 2024-04-17 00:00:34
おまけでDBA_JOBSも確認してみました。DBMS_SCHEDULERで実行しているので、DBA_JOBSに設定されたジョブはありません。
SQL> select job,log_user,last_date,next_date,what,interval from dba_jobs;
no rows selected
STATSPACKジョブの稼働を確認する
再び時間をあけてジョブがスケジュール通りに実行されているかを確認しました。
SQL> select snap_id,to_char(snap_time,'yyyy-mm-dd hh24:mi:ss') snap_time,
2 snap_level from stats$snapshot order by snap_id;
SNAP_ID SNAP_TIME SNAP_LEVEL
---------- ------------------- ----------
1 2024-04-16 10:00:00 5
2 2024-04-16 13:39:51 5
11 2024-04-17 00:00:00 5
12 2024-04-17 01:00:00 5
13 2024-04-17 02:00:02 5
（以下略）
ジョブがスケジュール通りに実行され、ちゃんとスナップショットが取得できていました。
めでたし、めでたし。
まとめ
DBMS_JOBで動かなかったジョブをDBMS_SCHEDULERで設定しなおすことにより期待通り動作するようにできました。
DBMS_JOBは11gR2でDBMS_SCHEDULERへの置き換えについて言及され始め、12cR2からは非推奨になった機能です。製品ドキュメントが代替機能への移行をガイドしているのあれば、それに従うべきだと体験した気がします。
いまさらながら、今後STATSPACKのジョブを設定する際はspauto.sqlではなくDBMS_SCHEDULERを使用していこうと思います。
参考資料
製品ドキュメント
- Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 23c (F73918-06)
- Oracle Database データベース管理者ガイド 23c (F73877-03)