23c FreeでStatspackジョブをDBMS_SCHEDULERで設定した話

# この記事について
23c FREEでStatspackのスナップショット取得ジョブが期待通りに実行されなかったためDBMS_SCHEDULERで作り直しました。DBMS_JOBからDBMS_SCHEDULERへの置き換えを実践したという話です。

以下の構成を使用しています。
* Oracle Database 23c Free
* Oracle Linux Server release 8.2

:::note warn
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ともに、ずっと働いているのですが、どうやら初回の後は実行できていないようです。
```:DBA_JOBSでの定義
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 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2828082/2faaf0d9-6f4a-02e1-441b-aeb321482034.png)

:::note info 
`spauto.sql`のジョブにこだわる理由もないうえ、何よりもDBMS_JOBは12cR2から非推奨になっています。DBMS_SCHEDULERへの置き換えについては11gR2のころから言及されていたと思います。
:::

## DBMS_JOBの定義を確認する
DBMS_SCHEDULERでの設定にあたって現在のDBMS_JOBのジョブを確認します。
```:DBA_JOBSでの定義
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;
```


:::note info
DBA_SCHEDULER_JOBSでも確認してみる
管理者ガイドに「DBMS_JOBはDBMS_SCHEDULERジョブのインタフェースとして機能する」という文言があるので、spauto.sqlで作ったジョブがDBA_SCHEDULER_JOBSにどのように定義されているか確認してみます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2828082/5dc134f2-7207-8c34-8938-6ca84eabbda3.png)

JOB_NAMEにDBMS_JOB$_1という名前で定義されています。
※ほかのジョブもあるためPERFSTATユーザーのものに絞っ他表示です。
```:DBA_SCHEDULER_JOBSでの定義
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')
```

```:DBA_SCHEDULER_JOBSでの定義（実行予定）
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の定義を確認する](https://qiita.com/drafts/2e1f187ad7cb01e00d9a/edit#dbms_job%E3%81%AE%E5%AE%9A%E7%BE%A9%E3%82%92%E7%A2%BA%E8%AA%8D%E3%81%99%E3%82%8B)」で得た値で、この例では「1」です。
```
SQL> BEGIN
  2    DBMS_JOB.REMOVE(1);
  3  END;
  4  /

PL/SQL procedure successfully completed.
```

DBA_JOBSを確認するとジョブはなくなっています。
```:DBA_JOBSの確認
SQL> select job,log_user,last_date,next_date,what,interval from dba_jobs;

no rows selected
```

::: note info
DBA_SCHEDULER_JOBSの内容も確認すると「DBMS_JOB$_1」はなくなっています。
```:DBA_SCHEDULER_JOBSの確認
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の定義を確認する]( 
https://qiita.com/drafts/2e1f187ad7cb01e00d9a/edit#dbms_job%E3%81%AE%E5%AE%9A%E7%BE%A9%E3%82%92%E7%A2%BA%E8%AA%8D)」で確認した内容をもとにします。

この例での設定を下表に示します。
<table>
  <caption>DBMS_SCHEDULER.CREATE_JOB設定例</caption>
  <thead>
    <tr>
      <th>パラメータ</th> <th>設定値</th> <th>説明</th> <th>DBMS_JOB設定</th>
    </tr>
  </thead>
  <tr><td> job_name </td> <td>STATSPACK_SNAP</td><td>任意のジョブ名。この例では左記の名称を設定する。</td><td>(該当なし)</td></tr>
  <tr><td> job_type </td> <td>PLSQL_BLOCK</td><td>DBMS_SCHEDULERの仕様に従って設定。この例で作成するジョブはPL/SQLブロックであるため左記の値を設定。</td><td>(該当なし)</td></tr>
  <tr><td> job_action </td> <td>statspack.snap;</td><td>実行する処理。spauto.sqlの設定内容を拝借。</td><td>what</td></tr>
  <tr><td> start_date </td> <td>TRUNC(SYSDATE+1/24,'HH')</td><td>ジョブの初回実行日時を設定。spauto.sqlによる設定を拝借。</td><td>next_date</td></tr>
  <tr><td> repeat_interval </td> <td>FREQ = HOURLY; INTERVAL = 1 </td><td>実行頻度。spauto.sqlによる毎時0分実行（trunc(SYSDATE+1/24,'HH')）をDBMS_SCHEDULERの表現で設定。※DBMS_JOBから登録された内ジョブでは trunc(SYSDATE+1/24,'HH') が設定されている。</td><td>interval</td></tr>
  <tr><td> enabled </td> <td>TRUE</td><td>作成と同時にジョブを有効化する。（デフォルト：FALSE）</td><td>(該当なし)</td></tr>
</table>

`DBMS_SCHEDULER.CREATE_JOB`を実行してジョブを作成します。

```:DBMS_SCHEDULERでのジョブ作成 
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.
``` 

作成されたジョブの設定を確認します。
```:DBA_SCHEDULER_JOBSでのジョブ設定確認
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を確認します。すでに初回の実行したようです。
```:DBA_SCHEDULER_JOBSでのジョブ設定確認(実行予定)
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
```

:::note info
おまけでDBA_JOBSも確認してみました。DBMS_SCHEDULERで実行しているので、DBA_JOBSに設定されたジョブはありません。
```: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)