SQL Plan Management（SPM）は、実行計画のロールバックを防ぐ仕組みであり、新規に生成された実行計画が検証された後にのみ使用されるようにすることで、計画のパフォーマンスが継続的に最適化および更新されることを保証します。
機能の適用範囲
現在、OceanBase データベース Community Edition は SPM 機能をサポートしていません。
OceanBase データベースはオンライン SPM 進化メカニズムをサポートしています。つまり、新規に生成された実行計画がベースラインに含まれていない場合、自動的に進化タスクが開始され、ユーザーの手動介入なしに計画の進化が自動的に完了します。
OceanBase データベースの SPM は、DBA_SQL_PLAN_BASELINES および DBA_SQL_MANAGEMENT_CONFIG ビュー、および DBMS_SPM システムパッケージを使用して実行計画を管理します。
SPMの実行メカニズム
SPMはSQL Plan Baselineに基づいて実装されており、SQL Plan Baselineは実行計画のベースラインであり、検証済みの実行計画情報（アウトラインデータなどの情報）を永続化して保存するために使用されます。各実行計画にはPlan Baselineが対応し、このPlan Baselineによって実行計画を再現することができます。
OceanBaseデータベースにおける計画の進化は常に計画生成によってトリガーされます。SPMの実行メカニズムは以下のように要約できます：
- SQLが初めて生成した計画は、デフォルトでベースラインとして扱われ、
ACCEPTされます。 - 新しい計画が計画ベースラインに存在し、かつベースラインが
FIXEDである場合、または他のFIXED計画ベースラインが存在しない場合、現在の計画を直接使用します。 - 再現可能な
FIXEDベースラインが存在する場合、常にFIXEDベースライン計画を優先的に使用し、計画の進化は行いません。 - 再現可能な
FIXEDベースラインが存在しないが、再現可能なACCEPTEDベースライン計画が存在する場合、新しい計画とベースライン計画を進化させます。 - 再現可能なベースライン計画が存在しない場合、新しく生成された計画を直接使用します。
SPMの制限事項
OceanBaseデータベースのSPMには、以下の制限事項があります：
- バックアップ・リストアの復元状態にあるテナントおよびプライマリ/スタンバイクラスタのスタンバイ機では、計画の進化を実行できません。
- システムテナント配下のSQLおよびInner SQLは、計画の進化を行えません。
- SQL文に
INSERT INTO VALUESが含まれる場合、計画の進化は行いません。 - 進化の結果は一時的にOBServerノードのLocal Cacheに保存され、定期的にInner Tableに同期されるため、任意のOBServerノード上の進化結果は他のOBServerノードからは即座に認識できません。
SPM関連ビュー
DBA_SQL_PLAN_BASELINES
DBA_SQL_PLAN_BASELINESビューは、SPM内のSQLの計画ベースラインを記録しています。このビューの各フィールドの意味は以下の表のとおりです。
フィールド名 |
データ型 |
NULL許容 |
説明 |
|---|---|---|---|
| SIGNATURE | VARCHAR2(128) | NOT NULL | 正規化されたSQLによって生成されるSQLの一意の識別子（このフィールドの型はOracleと互換性がありません）。 |
| SQL_HANDLE | VARCHAR2(128) | NOT NULL | SQLを表す一意のテキスト形式。 |
| SQL_TEXT | CLOB | NOT NULL | 正規化されていないSQLテキスト。 |
| PLAN_NAME | VARCHAR2(128) | NOT NULL | 計画の一意のテキスト形式の識別子。 |
| CREATOR | VARCHAR2(128) | この計画ベースラインを作成したユーザー名。 | |
| ORIGIN | VARCHAR2(14) | 計画ベースラインの作成方法。例：
|
|
| PARSING_SCHEMA_NAME | VARCHAR2(128) | 常にNULLです。 | |
| DESCRIPTION | VARCHAR2(500) | 計画ベースラインの説明。 | |
| VERSION | VARCHAR2(64) | 計画ベースライン作成時のデータベースバージョン。 | |
| CREATED | TIMESTAMP(6) | NOT NULL | 計画ベースラインの作成日時。 |
| LAST_MODIFIED | TIMESTAMP(6) | 計画ベースラインの最終変更日時。 | |
| LAST_EXECUTED | TIMESTAMP(6) | 計画ベースラインの最終実行日時。 | |
| LAST_VERIFIED | TIMESTAMP(6) | 計画ベースラインの最終検証日時。 | |
| ENABLED | VARCHAR2(3) | 計画ベースラインが有効('YES')か無効('NO')か。 | |
| ACCEPTED | VARCHAR2(3) | 計画ベースラインが採用された('YES')か採用されていない('NO')か。 | |
| FIXED | VARCHAR2(3) | 計画ベースラインが固定('YES')か非固定('NO')か。 | |
| REPRODUCED | VARCHAR2(3) | 計画ベースラインが再現可能('YES')か再現不可能('NO')か。 | |
| AUTOPURGE | VARCHAR2(3) | 計画ベースラインが自動クリーンアップされる('YES')か非自動クリーンアップされる('NO')か。 | |
| ADAPTIVE | VARCHAR2(3) | SPMによって自動的にキャプチャされた計画ベースラインが既に適応済みかどうか。 | |
| OPTIMIZER_COST | NUMBER | 計画ベースライン作成時のオプティマイザーによる計画コストの推定値。 | |
| MODULE | VARCHAR2(64) | 常にNULLです。 | |
| ACTION | VARCHAR2(64) | 常にNULLです。 | |
| EXECUTIONS | NUMBER | プランベースライン作成時のプラン実行回数。 | |
| ELAPSED_TIME | NUMBER | プランベースライン作成時のプラン総実行時間。 | |
| CPU_TIME | NUMBER | プランベースライン作成時のプラン総CPU時間。 | |
| BUFFER_GETS | NUMBER | 常にNULLです。 | |
| DISK_READS | NUMBER | 常にNULLです。 | |
| DIRECT_WRITES | NUMBER | 常にNULLです。 | |
| ROWS_PROCESSED | NUMBER | 常にNULLです。 | |
| FETCHES | NUMBER | 常にNULLです。 | |
| END_OFFETCH_COUNT | NUMBER | 常にNULLです。 |
DBA_SQL_MANAGEMENT_CONFIG
DBA_SQL_MANAGEMENT_CONFIG ビューは、SPM内の構成パラメータを記録しています。このビューの各フィールドの意味は以下の表のとおりです。
フィールド名 |
データ型 |
NULL許容 |
説明 |
|---|---|---|---|
| PARAMETER_NAME | VARCHAR2(128) | NOT NULL | 構成パラメータ名。例：
|
| PARAMETER_VALUE | NUMBER | NOT NULL | 構成パラメータ値。 |
| LAST_MODIFIED | TIMESTAMP(6) | パラメータ値が最後に更新された日時。 | |
| MODIFIED_BY | VARCHAR2(128) | パラメータ値を最後に更新したユーザー。 |
DBMS_SPMシステムパッケージ
DBMS_SPM システムパッケージはSQL計画を管理するために使用され、Plan Baseline情報のロード、変更、削除をサポートします。
次の表は、OceanBaseデータベースの現在のバージョンでサポートされているDBMS_SPMサブプログラムとその概要を示しています。
サブプログラム |
説明 |
|---|---|
| ACCEPT_SQL_PLAN_BASELINE | エボリューション計画の結果に基づいて、計画をベースラインとして受け入れます。 |
| ALTER_SQL_PLAN_BASELINE | ベースライン内の1つまたは一連の計画の属性を変更します。戻り値は変更されたベースラインの数を示します。 |
| CANCEL_EVOLVE_TASK | エボリューション中のタスクをキャンセルします。 |
| CONFIGURE | SPMの一部のパラメータを設定します。 |
| DROP_EVOLVE_TASK | エボリューションタスクを破棄します。 |
| DROP_SQL_PLAN_BASELINE | 1つまたは複数の計画ベースラインを破棄します。 |
| LOAD_PLANS_FROM_CURSOR_CACH | Cursor Cacheから1つまたは複数のSQL計画を読み取り、計画ベースラインとして設定します。 |
ACCEPT_SQL_PLAN_BASELINE
ACCEPT_SQL_PLAN_BASELINE プロシージャは、エボリューション計画の結果に基づいて、計画をベースラインとして受け入れるために使用されます。
構文
Oracleモードの構文は以下のとおりです：
DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR,
plan_name IN VARCHAR := NULL);
MySQLモードの構文は以下のとおりです：
DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE (
database_name VARCHAR(65535),
sql_handle VARCHAR(65535),
plan_name VARCHAR(65535) DEFAULT NULL);
パラメータ説明
パラメータ名 |
説明 |
|---|---|
| database_name | データベース名。このパラメータはMySQLモードでのみ指定が必要です。 |
| sql_handle | SQLの識別子、すなわちSQL_IDです。 |
| plan_name | 計画の名前、すなわちPlan Hash Valueです。値がNULLの場合、エボリューション中のすべてのSQL計画を受け入れることを意味します。 |
例
DECLARE
v_accept_plans NUMBER;
BEGIN
v_accept_plans := DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE(
sql_handle => '529F6E6454EF579C7CC265D1F6131D70',
plan_name => '3388268709115914355'
);
END;
/
ALTER_SQL_PLAN_BASELINE
ALTER_SQL_PLAN_BASELINE 関数は、ベースライン内の1つまたは一連の計画の属性を変更するために使用されます。戻り値は変更されたベースラインの数を示します。
構文
Oracleモードの構文は以下のとおりです：
DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR,
plan_name IN VARCHAR := NULL,
attribute_name IN VARCHAR,
attribute_value IN VARCHAR
)
RETURN PLS_INTEGER;
MySQLモードの構文は次のとおりです：
DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
database_name VARCHAR(65535),
sql_handle VARCHAR(65535),
plan_name VARCHAR(65535) DEFAULT NULL,
attribute_name VARCHAR(65535),
attribute_value VARCHAR(65535)
)
RETURN DECIMAL;
パラメータ説明
パラメータ名 |
説明 |
|---|---|
| database_name | データベース名。このパラメータはMySQLモードでのみ指定が必要です。 |
| sql_handle | SQLの識別子、すなわちSQL_IDです。 |
| plan_name | プランの名前、すなわちPlan Hash Valueです。値がNULLの場合、そのSQLに対して進化中のすべてのプランを受け入れることを意味します。 |
| attribute_name | プランの属性名。属性説明を参照してください。 |
| attribute_value | プランの属性値。属性説明を参照してください。 |
プロパティ説明
プロパティ名 |
許容されるプロパティ値 |
説明 |
|---|---|---|
| enabled | 'YES' または 'NO' | 'YES' はプランベースラインが有効であることを示します。最終的にプランベースラインが使用されるかどうかは、受け入れられるかどうかによって決まります。 |
| fixed | 'YES' または 'NO' | 'YES' は現在のプランベースラインを優先的に使用し、自動進化を行わないことを示します。 |
| autopurge | 'YES' または 'NO' | 'YES' はプランベースラインが一定期間使用されない場合、自動的に廃棄されることを示します。'NO' はプランベースラインが決して廃棄されないことを示します。 |
| plan_name | 最大30文字の文字列。 | プランの名前。 |
| description | 最大500バイトの文字列。 | プランの説明。 |
例
特定のPlan Baselineを固定すると、そのSQLは以降そのプランのみを使用します。
DECLARE
v_alter_plans NUMBER;
BEGIN
v_alter_plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle => '529F6E6454EF579C7CC265D1F6131D70',
plan_name => '3388268709115914355',
attribute_name => 'fixed',
attribute_value => 'YES' );
END;
/
CANCEL_EVOLVE_TASK
CANCEL_EVOLVE_TASKプロシージャは、進化中のタスクをキャンセルするために使用されます。
構文
Oracleモードの構文：
DBMS_SPM.CANCEL_EVOLVE_TASK (
task_name INVARCHAR2);
MySQLモードの構文：
DBMS_SPM.CANCEL_EVOLVE_TASK (
database_name VARCHAR(65535),
task_name VARCHAR(65535));
パラメータ説明
パラメータ名 |
説明 |
|---|---|
| database_name | データベース名。このパラメータはMySQLモードでのみ指定が必要です。 |
| task_name | タスク名。現在のOceanBaseデータベースで SQL_ID が表す進化タスク名です。 |
例
BEGIN
DBMS_SPM.CANCEL_EVOLVE_TASK ('SYS_AUTO_SPM_EVOLVE_TASK');
END;
CONFIGURE
CONFIGURE プロシージャは、SPMの一部のパラメータを設定するために使用されます。
構文
Oracleモードの構文は次のとおりです：
DBMS_SPM.CONFIGURE (
parameter_name IN VARCHAR2,
parameter_value IN NUMBER
);
MySQLモードの構文は次のとおりです：
DBMS_SPM.CONFIGURE (
database_name VARCHAR(65535),
parameter_name VARCHAR(65535),
parameter_value DECIMAL
);
パラメータ説明
パラメータ名 |
説明 |
|---|---|
| database_name | データベース名。このパラメータはMySQLモードでのみ指定が必要です。 |
| parameter_name | 設定するパラメータ名。SPMパラメータ説明を参照してください。 |
| parameter_value | 設定するパラメータ値。SPMパラメータ説明を参照してください。 |
SPMパラメータ説明
SPMパラメータ名 |
説明 |
許容範囲 |
デフォルト値 |
|---|---|---|---|
| space_budget_percent | SPMのSYSAUX領域の最大割合を設定します。 | 1、2、...、50 | 10 |
| plan_retention_weeks | プランが使用されない場合、自動的に削除されるまでの週数を設定します。 | 5、6、...、523 | 53 |
例
プランが20週間使用されない場合、自動的に削除されるように設定します。
BEGIN
DBMS_SPM.CONFIGURE ('plan_retention_weeks', 20);
END;
DROP_EVOLVE_TASK
DROP_EVOLVE_TASK プロシージャは、進化タスクを破棄するために使用されます。
構文
Oracleモードの構文は次のとおりです：
DBMS_SPM.DROP_EVOLVE_TASK (
task_name IN VARCHAR2);
MySQLモードの構文は次のとおりです：
DBMS_SPM.DROP_EVOLVE_TASK (
database_name VARCHAR(65535),
task_name VARCHAR(65535));
パラメータ説明
パラメータ名 |
説明 |
|---|---|
| database_name | データベース名。このパラメータはMySQLモードでのみ指定する必要があります。 |
| task_name | タスク名。現在のOceanBaseデータベースで SQL_ID が表す進化タスクの名前です。 |
例
BEGIN
DBMS_SPM.DROP_EVOLVE_TASK ('SYS_AUTO_SPM_EVOLVE_TASK');
END;
DROP_SQL_PLAN_BASELINE
DROP_SQL_PLAN_BASELINE 関数は、1つまたは複数の計画ベースラインを破棄するために使用されます。
構文
Oracleモードの構文は次のとおりです：
DBMS_SPM.DROP_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2,
plan_name IN VARCHAR2 := NULL
)
RETURN PLS_INTEGER;
MySQLモードの構文は次のとおりです：
DBMS_SPM.DROP_SQL_PLAN_BASELINE (
database_name VARCHAR(65535),
sql_handle VARCHAR(65535),
plan_name VARCHAR(65535) DEFAULT NULL
)
RETURN DECIMAL;
パラメータ説明
パラメータ名 |
説明 |
|---|---|
| database_name | データベース名。このパラメータはMySQLモードでのみ指定する必要があります。 |
| sql_handle | SQLの識別子、すなわち SQL_ID。実行計画はSQL文を識別するハンドル（Handle）です。特定の実行計画を持つSQL文を照会する際、類似したSQLが複数存在する場合、sql_handle を使用して対象文を正確に指定できます。 |
| plan_name | 実行計画の名前、すなわちPlan Hash Value。値が NULL の場合、そのSQLに対して進化中のすべての計画を受け入れることを意味します。 |
例
DECLARE
v_drop_plans number;
BEGIN
v_drop_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => '529F6E6454EF579C7CC265D1F6131D70',
plan_name => '3388268709115914355' );
END;
/
LOAD_PLANS_FROM_CURSOR_CACHE
LOAD_PLANS_FROM_CURSOR_CACHE 関数は、Plan Cacheから1つまたは複数のSQLの計画を読み取り、計画ベースラインとして設定するために使用されます。
Oracleモードの構文は次のとおりです：
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id IN VARCHAR,
plan_hash_value IN NUMBER := NULL,
fixed IN VARCHAR := 'NO',
enabled IN VARCHAR := 'YES'
)
RETURN PLS_INTEGER;
MySQLモードの構文は以下のとおりです：
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
database_name VARCHAR(65535),
sql_id VARCHAR(65535),
plan_hash_value DECIMAL DEFAULT NULL,
is_fixed VARCHAR(65535) DEFAULT 'NO',
enabled VARCHAR(65535) DEFAULT 'YES'
)
RETURN DECIMAL;
パラメータ説明
パラメータ名 |
説明 |
|---|---|
| database_name | データベース名。このパラメータはMySQLモードでのみ指定が必要です。 |
| sql_id | 読み込むSQL_ID。 |
| plan_hash_value | 読み込むプランのPlan Hash Value。値がNULLの場合、Plan Cache内で指定されたSQLに対するすべてのプランをプランベースラインとして読み込みます。 |
| is_fixed | 読み込むプランベースラインのis_fixed属性。 |
| enabled | 読み込むプランベースラインのenabled属性。 |
例
DECLARE
v_load_plans number;
BEGIN
v_load_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '529F6E6454EF579C7CC265D1F6131D70',
plan_hash_value => 13388268709115914355);
END;
/