Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@fujid(Fujihashi)inTIS株式会社

Oracle Database 自動SQL計画管理（SPM)

Last updated at Posted at 2024-03-17

はじめに

Oracle DatabaseのSQL計画管理（SPM）と自動SQL計画管理について記載します。
自動SQL計画管理はこれまでExadataかAutonomous Databaseのみで利用できる機能でしたが、Oracle Database 23cと19c(RU 19.22以降)のEnterpries Editionで利用できるようになりました。

※「Automatic SQL Plan Management」の欄を参照

SQL計画管理（SPM）とは

概要

  • SQLの実行計画変化によるパフォーマンス劣化を防ぐために、実行計画を管理することができます
  • 利用可能な実行計画である「SQL計画ベースライン」を作成することができます
  • ベースライン作成後は、SQL実行時に新しい実行計画が作成された場合も、ベースラインの実行計画が使用されます
  • SQL実行時に新しく作成された実行計画は、未承認の実行計画としてSQL計画履歴に追加されます
  • 「SPM展開アドバイザ」タスクという自動メンテナンスタスクにより、未承認の実行計画が既存の実行計画より性能が良い場合、自動的に承認してベースラインに追加することができます（※12c以降）
  • SQL実行時には、ベースラインの中で、最もコストの低い実行計画が選択されます（選択する実行計画を固定することもできます）

Standard Edition(SE)でも利用できますが、SEの場合は１つのベースラインしか作成できません

SQL計画ベースラインの作成方法

手動作成と自動作成の２通りの方法があります

手動作成方法

  • DBMS_SPMパッケージを使用して実行計画を読み込みます

  • 読み込み元によって以下の３つのファンクションが用意されています

    ファンクション名 説明
    LOAD_PLANS_FROM_CURSOR_CACHE
    • 共有SQL領域(カーソル・キャッシュ)内の実行計画を読み込む
    • 対象はSQL_IDやSQLテキストで指定する
    LOAD_PLANS_FROM_SQLSET
    • SQLチューニングセット(STS)に保存されている実行計画を読み込む
    • 対象はSTS名で指定する
    • フィルターで絞り込むことも可能
    LOAD_PLANS_FROM_AWR
    • AWRから実行計画を読み込む
    • 対象はスナップIDで指定する
    • フィルターで絞り込むことも可能
    • 読み込み例
      カーソルキャッシュから読み込み例
      VARIABLE v_plan_cnt NUMBER
BEGIN
  :v_plan_cnt:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '27m0sdw9snw59');
END;
      SQLチューニングセットから読み込み例
      VARIABLE v_plan_cnt NUMBER
EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
           sqlset_name  => 'SPM_STS', -
           basic_filter => 'sql_text like ''SELECT /*LOAD_STS*/%''' );
      AWRから読み込み例
      VARIABLE v_plan_cnt NUMBER
EXEC :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_AWR(begin_snap => 212, end_snap =>  213);
      ※ スナップIDに加えてbasic_filterパラメータを追加することで、対象の実行計画を絞り込むこともできます

※2024/3/24追記
「ADD_VERIFIED_SQL_PLAN_BASELINE」ファンクションが追加されていました
カーソルキャッシュ、自動SQLチューニング・セット、AWRから実行計画を見つけ、テスト（SPM展開アドバイザを内部的に実行）をして、最適なベースラインを作成してくれます

実行例
var report clob
exec :report := DBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINE('27m0sdw9snw59');

自動作成方法

  • 初期化パラメーター「OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES」を「TRUE」に設定することで、複数回実行されたSQLの最初の実行計画を承認済みのベースラインとして追加することができます
    ※デフォルト値はFALSEです
  • SQL文は「SQLシグネチャ」を使用して特定するため、大/小文字や空白数が異なっても同じSQLとして扱います
  • DBMS_SPM.CONFIGUREプロシージャを使用することで、対象のSQLをフィルタすることができます（12cR2から）
    ※フィルタしないと重要ではないSQLで格納先(SYSAUX)を無駄にするおそれあり
  • 構文 
    DBMS_SPM.CONFIGURE (
  parameter_name    IN VARCHAR2,
  parameter_value   IN VARCHAR2 := NULL,
  allow             IN BOOLEAN := TRUE);
  • フィルタ関連のパラメータ
    パラメータ 説明
    parameter_name
    auto_capture_sql_text
    発行したSQL文字列
    auto_capture_parsing_schema_name
    SQLが解析されたスキーマ名
    auto_capture_module
    SQLを発行したプログラムのMODULE名
    parameter_value 対象条件（SQLやスキーマ名など）
    allow
    TRUE
    parameter_valueを含める
    FALSE
    parameter_valueを含めない
    ※parameter_nameが「auto_capture_sql_text」の場合はLIKE検索
    • 設定例
      「select a」で始まるSQLを対象にする
      EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT', 'select a%', 'TRUE');
      SCOTTスキーマを対象外にする
      EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', 'SCOTT', 'FALSE');
    • マニュアル
      PL/SQLパッケージおよびタイプ・リファレンス
  • 未使用の計画は、保存期間（デフォルトは53週）が過ぎると自動的に削除されます
    (DBMS_SPM.CONFIGUREプロシージャのPLAN_RETENTION_WEEKSパラメータで設定可能)

自動SQL計画管理

概要

  • SQL計画ベースラインの作成は、「手動」は負荷が高く、「自動」は不要なものまで取得されるという問題があったため、19cから「SPM展開アドバイザ」の機能拡張として追加された機能です
  • 19cの「SPM展開アドバイザ」ではパフォーマンスに問題のあるSQLを自動検出し、アドバイザのテスト対象に追加できるようになりました
  • 当初はExadata、Autonomous Databaseのみで利用できる機能でしたが、23cと19c(RU 19.22以降)のEnterpries Editionで利用できるようになりました

拡張された「SPM展開アドバイザ」の流れ

  1. AWRまたはSTSからパフォーマンス低下の上位SQLを検出する　←　ここが追加
  2. カーソルキャッシュやAWR、STSから特定されたSQLの代替計画を検索し、未承認の計画として追加する　←　ここが追加
  3. 未承認の計画がテストされ、どの計画がベストか判断される
  4. 既存の計画よりパフォーマンスが良いことが判明した計画は、SQL計画ベースラインに追加される

設定方法

  • SPM展開アドバイザの下記の２つのパラメータ値を「AUTO」に設定します
    SQL計画ベースラインがないSQLでも、パフォーマンスが低下した上位SQLが対象になります

    パラメータ 説明
    ALTERNATE_PLAN_BASELINE ロードする代替計画の対象を決定します
    • EXISTING：ベースラインのあるSQLが対象（デフォルト）
    • NEW：ベースラインのないSQLが対象
    • AUTO：自動判断
    ALTERNATE_PLAN_SOURCE 代替計画を検索するソースを決定します
    • AUTO：自動判断
    • AUTOMATIC_WORKLOAD_REPOSITORY
    • CURSOR_CACHE
    • SQL_TUNING_SET
    ※複数の値をプラス記号(+)で結合できます

  • 設定例

    BEGIN
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER (task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
                                       parameter => 'ALTERNATE_PLAN_BASELINE',
                                       value => 'AUTO');
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER (task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
                                       parameter => 'ALTERNATE_PLAN_SOURCE',
                                       value => 'AUTO');
END;

高頻度自動SPM展開アドバイザ

概要

  • 標準ではSPM展開アドバイザは自動メンテナンスタスクのメンテナンスウインドウ内で１日１回実行されますが、データが頻繁に変更される場合は１日１回では足りない場合があります
  • 19cから「AUTO_SPM_EVOLVE_TASK」パラメータをONにすることで、メンテナンス・ウィンドウ以外でも自動的に実行できるようになります（デフォルトはOFF）
  • ONにすると、１時間ごとに実行されます（１回の最大実行時間は３０分）
  • 設定例 
    exec DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK', 'ON');
1
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?