はじめに

データベースを使用していると、統計情報の更新やデータベースのパッチ適用、システム構成の変更などにより、SQL の実行計画が変化することがあります。実行計画の変更は、その多くが良い方向に働くこともありますが、時には最適な実行計画から逸脱してパフォーマンス問題を引き起こすこともあります。こうした問題に対する手段として Oracle Database には「SQL Plan Management (SPM)」機能があります。そこでこの記事では、SPM の仕組みや設定方法、検証結果について紹介します。

※ この記事では Oracle Cloud Infrastructure の Oracle Base Database Service (BaseDB) 上で利用可能な Oracle Database 19c Enterprise Edition を使用しています。環境やバージョンによりライセンス情報や設定値のデフォルト値が異なる場合があります。

SQL Plan Management

SQL Plan Management (SPM) は SQL の実行計画を管理して安定性と最適化を保証するための機能です。意図せず SQL の実行計画が変わる可能性がある場合、SPM を適切に導入・運用することで、実行計画の変更によるパフォーマンス劣化リスクを最小限に抑えることができます。SPM は全てのエディションで利用は可能ですが、エディションごとに使用できる機能に制限があるため、使用前には必ず公式ドキュメント (Database / Oracle / Oracle Database / Release 19 - Database Licensing Information User Manual - 1 Licensing Information ⧉) をご確認ください。

SPM の仕組み

SQL が実行された際にオプティマイザが生成した実行計画を「ベースライン」として登録することで、以降同一の SQL が実行される際に、オプティマイザは登録されたベースラインを優先して使用するようになります。これにより意図しない実行計画の変更を防ぎ、パフォーマンスの安定化を図ります。

登録した実行計画の情報は、SQL 管理ベース (SQL Management Base: SMB) に保管されます。SMB は SYSAUX 表領域内に存在する SPM 用のリポジトリで、ベースラインだけではなく SQL テキストやバインディング変数、その他の関連情報も保存されています。

ベースラインの登録

SPM には、以下の 2 つの方法でベースラインを登録できます。

自動キャプチャ

一部例外はありますが、繰り返し実行が可能な SQL など、SPM の取得基準を満たした SQL の実行計画は、自動でベースラインとして登録されます。SPM の管理対象から除外したい SQL が存在する場合や特定の SQL のみを SPM で管理したい場合は、フィルタを設定します。

手動ロード

カーソルキャッシュや Automatic Workload Repository (AWR) に格納されている既存の実行計画を、DBMS_SPM パッケージを使用して手動でベースラインとして登録することも可能です。特に本番環境で SPM を導入する際、重要な SQL や過去にパフォーマンス問題が発生した実行計画を事前にロードする用途で利用します。

ベースラインの種類

ベースラインには大きく分けて「未承認のベースライン」と「承認済みのベースライン」、「固定計画」があります。

未承認のベースライン

SQL の実行時にオプティマイザが直接選択して使用しないベースラインです。自動キャプチャの場合は、すでにベースラインが存在する SQL に新しい実行計画が追加される際、「未承認のベースライン」として登録されます。後述する「実行計画の展開 (進化)」により、「承認済みのベースライン」になる場合があるほか、DBMS_SPM パッケージを使用して手動でも「承認済みのベースライン」にすることが可能です。

承認済みのベースライン

SQL の実行時にオプティマイザが優先的に選択して使用するベースラインです。一つの SQL に対して複数の「承認済みのベースライン」が存在する場合、オプティマイザはその中から最もコストの低いものを選択します。

固定計画

「承認済みのベースライン」のうち、FIXED の設定がされているベースラインです。DBMS_SPM パッケージを使用して手動で「固定計画」を設定します。実行する SQL に「固定計画」がある場合、オプティマイザはそのベースラインのみを使用します。

実行計画の選択方法

SPM が有効な環境では、実行計画は以下のフローで選択されます。

1. SQL を実行すると、オプティマイザが実行計画を生成します
   • 現在のデータベース環境 (統計情報、初期化パラメータなど) に基づいて生成
2. SPM は SMB を参照し、この SQL に対応する「承認済みのベースライン」が存在するか確認します
   • A. 「承認済みのベースライン」が SMB に存在しない場合
     1. 手順 1. で生成した実行計画を使用します
        • 自動キャプチャが有効な場合、この実行計画を「承認済みのベースライン」として登録
   • B. 「承認済みのベースライン」が SMB に存在する場合
     • a. 「固定計画」がある場合
       1. 「固定計画」を使用します
     • b. 「固定計画」がない場合
       1. 登録されている「承認済みのベースライン」の中から、最もコストの低い「承認済みのベースライン」を使用します
     • 自動キャプチャが有効で、手順 1. で生成した実行計画が SMB にない場合、その実行計画を「未承認のベースライン」として登録

実行計画の展開 (進化)

SPM では、より良い実行計画が生成される場合を考慮して、「未承認のベースライン」のパフォーマンスを検証します。検証した「未承認のベースライン」が既存の「承認済みのベースライン」よりも優れていた場合は、「承認済みのベースライン」となります。検証は通常、データベースに設定したメンテナンス・ウィンドウの期間に自動実行される「SPM 展開アドバイザ」のタスクで実施されますが、DBMS_SPM パッケージを使用して手動でも実行が可能です。

DBMS_SPM パッケージと実行に必要な権限

SYS が所有する DBMS_SPM パッケージにあるファンクションなどを使用して、SPM の構成オプションやベースラインを操作することができます。中でも使用頻度の高いものについて説明します。

• CONFIGURE プロシージャ
  • ベースラインの保管期間や SMB の容量、自動キャプチャ用のフィルタ設定など、SPM の構成オプションを設定可能
• LOAD_PLANS_FROM_CURSOR_CACHE ファンクション
  • カーソルキャッシュに存在する実行計画を、SQL ID 等を指定してベースラインとして登録
• ALTER_SQL_PLAN_BASELINE ファンクション
  • 特定のベースラインに対して、固定や承認を手動で変更可能
• DROP_SQL_PLAN_BASELINE ファンクション
  • 登録されているベースラインを削除
  • 削除はベースライン単位か SQL 単位のどちらか
    • 全ての SQL のベースライン一括削除はできないため、専用の処理を組む必要がある

そのほかについては、公式ドキュメント (Database / Oracle / Oracle Database / Release 19 - PL/SQL Packages and Types Reference - 163 DBMS_SPM ⧉) をご確認ください。実行については、ドキュメントでは DBMS_SPM パッケージに対する EXECUTE 権限か ADMINISTER SQL MANAGEMENT OBJECT システム権限が必要と記述がありますが、この記事の執筆時は、DBMS_SPM パッケージの EXECUTE 権限付与だけでは実行できませんでした。

SPM の設定と無効化

SPM の動作は、初期化パラメータと DBMS_SPM パッケージの CONFIGURE プロシージャで制御することができます。

関連する初期化パラメータ

• OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE
  • SPM でベースラインの自動キャプチャを有効にするか設定するパラメータ
  • デフォルトでは FALSE (無効)
• OPTIMIZER_USE_SQL_PLAN_BASELINE
  • SQL 実行時にオプティマイザが SMB のベースラインを実行計画の選択時に利用するか設定するパラメータ
  • デフォルトでは TRUE (有効)

初期化パラメータの変更方法

二つの初期化パラメータは、ISPDB_MODIFIABLE が TRUE であるため、CDB と PDB で異なる値を設定することが可能です。また ISSYS_MODIFIABLE が IMMEDIATE であるため、設定変更の反映にセッションの張り直しやデータベースの再起動は必要ありません。初期化パラメータを更新するためには、ALTER SYSTEM 権限が必要です。

SPM の構成オプション

DBMS_SPM.CONFIGURE プロシージャを使用することで、ベースラインの保管期間や SMB の容量、自動キャプチャ用のフィルタ設定などを変更できます。

ベースラインの保管期間

ベースラインは PLAN_RETENTION_WEEKS で設定された期間 (週) だけ保管されます。保管期限を過ぎた場合、ベースラインは自動的に削除されます。設定可能な値は 5～523 で、デフォルトは 53 (週) です。

ベースラインの保管容量

ベースラインなどを保管する SMB の容量は、SYSAUX 表領域に占める SMB の割合 (パーセント) として、SPACE_BUDGET_PERCENT で設定します。設定可能な値は 1～50 で、デフォルトは 10 (%) です。設定した値を超えると、アラートログに以下のようなログが出力されますが、ベースライン等は自動削除されません。

1
SPM: SMB space usage (<SMB のサイズ>) exceeds <SYSAUX 表領域に占める SMB の割合>% of SYSAUX size (<SYSAUX 表領域のサイズ>)

ベースラインの自動キャプチャ設定

SPM に登録されるベースラインを自動取得するためのフィルタを設定できます。設定可能なフィルタは「スキーマ」、「SQL テキスト」、「アクション」、「モジュール」の 4 つです。各フィルタは同時に設定が可能です。また、一つのフィルタについて複数の条件を指定することが可能です。どのフィルタの条件もインクルード、エクスクルードの指定ができます。デフォルトではどのフィルタも設定されていないため、取得可能なすべての実行計画が SPM で自動キャプチャされます。そのため、SPM 利用時に自動キャプチャを制限する場合は、自動キャプチャを有効化する (OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE = TRUE) 前にフィルタを設定します。

SPM の無効化

以下の手順を実施して、SPM を無効化します。

1. OPTIMIZER_USE_SQL_PLAN_BASELINES を FALSE に変更して、オプティマイザのベースライン使用を停止します
2. OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES を FALSE に変更して、自動キャプチャを停止します
3. キャプチャしたベースラインが不要な場合、DBMS_SPM.DROP_SQL_PLAN_BASELINE ファンクションを使用してベースラインを削除します

SPM の検証

SPM の検証として、実際に自動キャプチャを有効化して動作を確認しました。以下の手順は PDB の SYSDBA 権限を持つ SYS で実施しています。

SPM の有効化前の確認

• 初期化パラメータを確認

  • optimizer_capture_sql_plan_baselines が FALSE (自動キャプチャ: 無効) であることを確認
  • optimizer_use_sql_plan_baselines が TRUE (ベースライン利用: 有効) であることを確認

  1
  SHOW PARAMETER SQL_PLAN_BASELINE;

  1
  NAME                                 TYPE    VALUE
  2
  ------------------------------------ ------- -----
  3
  optimizer_capture_sql_plan_baselines boolean FALSE
  4
  optimizer_use_sql_plan_baselines     boolean TRUE

• SYSAUX 表領域内の SMB 使用量と SYSAUX 表領域の使用率を確認

  • SMB_USAGE_MB: SMB の使用量 (MB)
  • SYSAUX_SIZE_MB: SYSAUX 表領域のサイズ (MB)
  • SPACE_USAGE_PERCENT: SYSAUX 表領域における SMB の使用率 (%)

  1
  WITH
  2
    SYSAUX_INFO AS (
  3
      SELECT
  4
        (SUM(BYTES) / 1024 / 1024) AS SYSAUX_SIZE_MB
  5
      FROM
  6
        DBA_DATA_FILES
  7
      WHERE
  8
        TABLESPACE_NAME = 'SYSAUX'
  9
    )
  10
  SELECT
  11
    OCCUPANT_NAME
  12
    , (SPACE_USAGE_KBYTES / 1024) AS SMB_USAGE_MB
  13
    , SYSAUX_SIZE_MB
  14
    , ROUND(
  15
        ((SPACE_USAGE_KBYTES / 1024) / SYSAUX_SIZE_MB) * 100
  16
        , 2
  17
      ) AS SPACE_USAGE_PERCENT
  18
  FROM
  19
    V$SYSAUX_OCCUPANTS
  20
    , SYSAUX_INFO
  21
  WHERE
  22
    OCCUPANT_NAME = 'SQL_MANAGEMENT_BASE'
  23
  ;

  1
  OCCUPANT_NAME       SMB_USAGE_MB SYSAUX_SIZE_MB SPACE_USAGE_PERCENT
  2
  ------------------- ------------ -------------- -------------------
  3
  SQL_MANAGEMENT_BASE        1.125           1500                0.08

• SPM の構成オプションを確認

  1
  SELECT
  2
    *
  3
  FROM
  4
    DBA_SQL_MANAGEMENT_CONFIG
  5
  ORDER BY
  6
    PARAMETER_NAME
  7
  ;

• 登録されているベースラインを確認

  1
  SELECT
  2
    *
  3
  FROM
  4
    DBA_SQL_PLAN_BASELINES
  5
  ;

SPM の有効化

1. 必要に応じて、SPM の構成オプションからフィルタを設定する

   • 今回の検証ではベースラインに登録される様子を確認するため、フィルタは未設定

   • SQL 中の改行は、SQL テキストでは半角スペースとして扱われる場合があるため、SQL テキストによる自動キャプチャのフィルタを利用する場合は、以下を考慮したフィルタ文字列を登録する必要がある点に注意

     1
     -- フィルタしたい SQL (改行有り)
     2
     SELECT
     3
       *
     4
     FROM
     5
       MYTABLE1
     6
     ;
     7
     -- 内部で保存されている SQL テキスト
     8
     SELECT   * FROM   MYTABLE1 ;

2. 初期化パラメータを設定する

   1
   ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE SCOPE = BOTH;

   1
   System Altered.

3. 変更後の初期化パラメータを確認する

   • optimizer_capture_sql_plan_baselines が TRUE (自動キャプチャ: 有効) であることを確認
   • optimizer_use_sql_plan_baselines が TRUE (ベースライン利用: 有効) であることを確認

   1
   NAME                                 TYPE    VALUE
   2
   ------------------------------------ ------- -----
   3
   optimizer_capture_sql_plan_baselines boolean TRUE
   4
   optimizer_use_sql_plan_baselines     boolean TRUE

登録されているベースラインの確認

1. 登録されているベースラインを確認する

   • 検証では SYS が実行している SQL が登録されていた

2. DBA_SQL_PLAN_BASELINES の PARSING_SCHEMA_NAME ごとに登録件数を確認する

   1
   SELECT
   2
     PARSING_SCHEMA_NAME
   3
     , COUNT(PARSING_SCHEMA_NAME)
   4
   FROM
   5
     DBA_SQL_PLAN_BASELINES
   6
   GROUP BY
   7
     PARSING_SCHEMA_NAME
   8
   ORDER BY
   9
     PARSING_SCHEMA_NAME
   10
   ;

   1
   PARSING_SCHEMA_NAME COUNT(PARSING_SCHEMA_NAME)
   2
   ------------------- --------------------------
   3
   SYS                                        78

3. SYSAUX 表領域内の SMB 使用量と SYSAUX 表領域の使用率を確認する

   1
   OCCUPANT_NAME       SMB_USAGE_MB SYSAUX_SIZE_MB SPACE_USAGE_PERCENT
   2
   ------------------- ------------ -------------- -------------------
   3
   SQL_MANAGEMENT_BASE      14.5625           1530                0.95

SPM の無効化とベースラインの削除

1. 初期化パラメータを設定する

   1
   ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE SCOPE=BOTH;

   1
   System Altered.

2. 変更後の初期化パラメータを確認する

   • optimizer_capture_sql_plan_baselines が FALSE (自動キャプチャ: 無効) であることを確認
   • optimizer_use_sql_plan_baselines が TRUE (ベースライン利用: 有効) であることを確認

   1
   NAME                                 TYPE    VALUE
   2
   ------------------------------------ ------- -----
   3
   optimizer_capture_sql_plan_baselines boolean FALSE
   4
   optimizer_use_sql_plan_baselines     boolean TRUE

3. 登録されている全てのベースラインを削除する

   1
   SET SERVEROUTPUT ON;
   2
   DECLARE
   3
     drop_baseline_counter NUMBER := 0;
   4
   BEGIN
   5
     FOR rec IN (
   6
       SELECT
   7
         sql_handle
   8
         , plan_name
   9
       FROM
   10
         DBA_SQL_PLAN_BASELINES
   11
     ) LOOP
   12
       drop_baseline_counter := drop_baseline_counter + DBMS_SPM.DROP_SQL_PLAN_BASELINE(
   13
         sql_handle => rec.sql_handle,
   14
         plan_name  => rec.plan_name
   15
       );
   16
     END LOOP;
   17
     DBMS_OUTPUT.PUT_LINE('removed baseline:' || drop_baseline_counter);
   18
   END;
   19
   /

   1
   removed baseline: 78

4. ベースラインが削除されたことを確認する

おわりに

SQL Plan Management (SPM) は SQL の実行計画の予期せぬ変更からシステムを守り、安定したパフォーマンスを維持するための重要な機能です。この記事では Oracle Database 19c 環境における SPM の概要と設定方法、構成オプションを確認しました。ベースラインの自動キャプチャは非常に便利な機能ですが、フィルタを設けない場合は不要なベースラインも取得するため、SYSAUX 表領域を逼迫する可能性もあります。本番環境での SPM 導入は慎重に進め、まずは検証環境で挙動を確認することを推奨します。

SPM の適切な設定と継続的な監視は、データベースの安定稼働と性能維持に大きく貢献するため、ぜひ検討してみてください。