Oracle Database のメンテナンスタスクとスケジューラについて

こんにちは。 YDCの ザッキー です。
今回はOracle 12cの DBMS_AUTO_TASK_ADMIN と DBMS_SCHEDULER PL/SQLパッケージを使って
メンテナンスタスクとジョブ・スケジューラをカスタマイズする方法を紹介します。

1.自動化メンテナンス・タスクのカスタマイズ

自動化メンテナンス・タスクとは、データベースのメンテナンス操作を実行するために、
一定の間隔をおいて自動的に開始されるタスクです。
デフォルトでは以下の3種類の自動化メンテナンス・タスクが設定されています。

  • 自動オプティマイザ統計収集
  • データベース内に統計がないか、古い統計のみがあるすべてのスキーマ・オブジェクトに関する
    オプティマイザ統計を収集します。

  • 自動セグメント・アドバイザ
  • 再生可能な領域が存在しているセグメントを識別し、それらのセグメントの断片化を解消する
    方法について推奨事項を生成します。

  • 自動SQLチューニング・アドバイザ
  • 高負荷のSQL文のパフォーマンスを調査し、それらの文のチューニング方法について推奨事項を
    生成します。

    自動化メンテナンス・タスクはメンテナンス・ウィンドウで実行されます。
    管理者は、このメンテナンス・ウィンドウをカスタマイズすることによって使用環境に
    マッチした運用が行えます。

    自動化メンテナンス・タスクをカスタマイズするには、2つの方法があります。

    ① Oracle Enterprise Manager Cloud Control (Cloud Control)のGUIを使用する方法
    ② PL/SQLパッケージを使用する方法

    今回は、② PL/SQLパッケージを使用する方法 について紹介します。

    今回のデータベース環境です。

    データベース環境
    OS データベースバージョン 使用エディション 備考
    Red Hat EL 7.1 12.2.0.1.0 Enterprise Edition 64bit版

    まず、3種類の自動化メンテナンス・タスクを確認します。
    管理者権限でデータベースに接続し、下記のSQLでDBA_AUTOTASK_CLIENTビューを確認します。

    SQL> select CLIENT_NAME,STATUS,WINDOW_GROUP from DBA_AUTOTASK_CLIENT;

    CLIENT_NAME STATUS WINDOW_GROUP
    ---------------------------------------- -------- --------------------
    auto optimizer stats collection ENABLED ORA$AT_WGRP_OS
    auto space advisor ENABLED ORA$AT_WGRP_SA
    sql tuning advisor ENABLED ORA$AT_WGRP_SQ

    (1) メンテナンスタスクの無効化

    DBMS_AUTO_TASK_ADMIN PL/SQLパッケージを使用して有効(ENABLED)/無効(DISABLED)の変更が行えます。
    次の例は、自動SQLチューニング・アドバイザ(sql tuning advisor)の STATUSを無効にします。

    BEGIN
    DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'sql tuning advisor',
    operation => NULL,
    window_name => NULL);
    END;
    /

    PL/SQLプロシージャが正常に完了しました。

    変更後の確認は、前述の問合せで行えます。

    SQL> select CLIENT_NAME,STATUS,WINDOW_GROUP from DBA_AUTOTASK_CLIENT;

    CLIENT_NAME STATUS WINDOW_GROUP
    ---------------------------------------- -------- --------------------
    auto optimizer stats collection ENABLED ORA$AT_WGRP_OS
    auto space advisor ENABLED ORA$AT_WGRP_SA
    sql tuning advisor DISABLED ORA$AT_WGRP_SQ

    (2) メンテナンス・ウィンドウのカスタマイズ

    ここでは自動オプティマイザ統計収集(auto optimizer stats collection)が設定している WINDOW_GROUP の詳細を確認します。

    SQL> select * from DBA_SCHEDULER_WINDOW_GROUPS where WINDOW_GROUP_NAME = 'ORA$AT_WGRP_OS';

    WINDOW_GROUP_NAME ENABLE NUMBER_OF_WINDOWS NEXT_START_DATE COMMENTS
    -------------------- ------ ----------------- ---------------------------------------- ----------------------------------------
    ORA$AT_WGRP_OS TRUE 7 18-03-23 22:00:00.000000 ASIA/TOKYO auto optimizer stats collection

    自動オプティマイザ統計収集が、ウィンドウ・グループ:ORA$AT_WGRP_OSに設定されていて
    このタスク実行が有効であることが確認できました。

    ウィンドウ・グループ:ORA$AT_WGRP_OS に属するウィンドウメンバーについて確認します。

    SQL> select * from DBA_SCHEDULER_WINGROUP_MEMBERS where WINDOW_GROUP_NAME = 'ORA$AT_WGRP_OS';

    WWINDOW_GROUP_NAME WINDOW_NAME
    -------------------- ------------------------------
    ORA$AT_WGRP_OS MONDAY_WINDOW
    ORA$AT_WGRP_OS TUESDAY_WINDOW
    ORA$AT_WGRP_OS WEDNESDAY_WINDOW
    ORA$AT_WGRP_OS THURSDAY_WINDOW
    ORA$AT_WGRP_OS FRIDAY_WINDOW
    ORA$AT_WGRP_OS SATURDAY_WINDOW
    ORA$AT_WGRP_OS SUNDAY_WINDOW

    (2)-1. メンテナンスウィンドウの確認

    MAINTENANCE_WINDOW_GROUP に属するウィンドウを、各メンテナンス・タスクのステータス (有効 / 無効) とともに
    表示してみます。

    SQL> select * from DBA_AUTOTASK_WINDOW_CLIENTS;

    WINDOW_NAME WINDOW_NEXT_TIME WINDOW_ACTIVE AUTOTASK_STATUS OPTIMIZER_STATS SEGMENT_ADVISOR SQL_TUNE_ADVISOR HEALTH_MONITOR
    -------------------- ---------------------------------------- ------------- --------------- --------------- --------------- ---------------- --------------
    MONDAY_WINDOW 18-03-26 22:00:00.000000 ASIA/TOKYO FALSE ENABLED ENABLED ENABLED DISABLED DISABLED
    TUESDAY_WINDOW 18-03-27 22:00:00.000000 ASIA/TOKYO FALSE ENABLED ENABLED ENABLED DISABLED DISABLED
    WEDNESDAY_WINDOW 18-03-28 22:00:00.000000 ASIA/TOKYO FALSE ENABLED ENABLED ENABLED DISABLED DISABLED
    THURSDAY_WINDOW 18-03-29 22:00:00.000000 ASIA/TOKYO FALSE ENABLED ENABLED ENABLED DISABLED DISABLED
    FRIDAY_WINDOW 18-03-23 22:00:00.000000 ASIA/TOKYO FALSE ENABLED ENABLED ENABLED DISABLED DISABLED
    SATURDAY_WINDOW 18-03-24 06:00:00.000000 ASIA/TOKYO FALSE ENABLED ENABLED ENABLED DISABLED DISABLED
    SUNDAY_WINDOW 18-03-25 06:00:00.000000 ASIA/TOKYO FALSE ENABLED ENABLED ENABLED DISABLED DISABLED

    7行が選択されました。

    各カラムについて以下に説明します。

  • WINDOW_NEXT_TIME: 曜日ウィンドウの次回起動予定日時
  • WINDOW_ACTIVE : ウィンドウが現在Active(オープンしている)かどうかの表示(TRUE:Active / FALSE:Inactive)
  • AUTOTASK_STATUS : 自動化メンテナンス・タスク・サブシステム(全体)のStatus(ENABLED:有効 / DISABLED:無効)
  • OPTIMIZER_STATS : 自動オプティマイザ統計収集 タスクのStatus(ENABLED:有効 / DISABLED:無効)
  • SEGMENT_ADVISOR : 自動セグメント・アドバイザ タスクのStatus(ENABLED:有効 / DISABLED:無効)
  • SQL_TUNE_ADVISOR: 自動SQLチューニング・アドバイザ タスクのStatus(ENABLED:有効 / DISABLED:無効)
  • (2)-2. メンテナンスウィンドウの無効化

    この各ウィンドウは、運用するシステム要件によりカスタマイズが必要となります。
    次の例は、ウィンドウ:SUNDAY_WINDOW での自動オプティマイザ統計収集の実行を無効にします。

    BEGIN
    DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => 'SUNDAY_WINDOW');
    END;
    /

    PL/SQLプロシージャが正常に完了しました。

    (2)-3. メンテナンスウィンドウの開始時間と期間の変更

    DBMS_SCHEDULER PL/SQLパッケージを使用してメンテナンス・ウィンドウ属性の変更が行えます。
    次の例は、ウィンドウ:FRIDAY_WINDOW 開始時間を23時と期間を3時間に変更します。

    BEGIN
    DBMS_SCHEDULER.DISABLE(name => 'FRIDAY_WINDOW');
    DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'FRIDAY_WINDOW',
    attribute => 'DURATION',
    value => numtodsinterval(3, 'hour'));
    DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'FRIDAY_WINDOW',
    attribute => 'REPEAT_INTERVAL',
    value => 'freq=daily;byday=FRI;byhour=23;byminute=0; bysecond=0');
    DBMS_SCHEDULER.ENABLE(name => 'FRIDAY_WINDOW');
    END;
    /

    PL/SQLプロシージャが正常に完了しました。

    変更されたMAINTENANCE_WINDOW_GROUP 設定を確認します。

    SQL> select * from DBA_AUTOTASK_WINDOW_CLIENTS;

    WINDOW_NAME WINDOW_NEXT_TIME WINDOW_ACTIVE AUTOTASK_STATUS OPTIMIZER_STATS SEGMENT_ADVISOR SQL_TUNE_ADVISOR HEALTH_MONITOR
    -------------------- ---------------------------------------- ------------- --------------- --------------- --------------- ---------------- --------------
    MONDAY_WINDOW 18-03-26 22:00:00.000000 ASIA/TOKYO FALSE ENABLED ENABLED ENABLED DISABLED DISABLED
    TUESDAY_WINDOW 18-03-27 22:00:00.000000 ASIA/TOKYO FALSE ENABLED ENABLED ENABLED DISABLED DISABLED
    WEDNESDAY_WINDOW 18-03-28 22:00:00.000000 ASIA/TOKYO FALSE ENABLED ENABLED ENABLED DISABLED DISABLED
    THURSDAY_WINDOW 18-03-29 22:00:00.000000 ASIA/TOKYO FALSE ENABLED ENABLED ENABLED DISABLED DISABLED
    FRIDAY_WINDOW 18-03-23 23:00:00.000000 ASIA/TOKYO FALSE ENABLED ENABLED ENABLED DISABLED DISABLED
    SATURDAY_WINDOW 18-03-24 06:00:00.000000 ASIA/TOKYO FALSE ENABLED ENABLED ENABLED DISABLED DISABLED
    SUNDAY_WINDOW 18-03-25 06:00:00.000000 ASIA/TOKYO FALSE ENABLED DISABLED ENABLED DISABLED DISABLED

    7行が選択されました。

    変更されたFRIDAY_WINDOW 設定を確認します。

    SQL> select WINDOW_NAME,REPEAT_INTERVAL,DURATION,ENABLED,COMMENTS from DBA_SCHEDULER_WINDOWS where ENABLED = 'TRUE';

    WINDOW_NAME REPEAT_INTERVAL DURATION ENABLE COMMENTS
    -------------------- ------------------------------------------------------------ -------------------- ------ ----------------------------------------
    MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE Monday window for maintenance tasks
    TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE Tuesday window for maintenance tasks
    WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE Wednesday window for maintenance tasks
    THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE Thursday window for maintenance tasks
    FRIDAY_WINDOW freq=daily;byday=FRI;byhour=23;byminute=0; bysecond=0 +000 03:00:00 TRUE Friday window for maintenance tasks
    SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE Saturday window for maintenance tasks
    SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE Sunday window for maintenance tasks

    7行が選択されました。

    2.スケジューラを使用したカスタム・ジョブの作成

    カスタムジョブとスケジューラ設定をDBMS_SCHEDULER PL/SQLパッケージを使用して作成する例を紹介します。
    データベースのパフォーマンスやエラー解析のためにSTATSPACKを使用することがあります。
    次の例は、STATSPACK導入後に、自動でスナップショットを1時間毎(毎正時)に取得する設定で
    プログラム、スケジュール を作成してからジョブを作成します。

    BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name => 'PERFSTAT.STATSPACK_SNAP_PROGRAM',
    program_type => 'plsql_block',
    program_action => 'BEGIN STATSPACK.SNAP(i_snap_level=>7); END;',
    number_of_arguments => 0,
    enabled => true,
    comments => 'Statspack: Create Snapshots Program');
    DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name => 'PERFSTAT.STATSPACK_SNAP_SCHEDULE',
    start_date => (TRUNC(SYSDATE) + 0/1440),
    repeat_interval => 'FREQ=HOURLY; INTERVAL=1; BYMINUTE=0; BYSECOND=0',
    comments => 'Statspack: Create Snapshots Schedule');
    DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'PERFSTAT.STATSPACK_SNAP_JOB',
    program_name => 'PERFSTAT.STATSPACK_SNAP_PROGRAM',
    schedule_name => 'PERFSTAT.STATSPACK_SNAP_SCHEDULE',
    enabled => true,
    auto_drop => false,
    comments => 'Statspack: Create Snapshots Job');
    END;
    /

    PL/SQLプロシージャが正常に完了しました。

    次の例は、指定時刻(AM4:30)に自動で不要なスナップショット(90日以上経過)を削除するジョブを作成します。

    BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name => 'PERFSTAT.STATSPACK_PURGE_PROGRAM',
    program_type => 'plsql_block',
    program_action => 'BEGIN STATSPACK.PURGE(i_purge_before_date=>SYSDATE-90); END;',
    number_of_arguments => 0,
    enabled => true,
    comments => 'Statspack: Purge Snapshots Program');
    DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name => 'PERFSTAT.STATSPACK_PURGE_SCHEDULE',
    start_date => (TRUNC(SYSDATE) + 0/1440),
    repeat_interval => 'FREQ=DAILY; BYHOUR=4; BYMINUTE=30; BYSECOND=0',
    comments => 'Statspack: Purge Snapshots Schedule');
    DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'PERFSTAT.STATSPACK_PURGE_JOB',
    program_name => 'PERFSTAT.STATSPACK_PURGE_PROGRAM',
    schedule_name => 'PERFSTAT.STATSPACK_PURGE_SCHEDULE',
    enabled => true,
    auto_drop => false,
    comments => 'Statspack: Purge Snapshots Job');
    END;
    /

    PL/SQLプロシージャが正常に完了しました。


    このように、DBMS_AUTO_TASK_ADMIN と DBMS_SCHEDULER PL/SQLパッケージの
    使用方法について理解することで柔軟な運用設定が行えます。

    Standby Expressに関するお問合わせ

    • TEL 042-333-6217
    • FAX 042-352-6101
    • LINE
    • Mail