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の使用方法
プロジェクトのデリバリプロセスにおいては、通常 トラフィック再生によるSQLパフォーマンスの検証 を通じて、パフォーマンスが期待に達しないSQLをチューニングします。一般的なチューニング作業には、インデックスの調整（追加・削除）、テーブルのパーティション構造の調整、テーブルの分散・移行・複製テーブルへの変更、または特定のSQLに対するアウトラインの結合によるチューニングなどが含まれます。チューニング完了後は、以下の手順でSPM（SQL計画管理）を有効化し、本番環境におけるSQL実行計画を安定させることを推奨します。
基礎的な実践手順
ステップ１：SPMを有効にする
ALTER SYSTEM SET sql_plan_management_mode = 'OnlineEvolve';
説明：
OnlineEvolveモードは、SPMがオンラインで進化し、より適切な計画を動的に選択することを許可します。- テナントに
ALTER SYSTEM権限が付与されていることを確認する必要があります。
ステップ２：Plan Cacheからベースライン計画をインポートする
適用シナリオ：ストレステストが終了した直後、Plan Cacheには依然として多数のSQLの実行計画が保持されています。この場合、Plan Cache内の実行計画を直接SPMにインポートしてベースライン計画を作成できます。
ストアドプロシージャは以下のとおりです：
CALL DBMS_SPM.BATCH_LOAD_PLANS_FROM_CURSOR_CACHE();
その他のシナリオ：ストレステストが長時間前に終了し、Plan Cache内の計画が無効になっている場合、新たなストレステストを実行することで、SPMが自動的にベースライン計画をキャプチャするようトリガーできます。
SPMの監視とメンテナンスの進化
DBA_OB_SPM_EVO_RESULTを使用したSQL進化の分析
ビューDBA_OB_SPM_EVO_RESULT(V4.2.5バージョンで導入され、そのフィールドEVO_PLAN_HASHとBASELINE_PLAN_HASHはV4.2.5 BP4バージョンで導入されました)は、SQL計画の進化プロセスを記録しており、以下が含まれます：
- 進化をトリガーした時間(
START_TIMEとEND_TIME) - 新しい計画とベースライン計画の実行回数、実行時間、およびHash値
- 進化状態(
successは進化完了を示します)
SPM進化のケース結果出力は以下のとおりです：
+----------------------------------+-----------------+---------------------+-------------------+---------------------+-------------------+----------------------+----------------------+
| SQL_ID | NEW_PLAN_BETTER | EVO_PLAN_EXEC_COUNT | EVO_PLAN_CPU_TIME | BASELINE_EXEC_COUNT | BASELINE_CPU_TIME | EVO_PLAN_HASH | BASELINE_PLAN_HASH |
+----------------------------------+-----------------+---------------------+-------------------+---------------------+-------------------+----------------------+----------------------+
| 6E3C88F5D1A97E39A0F7F940EEC6B55E | 1 | 93 | 7077 | 57 | 11958 | 14372305556477891660 | 5690258854010153160 |
| AAD1D09174C0B1512772080AD32DF991 | 0 | 73 | 7339 | 77 | 5819 | 2529381794102860446 | 11087212812447126950 |
| D603D676045B5207E0C5F407E60C4DA7 | 1 | 110 | 1373 | 41 | 5303 | 10177384921167613051 | 3998439253758435947 |
| FEE266BF5572B246FAF2DDA13D309E63 | 0 | 73 | 1355 | 77 | 1274 | 10177384921167613051 | 18341027855757513928 |
| 216171CF20FDA047C669CC68ECFCF34D | 1 | 96 | 1749 | 54 | 5026 | 10177384921167613051 | 3998439253758435947 |
| 45C742A8E022C6603458F203B7ACB7A1 | 1 | 115 | 624 | 35 | 1617 | 2529381794102860446 | 11087212812447126950 |
| D4839A71B942564C1CA3827FFDD4BAA5 | 0 | 88 | 7335 | 62 | 7157 | 3998439253758435947 | 10177384921167613051 |
| D4839A71B942564C1CA3827FFDD4BAA5 | 1 | 95 | 6209 | 55 | 7093 | 3998439253758435947 | 10177384921167613051 |
| 1578C21A38305F8CF1802714B8810EC1 | 0 | 35 | 16223 | 115 | 5075 | 9360560344866912357 | 8291178960580797251 |
| AF9FB704A76392E937E41D0C89D6DD90 | 0 | 75 | 1093 | 75 | 1023 | 10177384921167613051 | 18341027855757513928 |
+----------------------------------+-----------------+---------------------+-------------------+---------------------+-------------------+----------------------+----------------------+
例
パフォーマンスの低下したSQLのベースライン計画を特定して固定する
以下のクエリを使用して、頻繁に進化し、新しい計画のパフォーマンスが低いSQLを特定します：
SELECT SQL_ID, BASELINE_PLAN_HASH, COUNT(*),
SUM(BASELINE_EXEC_COUNT), SUM(EVO_PLAN_EXEC_COUNT),
SUM(BASELINE_CPU_TIME), SUM(EVO_PLAN_CPU_TIME)
FROM DBA_OB_SPM_EVO_RESULT
WHERE TYPE = 'OnlineEvolve' AND BASELINE_EXEC_COUNT >= 130
GROUP BY SQL_ID, BASELINE_PLAN_HASH
ORDER BY COUNT(*) DESC LIMIT 10;
クエリロジックの説明：
- 篩選条件：
TYPE = 'OnlineEvolve'：オンライン進化モードでのレコードを分析します。BASELINE_EXEC_COUNT >= 130：高頻度であり、かつベースライン計画よりも著しく優れている進化レコードに焦点を当てます。
結果出力：
+----------------------------------+----------------------+----------+--------------------------+--------------------------+------------------------+------------------------+
| SQL_ID | BASELINE_PLAN_HASH | COUNT(*) | SUM(BASELINE_EXEC_COUNT) | SUM(EVO_PLAN_EXEC_COUNT) | SUM(BASELINE_CPU_TIME) | SUM(EVO_PLAN_CPU_TIME) |
+----------------------------------+----------------------+----------+--------------------------+--------------------------+------------------------+------------------------+
| 074763820FA61A305954C766840FDADC | 14374891169779151928 | 588 | 84066 | 4134 | 896978 | 44750359 |
| 235E3D8A37DE36748D5AB339CA7C55E6 | 18023436715182273474 | 95 | 13366 | 884 | 191699 | 10524837 |
| DF561B4447FF2DD02C14BCA10AF13778 | 18023436715182273474 | 87 | 12244 | 806 | 184878 | 9957498 |
| E8E91ADB581F0BF42F18906475265FC8 | 17605863854413066828 | 42 | 5970 | 330 | 99170 | 5631082 |
| 9E36920D6AA2CD8681210954EDBEE722 | 14714973681921413470 | 39 | 5613 | 237 | 194064 | 9644621 |
| 9C6846E1CA5F255770E0E78F036942D6 | 18023436715182273474 | 27 | 3805 | 245 | 59984 | 2487403 |
| BA3BDA17E30C2A18F276D294949B2F30 | 18023436715182273474 | 20 | 2833 | 167 | 48265 | 3142497 |
| 04CF831409FDEB6648343852A949CF09 | 14374891169779151928 | 16 | 2155 | 245 | 11702 | 611840 |
| E18C32524CD902E2ED56D6171C1E7227 | 18023436715182273474 | 12 | 1709 | 91 | 39279 | 3385151 |
+----------------------------------+----------------------+----------+--------------------------+--------------------------+------------------------+------------------------+
- 結果のフィールド
count(*)（進化回数）：- 最初のレコードの
588回は、このSQLのベースライン計画と新しい計画を588回比較し、SPM進化を頻繁にトリガーしたことを示しています。
- 最初のレコードの
- CPU時間の比較：
- ベースライン計画の合計CPU時間：
896,978 μs（約0.9秒） - 新しい計画の合計CPU時間：
44,750,359 μs（約44.75秒） - 平均CPU時間の比較：
- ベースライン計画の平均：
896,978 / 84,066 ≈ 10.7 μs - 新しい計画の平均：
44,750,359 / 4,134 ≈ 10,838 μs
- ベースライン計画の平均：
- 新しい計画のCPU時間はベースラインの1000倍であり、パフォーマンスが著しく低下しています。
- ベースライン計画の合計CPU時間：
パフォーマンスが劣化したSQLのベースライン計画を特定して固定する
繰り返し進化し、新しい計画のパフォーマンスが著しく低いSQLについて、そのベースライン計画の状態をFIXEDに設定し、SPMが安定した計画を使用するよう強制します。
例えば、上記の例で進化回数が588回のSQL_ID = 074763820FA61A305954C766840FDADCについて、ベースライン計画としてBASELINE_PLAN_HASH = 14374891169779151928をFIXEDに設定します。SQLの例は以下のとおりです：
SELECT
DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
'074763820FA61A305954C766840FDADC',
'14374891169779151928'
'fixed',
'YES')
FROM DUAL;
関連ドキュメント
SPM関連ビュー
DBA_SQL_PLAN_BASELINESビューは、SPM内のSQLの計画ベースラインを記録します。
DBA_SQL_MANAGEMENT_CONFIGビューは、SPM内の構成パラメータを記録します。
SPM関連システムパッケージ
MySQLモードのSPMシステムパッケージ：
- ACCEPT_SQL_PLAN_BASELINE（MySQLモード）
- ALTER_SQL_PLAN_BASELINE（MySQLモード）
- CANCEL_EVOLVE_TASK（MySQLモード）
- CONFIGURE（MySQLモード）
- DROP_EVOLVE_TASK（MySQLモード）
- DROP_SQL_PLAN_BASELINE（MySQLモード）
- LOAD_PLANS_FROM_CURSOR_CACH（MySQLモード）
OracleモードのSPMシステムパッケージ：