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とは実行計画のベースラインであり、検証済みの実行計画情報（アウトラインデータなど）を永続的に格納するために使用されます。各実行計画には1つの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ノードのローカルキャッシュに保存され、定期的にインナーテーブルに同期されるため、任意の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_OF_FETCH_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;
/