Oracle Databaseのジョブスケジューラー機能についてまとめてみた

## はじめに
システム運用する際に、ジョブ管理ソフトとしてJP1、WebSAM、Systemwalkerなどの有償ソフトや、cron、WindowsのタスクスケジューラーといったOSのジョブ管理機能を利用する事があると思いますが、Oracle Database(以下、Oracle)にもジョブ管理機能があります。

Oracleのジョブ管理機能をあまり利用する事は無いかもしれませんが、夜間バッチ実行中や実行後に、実データと統計情報との乖離が発生してパフォーマンスが悪化するような場合に、デフォルトの統計情報取得処理のタイミング以外で、Oracleのジョブ管理機能を利用して定期的にOracleの統計情報を再取得する事で問題を解消できたりします。

直近でも、この機能を利用する機会があったため、改めてOracleのジョブスケジューラー機能について整理してみたいと思います。

## 概要
Oracleのジョブスケジューラー機能では、以下のような事を行う事ができます。
- Oracle(ローカルおよびリモート)のPL/SQL、ストアドプロシージャ(PL/SQL、Java)、SQLスクリプト、RMANスクリプトの実行
- (ローカルおよびリモートの)アプリケーション、シェルスクリプトなどの外部実行可能ファイルの実行
- ジョブ実行のスケジューリング
    - 時間ベースのスケジューリング
    - イベントベースのスケジューリング(Oracleのアドバンスド・キューイング、ファイルが作成された事をトリガーにするなど)
    - 依存性スケジューリング(ジョブ・チェーンの作成、前のタスクの結果による条件分岐、ネストなど)
- ビジネス要件に基づくジョブの優先度付け
    - ジョブ・クラスによるリソースの制御
    - スケジュールに基づいたジョブの優先度の制御
- ジョブの管理と監視
    - Oracle Enterprise Manager Cloud ControlまたはSQLを使用した実行状況の確認
    - メール通知

Oracleスケジューラーの概要については、以下を参照ください。

https://docs.oracle.com/cd/F19136_01/admin/oracle-scheduler-concepts.html#GUID-6B8C0335-D36B-44D3-95BC-82FA2141D967

これだけ見ると、ジョブスケジューラーとしてかなり高機能で、有償のジョブ管理ソフトに近い事ができそうです。
ただ、通常のシステム運用ではOracleに関係の無いジョブが多数実行されるものであるため、ジョブ管理をOracleに全てまとめる事は少ないと思います。

本記事では、Oracleに関連するタスク(ジョブ)をスケジュール実行したい場合の例をいくつか紹介しつつ、その設定例を記載してみようと思います。

ジョブ・チェーンを利用し複数ジョブを1つのジョブにまとめるといった、複雑なジョブの設定方法については、以下を参照ください。

https://www.ashisuto.co.jp/db_blog/article/job-scheduling-using-oracle-scheduler.html

## Oracleのジョブスケジューラー設定
Oracleのジョブスケジューラー機能の設定は、Oracle Enterprise Manager Cloud Controlまたは、DBMS_SCHEDULERパッケージを利用します(SE2環境ではDBMS_SCHEDULERを利用)。

DBMS_SCHEDULERパッケージの詳細は、以下マニュアルを参照ください。

https://docs.oracle.com/cd/F19136_01/arpls/DBMS_SCHEDULER.html

:::note info
DBMS_SCHEDULERパッケージの基本的な機能を利用するためには、「CREATE JOB」または「CREATE ANY JOB」権限が必要です。
※一部特殊機能については「MANAGE SCHEDULER」権限が必要
:::

:::note info
Oracleには、以下のような自動メンテナンスタスクがあり、タスクの有効化、無効化はDBMS_AUTO_TASK_ADMINパッケージで行いますが、自動メンテナンスタスクのスケジュール(Window)変更は、DBMS_SCHEDULERパッケージで行います。
- 自動オプティマイザ統計収集
- 自動セグメント・アドバイザ
- 自動SQLチューニング・アドバイザ
:::

用途毎にDBMS_SCHEDULERパッケージのどのプロシージャを実行すればよいか一覧にまとめます。
| 用途 | 実行するプロシージャ |
|:-|:-|
| ジョブの作成 | CREATE_JOB、CREATE_JOBS |
| ジョブの変更 | SET_ATTRIBUTE、SET_JOB_ATTRIBUTES、SET_ATTRIBUTE_NULL |
| ジョブの実行 | RUN_JOB |
| ジョブのコピー | COPY_JOB |
| ジョブの削除 | DROP_JOB |
| ジョブの停止 | STOP_JOB |
| ジョブの無効化 | DISABLE |
| ジョブの有効化 | ENABL |

CREATE_JOBプロシージャでジョブを作成して定期的に処理を実行し、実行スケジュールなどを変更したい場合は、SET_ATTRIBUTEプロシージャで変更。
ジョブが不要になったら、DISABLEプロシージャ、DROP_JOBプロシージャで無効化または削除するといった使い方をします。

#### ジョブの作成
ジョブの作成は、DBMS_SCHEDULERパッケージのCREATE_JOBプロシージャを利用します。
作成時には、ジョブ・タイプ、実行する処理(プログラム)およびスケジュールなどを指定します。

ジョブ・タイプは、CREATE_JOBプロシージャのjob_type属性、処理の指定はjob_action属性、スケジュールはrepeat_interval属性で指定します。

ジョブ・タイプには、無名PL/SQLブロック、ストアド・プロシージャ、SQLスクリプトなどを指定する事ができ、実行する処理(プログラム)の指定は、ジョブ・タイプにより異なります。

また、複数のジョブで同じ処理(プログラム)が実行される場合は、名前付きプログラムとして処理(プログラム)をCREATE_PROGRAMプロシージャで作成しておき、CREATE_JOBプロシージャのjob_action属性ではなく、program_name属性で名前付きプログラムを指定する事も可能です。

| ジョブ・タイプ | ジョブ・タイプの意味 | 処理の指定方法<br>(job_actionに指定する場合) |
|:-|:-|:-|
| PLSQL_BLOCK | 無名PL/SQLブロック | 無名PL/SQLブロックのコードを記述 |
| STORED_PROCEDURE | PL/SQLプロシージャ、Javaストアド・プロシージャまたは外部Cサブプログラム | 大文字でプロシージャ名を指定 |
| EXECUTABLE | 外部実行可能ファイルを使用してデータベースの外部で実行される | 外部実行可能ファイルをフルパスで指定 |
| CHAIN | チェーンとして実行される | チェーン・オブジェクトの名前を指定 |
| EXTERNAL_SCRIPT | コマンド・シェルを使用する外部スクリプト(cmd.exe、シェル) | 外部スクリプトをフルパスで指定 |
| SQL_SCRIPT | SQL*Plusスクリプト | SQL*Plusスクリプトをフルパスで指定 |
| BACKUP_SCRIPT | RMANバックアップ・スクリプト | RMANバックアップ・スクリプトをフルパスで指定 |

ジョブの実行スケジュールは、スケジューラーのカレンダ構文で指定します。
<details><summary>カレンダ構文(マニュアルから一部を引用)</summary>

> frequency_clause = "FREQ" "=" ( predefined_frequency | user_defined_frequency )
> predefined_frequency = "YEARLY" | "MONTHLY" | "WEEKLY" | "DAILY" | 
> 　　"HOURLY" | "MINUTELY" | "SECONDLY"
> user_defined_frequency = named_schedule

> interval_clause = "INTERVAL" "=" intervalnum
> 　　intervalnum = 1 through 99
> bymonth_clause = "BYMONTH" "=" monthlist
> 　　monthlist = month ( "," month)*
> 　　month = numeric_month | char_month
> 　　numeric_month = 1 | 2 | 3 ...  12
> 　　char_month = "JAN" | "FEB" | "MAR" | "APR" | "MAY" | "JUN" |
> 　　"JUL" | "AUG" | "SEP" | "OCT" | "NOV" | "DEC"

> bydate_clause = "BYDATE" "=" date_list
> 　　date_list = date ( "," date)*
> 　　date = [YYYY]MMDD [ offset | span ]
> bymonthday_clause = "BYMONTHDAY" "=" monthday_list
> 　　monthday_list = monthday ( "," monthday)*
> 　　monthday = [minus] monthdaynum
> 　　monthdaynum = 1 through 31
> byday_clause = "BYDAY" "=" byday_list
> 　　byday_list = byday ( "," byday)*
> 　　byday = [weekdaynum] day
> 　　weekdaynum = [minus] daynum
> 　　daynum = 1 through 53 /* if frequency is yearly */
> 　　daynum = 1 through 5  /* if frequency is monthly */
> 　　day = "MON" | "TUE" | "WED" | "THU" | "FRI" | "SAT" | "SUN"
> BYTIME clause:  BYTIME=[hour_minute_second_list|minute_second_list]
> 　　hour_minute_second_list: hh24mmss, .., hh24mmss
> 　　minute_second_list: mmss, .. mmss
> byhour_clause = "BYHOUR" "=" hour_list
> 　　hour_list = hour ( "," hour)*
> 　　hour = 0 through 23
> byminute_clause = "BYMINUTE" "=" minute_list
> 　　minute_list = minute ( "," minute)*
> 　　minute = 0 through 59
> bysecond_clause = "BYSECOND" "=" second_list
> 　　second_list = second ( "," second)*
> 　　second = 0 through 59

> schedule_clause = named_schedule [ offset ]
> named_schedule = [schema "."] schedule
</details>

スケジュールについても、四半期毎など複数のジョブで指定されるものを、名前付きスケジュールとしてCREATE_SCHEDULEプロシージャで作成しておき、CREATE_JOBプロシージャのrepeat_interval属性ではなく、schedule_name属性で名前付きスケジュールを指定する事も可能です。

カレンダ構文は指定方法が少し分かりにくいため、ジョブの作成例としていくつか記載してみたいと思います。

#### 例1. 毎日AM5時10分にTEST_TABLEの統計情報を取得するジョブ
```plsql
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'TABLE_STATS_JOB',
   job_type           =>  'PLSQL_BLOCK',
   job_action => 'BEGIN' ||
   ' dbms_stats.gather_table_stats(ownname => null, tabname => ''TEST_TABLE'', cascade => true, no_invalidate => false);' ||
   ' END;',
   start_date         => systimestamp,
   repeat_interval    => 'FREQ=DAILY;BYHOUR=5;byminute=10;bysecond=0',
   auto_drop          => FALSE,
   enabled            => TRUE);
END;
/
```
ジョブ・タイプに'PLSQL_BLOCK'を指定し、job_actionに無名PL/SQLブロックを記述しています。

:::note warn
無名PL/SQLブロック内で文字列を指定する場合は、''TEST_TABLE''のように`''`で囲って記述する必要があります。
:::

実行スケジュールは、repeat_intervalで指定します。以下の`FREQ=DAILY`で日次処理である事を指定し、`BYHOUR=5;byminute=10;bysecond=0`で5時10分0秒を指定しています。
```
FREQ=DAILY;BYHOUR=5;byminute=10;bysecond=0
```
これにより、毎日5時10分0秒に、dbms_stats.gather_table_statsを実行してTEST_TABLEの統計情報を取得するジョブが登録されます。

このようなジョブを、バッチ処理などで特定のテーブルのレコードが大量に更新された後に実行する事で、実データと統計情報との乖離を解消し、パフォーマンス悪化を防ぐ事ができます。

#### 例2. 30分毎にスキーマ全体の統計情報を取得するジョブ(名前付きプログラム、スケジュール指定)
```plsql
begin
  dbms_scheduler.create_program(
    program_name => 'SCHEMA_STATS_PROGRAM',
    program_type => 'PLSQL_BLOCK',
    program_action => 'BEGIN' || 
    '  dbms_stats.gather_schema_stats(ownname => null, cascade => true, no_invalidate => false, options => ''GATHER AUTO'');' ||
    ' END;',
    enabled => FALSE
  ); 
  dbms_scheduler.enable('SCHEMA_STATS_PROGRAM');

  dbms_scheduler.create_schedule(
    schedule_name => 'SCHEMA_STATS_SCHEDULE',
    start_date => systimestamp,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=30;'
  );

  dbms_scheduler.create_job(
    job_name => 'SCHEMA_STATS_JOB',
    program_name => 'SCHEMA_STATS_PROGRAM',
    schedule_name => 'SCHEMA_STATS_SCHEDULE',
    enabled => FALSE,
    auto_drop => FALSE
  );
  dbms_scheduler.enable('SCHEMA_STATS_JOB');
end;
/
```
この例は、スキーマ全体の統計情報を30分毎に取得するジョブを、名前付きプログラム、名前付きスケジュール指定で記述したものです。

job_action、repeat_intervalの指定を、それぞれCREATE_PROGRAM、CREATE_SCHEDULEプロシージャで定義し、CREATE_JOBプロシージャ実行時に、program_name属性、schedule_name属性で、それぞれの名前を指定しています。

repeat_intervalで、`FREQ=MINUTELY; INTERVAL=30;`とする事で、30分毎というスケジュール指定になります。

このようなジョブは、年度末や年度初めなど、スキーマ全体で大きなデータ更新が発生する特定のバッチ処理実行中にのみ有効化して実行させる事で、ジョブの実行時間が長くなったり、全体のパフォーマンスが悪化しないようにするための予防策になります。

#### 例3. Statspackのスナップショットを毎時15分に取得するジョブ(名前付きプログラム、スケジュール指定)
```plsql
begin
  dbms_scheduler.create_program(
    program_name => 'PERFSTAT.SNAP_PROGRAM',
    program_type => 'PLSQL_BLOCK',
    program_action => 'BEGIN' ||
      '  PERFSTAT.STATSPACK.PURGE(I_PURGE_BEFORE_DATE=>SYSDATE-14);' ||
      '  PERFSTAT.STATSPACK.SNAP(I_SNAP_LEVEL => 7);' ||
      'END;',
    enabled => FALSE
  ); 
  dbms_scheduler.enable('PERFSTAT.SNAP_PROGRAM');

  dbms_scheduler.create_schedule(
    schedule_name => 'PERFSTAT.SNAP_SCHEDULE',
    start_date => systimestamp,
    repeat_interval => 'FREQ=HOURLY;BYMINUTE=15'
  );

  dbms_scheduler.create_job(
    job_name => 'PERFSTAT.SNAP_JOB',
    program_name => 'PERFSTAT.SNAP_PROGRAM',
    schedule_name => 'PERFSTAT.SNAP_SCHEDULE',
    enabled => FALSE,
    auto_drop => FALSE
  );
  dbms_scheduler.enable('PERFSTAT.SNAP_JOB');
end;
/
```
Statspackのスナップショット取得を自動で行うようにするには、spauto.sqlを実行して設定するのが簡単ですが、spauto.sqlだと毎時0分にスナップショットが取得されます。

毎時0分にはデフォルトでAWRレポート用のスナップショットなども取得されるので、毎時0分以外のタイミングでスナップショットを取得したい場合があります。

そのような場合に、この例のようなジョブを登録する事で、好きな時間にスナップショットを取得する事ができます。

repeat_intervalで、`FREQ=HOURLY;BYMINUTE=15`とする事で、毎時15分というスケジュール指定になります。

#### ジョブ設定の変更
例1.の毎日AM5時10分にTEST_TABLEの統計情報を取得するジョブを、毎日0:08、0:58、2:53、5:43、21:53に実行するよう変更してみる事にします。

```plsql
-- 変更前
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'TABLE_STATS_JOB',
   job_type           =>  'PLSQL_BLOCK',
   job_action => 'BEGIN' ||
   ' dbms_stats.gather_table_stats(ownname => null, tabname => ''TEST_TABLE'', cascade => true, no_invalidate => false);' ||
   ' END;',
   start_date         => systimestamp,
   repeat_interval    => 'FREQ=DAILY;BYHOUR=5;byminute=10;bysecond=0',
   auto_drop          => FALSE,
   enabled            => TRUE);
END;
/
```
ジョブ設定の変更は、SET_ATTRIBUTEプロシージャで行います。
実行する処理の変更はなく、スケジュールの変更になるため、repeat_interval属性の指定値を更新します。

SET_ATTRIBUTEプロシージャでは、設定を変更するジョブ名、変更する属性、変更後の値を引数として渡して実行します。
毎日5時10分の指定を、毎日指定の時間に変更するため、FREQはDAILYのままにし、BYTIMEで実行時間を指定します。

以下を実行する事で、毎日0:08、0:58、2:53、5:43、21:53にジョブが実行されるように変更されます。
```plsql
exec DBMS_SCHEDULER.SET_ATTRIBUTE('TABLE_STATS_JOB', 'repeat_interval', 'FREQ=DAILY;BYTIME=000800,005800,025300,054300,215300');
```

#### ジョブの有効化・無効化、削除
引数に対象のジョブ名を指定して実行します。
```plsql
-- ジョブの有効化
exec DBMS_SCHEDULER.ENABLE('TABLE_STATS_JOB');

-- ジョブの無効化
exec DBMS_SCHEDULER.DISABLE('TABLE_STATS_JOB');

-- ジョブの削除
exec DBMS_SCHEDULER.DROP_JOB('TABLE_STATS_JOB');
```

#### 登録済みジョブの設定内容確認
ジョブ設定は、USER_SCHEDULER_JOBSデータディクショナリビューで確認できます。
```sql
select JOB_NAME,
       PROGRAM_NAME,
       SCHEDULE_NAME,
       SCHEDULE_TYPE,
       START_DATE,
       REPEAT_INTERVAL,
       END_DATE,
       ENABLED,
       STATE,
       RUN_COUNT,
       FAILURE_COUNT,
       LAST_START_DATE,
       LAST_RUN_DURATION,
       NEXT_RUN_DATE
  from USER_SCHEDULER_JOBS;
```
また、名前付きプログラム、スケジュール設定は以下で確認できます。
```sql
-- スケジュールの確認
select SCHEDULE_NAME,
       SCHEDULE_TYPE,
       START_DATE,
       REPEAT_INTERVAL
  from USER_SCHEDULER_SCHEDULES;

-- 実行プログラムの確認
select PROGRAM_NAME,
       PROGRAM_TYPE,
       PROGRAM_ACTION,
       ENABLED
  from USER_SCHEDULER_PROGRAMS;
```

#### ジョブの実行結果の確認
ジョブの実行結果、ジョブの実行時間は、USER_SCHEDULER_JOB_RUN_DETAILSデータディクショナリビューで確認できます。
```sql
select LOG_DATE,
       JOB_NAME,
       STATUS,
       ERROR#,
       RUN_DURATION
  from USER_SCHEDULER_JOB_RUN_DETAILS
order by LOG_DATE desc;
```

## おわりに
Oracleのジョブスケジューラー機能についてまとめてみました。

あまり知られていない機能かもしれませんが、システム運用上必要で、Oracle内で完結できる定期実行処理について、Oracleのジョブスケジューラー機能で実装してみるのも選択肢として持っておいてもよいように思います。

Oracleは、バッチ処理などによる大量データ更新があると、実データと統計情報との乖離によりパフォーマンス悪化する場合があり、そのような事象の対策としてこの機能を利用するのは個人的にはありだと考えています。