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

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

https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87__GUID-4629C0DE-5299-480A-BE4F-1C279B7E300F

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

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

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

## SQL計画ベースラインの作成方法
手動作成と自動作成の２通りの方法があります
### 手動作成方法
* DBMS_SPMパッケージを使用して実行計画を読み込みます
* 読み込み元によって以下の３つのファンクションが用意されています

  | ファンクション名 | 説明 |
  |:-|:-|
  | LOAD_PLANS_FROM_CURSOR_CACHE  | <ul><li>共有SQL領域(カーソル・キャッシュ)内の実行計画を読み込む</li><li>対象はSQL_IDやSQLテキストで指定する</li></ul>|
  | LOAD_PLANS_FROM_SQLSET  | <ul><li>SQLチューニングセット(STS)に保存されている実行計画を読み込む</li><li>対象はSTS名で指定する</li><li>フィルターで絞り込むことも可能</li></ul>|
  | LOAD_PLANS_FROM_AWR  | <ul><li>AWRから実行計画を読み込む</li><li>対象はスナップIDで指定する</li><li>フィルターで絞り込むことも可能</li></ul> |
  * 読み込み例
    ```sql:カーソルキャッシュから読み込み例
    VARIABLE v_plan_cnt NUMBER
    BEGIN
      :v_plan_cnt:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '27m0sdw9snw59');
    END;
    ```
    ```sql: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*/%''' );
    ```
    ```sql: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展開アドバイザを内部的に実行）をして、最適なベースラインを作成してくれます
```sql:実行例
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)を無駄にするおそれあり
* 構文
  ```sql
  DBMS_SPM.CONFIGURE (
    parameter_name    IN VARCHAR2,
    parameter_value   IN VARCHAR2 := NULL,
    allow             IN BOOLEAN := TRUE);
  ```
* フィルタ関連のパラメータ
  | パラメータ | 説明 |
  |:-|:-|
  | parameter_name  | <dl><dt>auto_capture_sql_text</dt><dd>発行したSQL文字列</dd><dt>auto_capture_parsing_schema_name</dt><dd>SQLが解析されたスキーマ名</dd><dt>auto_capture_module</dt><dd>SQLを発行したプログラムのMODULE名</dd></dl>|
  | parameter_value  | 対象条件（SQLやスキーマ名など）|
  | allow | <dl><dt>TRUE</dt><dd>parameter_valueを含める</dd><dt>FALSE</dt><dd>parameter_valueを含めない</dd></dl>※parameter_nameが「auto_capture_sql_text」の場合はLIKE検索 |  
  * 設定例
    ```sql:「select a」で始まるSQLを対象にする
    EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT', 'select a%', 'TRUE');
    ```
    ```sql:SCOTTスキーマを対象外にする
    EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', 'SCOTT', 'FALSE');
    ```
  * マニュアル
  [PL/SQLパッケージおよびタイプ・リファレンス](https://docs.oracle.com/cd/F19136_01/arpls/DBMS_SPM.html#GUID-D855D4B9-D071-4BB2-A554-38479867DE76)
* 未使用の計画は、保存期間（デフォルトは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を検出する　**←　ここが追加**
1. カーソルキャッシュやAWR、STSから特定されたSQLの代替計画を検索し、未承認の計画として追加する　**←　ここが追加**
1. 未承認の計画がテストされ、どの計画がベストか判断される
1. 既存の計画よりパフォーマンスが良いことが判明した計画は、SQL計画ベースラインに追加される

## 設定方法
* SPM展開アドバイザの下記の２つのパラメータ値を「AUTO」に設定します
SQL計画ベースラインがないSQLでも、パフォーマンスが低下した上位SQLが対象になります
  | パラメータ | 説明 |
  |:-|:-|
  | ALTERNATE_PLAN_BASELINE  | ロードする代替計画の対象を決定します<ul><li>EXISTING：ベースラインのあるSQLが対象（デフォルト）</li><li>NEW：ベースラインのないSQLが対象</li><li>AUTO：自動判断</li></ul>|
  | ALTERNATE_PLAN_SOURCE  | 代替計画を検索するソースを決定します<ul><li>AUTO：自動判断</li><li>AUTOMATIC_WORKLOAD_REPOSITORY</li><li>CURSOR_CACHE</li><li>SQL_TUNING_SET</li></ul>※複数の値をプラス記号(+)で結合できます|

* 設定例
  ```sql
  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にすると、１時間ごとに実行されます（１回の最大実行時間は３０分）
* 設定例
  ```sql
  exec DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK', 'ON');
  ```