SPMを使用したSQL実行計画の管理
SQL計画管理(SPM)を使用すると、SQL実行計画(SQL計画)の変更によってSQL文の実行時のパフォーマンスが低下しないようにできます。
SPMは、OracleオプティマイザでSQL計画を自動的に管理し、データベースで既知または確認済の計画のみが使用されるようにする予防的なメカニズムです。データベース・アプリケーションのパフォーマンスは、一貫性のあるSQL文の実行に大きく依存します。SQL文の実行計画は、オプティマイザ統計の再収集、オプティマイザ・パラメータの変更、スキーマまたはメタデータ定義の変更など、様々な理由で予期せず変更される可能性があります。SPMは、環境の変更中に現在のSQL計画を保持するフレームワークを通じて「計画の安定性」を提供しますが、変更できるのはより適切な計画の場合のみです。SQL文に対して新しいSQL計画が見つかった場合、そのSQL計画は、現在の計画と同等か、またはそれよりも高いパフォーマンスであることが検証されるまで使用されません。
SPMは、SQL計画ベースラインというプロアクティブ・メカニズムを使用します。これは、OracleオプティマイザがSQL文に使用できる承認済のSQL計画のセットです。SPMでは、ベースラインを使用することで、環境の変更による計画の不具合を回避しながら、オプティマイザがより適切な計画を検出して使用できるようにします。
SPMのメインコンポーネントは次のとおりです。
- 計画の取得: 一連のSQL文に対して、計画に関連する情報を取得し、SQL管理ベースに保存するための方法。計画の取得には、計画をSPMに認識させることと、次の方法で行うことができます。
- 計画の自動取得: 有効にすると、データベースは実行したSQL文の自動取得が可能かどうかを確認します。計画の自動取得の対象になるには、実行された文が繰返し可能であることと、どの取得フィルタでも除外されていないことが必要です。
- 計画の手動取得: SQL文の既存実行計画のSQL計画ベースラインへのユーザー開始バルク・ロード。
- 計画の選択: Oracleオプティマイザが、保存された計画履歴に基づいて計画変更を検出する機能、およびSQL計画ベースラインを使用して、パフォーマンスが低下する可能性を回避するための計画を選択します。
- 計画の進化: 手動または自動で、既存のSQL計画ベースラインに新しい計画を追加するプロセス。Oracleオプティマイザは、新しい計画を検証して既存のSQL計画ベースラインに追加します。
SPMとそのコンポーネントの詳細は、Oracle Database SQLチューニング・ガイドのSQL計画管理の概要を参照してください。
SPMを使用するには、「管理対象データベースの詳細」ページに移動し、左側のペインの「リソース」の下にある「SQL計画管理」をクリックします。データベース管理では、次のSPMタスクを実行できます:
- SQL計画ベースラインの管理
- SQL計画をSQL計画ベースラインにロードするタスクを送信します。
- SQL計画ベースラインの有効化、無効化または編集、自動計画取得、SPM展開アドバイザの自動タスク・パラメータなどの構成タスクを実行します。
SPMタスクの実行に必要な権限
次の表に、SPMタスクとその実行に必要な権限を示します。
ADMINISTER SQL MANAGEMENT OBJECT
権限を付与されているすべてのユーザーがDBMS_SPM
パッケージを実行できます。
タスク | 必要な権限 |
---|---|
SQL文に関連付けられている1つ以上のSQL計画またはすべての計画の属性を変更します。 | SYS.DBMS_SPM パッケージに対するEXECUTE 権限。
|
SQL管理ベースのディスク領域制限を変更します。 | SYS.DBMS_SPM パッケージに対するEXECUTE 権限。
|
未使用のSQL計画の保存期間を変更します。 | SYS.DBMS_SPM パッケージに対するEXECUTE 権限。
|
自動取得フィルタを構成します。 | SYS.DBMS_SPM パッケージに対するEXECUTE 権限。
|
SPM展開アドバイザの自動タスクを構成します。 | SYS.DBMS_SPM パッケージに対するEXECUTE 権限。
ノート: 自動SPM展開アドバイザ・タスク |
計画の自動取得を使用不可にします。 | ALTER SYSTEM privilege |
SPM展開アドバイザの自動タスクを無効にします。 | SYS.DBMS_AUTO_TASK_ADMIN パッケージに対するEXECUTE 権限。
|
SPM展開アドバイザの高頻度自動タスクを無効にします。 | SYS.DBMS_SPM パッケージに対するEXECUTE 権限。
|
SQL管理ベースに格納されたSQLプラン・ベースラインの使用を無効にします。 | ALTER SYSTEM privilege |
SQL文に関連付けられている単一のSQL計画またはすべての計画を削除します。 | SYS.DBMS_SPM パッケージに対するEXECUTE 権限。
|
計画の自動取得を有効にします。 | ALTER SYSTEM privilege |
SPM展開アドバイザの自動タスクを有効にします。 | SYS.DBMS_AUTO_TASK_ADMIN パッケージに対するEXECUTE 権限。
|
SPM展開アドバイザの高頻度自動タスクを有効にします。 | SYS.DBMS_SPM パッケージに対するEXECUTE 権限。
|
SQL管理ベースに格納されたSQLプラン・ベースラインの使用を有効にします。 | ALTER SYSTEM privilege |
AWRスナップショットからのSQL計画のロード | SYS.DBMS_SPM およびSYS.DBMS_SCHEDULER パッケージに対するEXECUTE 権限。
|
カーソル・キャッシュからのSQL計画のロード | SYS.DBMS_SPM およびSYS.DBMS_SCHEDULER パッケージに対するEXECUTE 権限。
|
SQL計画ベースライン構成の詳細を表示します。 | 次のビューに対するSELECT またはREAD 権限:
|
SQL計画ベースライン詳細の表示 |
|
SQL計画ベースラインを表示します。 | SYS.DBA_SQL_PLAN_BASELINES ビューに対するSELECT またはREAD 権限。
|
SQL計画ベースラインをロードするために発行されたジョブを表示します。 | SYS.DBA_SCHEDULER_JOBS ビューに対するSELECT またはREAD 権限。
|
属性別に集計されたSQL計画ベースラインの数を表示します。 | SYS.DBA_SQL_PLAN_BASELINES ビューに対するSELECT またはREAD 権限。
|
最後の実行で集計されたSQL計画ベースラインの数を表示します。 | SYS.DBA_SQL_PLAN_BASELINES ビューに対するSELECT またはREAD 権限。
|
カーソル・キャッシュからSQL文を表示します。 | SYS.V_$SQL ビューに対するSELECT またはREAD 権限。
|
SQL計画ベースラインの管理
SQL計画ベースラインは、「SQL計画ベースライン」タブで管理できます。
「SQL計画ベースライン」タブの上部には、次のタイルが表示されます。
- サマリー: SQL計画ベースラインの合計数と、SQL計画ベースライン、自動計画取得および自動SPM展開アドバイザのタスクが有効かどうかが表示されます。「サマリー」タイルで、「有効化」または「無効化」ボタンをクリックし、データベース資格証明を指定して、SQL計画ベースライン、自動計画取得および自動SPM展開アドバイザ・タスクを有効または無効にできます。
- ベースライン最終実行: SQL計画ベースラインの数が、最後に実行された日時に基づいて表示されます。「ベースライン最終実行」タイルで、円グラフにマウス・ポインタを重ねて追加の詳細を表示し、凡例にリストされている期間オプションをクリックして、チャートに表示されるデータをフィルタします。
- SQL計画統計: 次の統計別に分類されたSQL計画が表示されます。
- 有効: Oracleオプティマイザが使用できるSQL計画。
- 受入れ済: SQL計画ベースラインにあり、Oracleオプティマイザで使用できるSQL計画。
- 再生成済: Oracleオプティマイザによって再現されるSQL計画。
- 修正済: 優先としてマークされた承認済のSQL計画。これにより、Oracleオプティマイザは、SQL計画ベースライン内のこれらの計画のみを考慮するようになります。
- 自動パージ: デフォルトの保存期間後に自動的にパージされるように構成されたSQL計画。
「SQL計画統計」タイルで、水平棒グラフにマウス・ポインタを重ねて追加の詳細を表示し、凡例にリストされているオプションをクリックしてチャートに表示されるデータをフィルタします。
「SQL計画」セクションには、実行されたSQL計画が、SQL計画が最後に実行された日時、有効化、受入れ済、再現済など、その起点などの詳細とともにリストされます。リストをフィルタするには:
- 最終実行時間でフィルタするには、「ベースライン最終実行」タイルで円グラフのセクションをクリックします。
- 「SQL計画統計」タイルのバーをクリックして、自動パージが有効、受入れ済、再現済、固定または構成されているかどうかに基づいて計画をフィルタします。
検索フィールドを使用して、SQLテキスト、計画名またはオリジンで検索することもできます。
「SQL計画」セクションでは、次のことができます:
- 「SQLテキスト」列の「SQL計画」リンクをクリックして、SQL計画を表示します。
- SQL文の「アクション」アイコン()をクリックし、メニューの次のオプションを使用します:
- SQL文属性の編集: クリックしてSQL文属性を編集します。SQL文の属性に加えられたすべての変更は、関連付けられたすべてのSQL計画に影響することに注意してください。
- SQL文の削除: クリックしてSQL文を削除します。SQL文を削除すると、関連付けられたSQL計画も削除されることに注意してください。
- SQL計画の「アクション」アイコン()をクリックし、メニューの次のオプションを使用します:
- 詳細の表示: SQL計画を表示する場合にクリックします。
- 属性の編集: SQL計画の次の属性を設定または編集する場合にクリックします。
- 自動パージ: このチェック・ボックスを選択すると、指定した保存期間後にSQL計画が自動的にパージ(削除)されます。
- 有効: このチェック・ボックスを選択して、SQL計画が有効な計画であることを示します。
- 固定: このチェック・ボックスを選択して、SQL計画が固定計画であることを示します。
- 削除: クリックして、SQL計画ベースラインからSQL計画を削除します。
SQL計画のロード
「SQL計画のロード」タブで、タスクを発行してSQL計画をSQL計画ベースラインにロードできます。
SQL計画は、次のソースからロードできます。
- AWR: 自動ワークロード・リポジトリ(AWR)のスナップショットから計画をロードします。詳細は、AWRからのSQL計画のロードを参照してください。
ノート
AWRからのSQL計画のロードのサポートは、Oracle Databasesバージョン12.2以降でのみ使用できます。 - カーソル・キャッシュ: 共有SQL領域(カーソル・キャッシュ)から計画をロードします。詳細は、「カーソル・キャッシュからのSQL計画のロード」を参照してください。
AWRからのSQL計画のロード
- 「SQL計画管理」セクションで、「SQL計画のロード」タブをクリックします。
- 「次からのSQL計画のロード」ドロップダウン・リストで、「AWR」を選択し、「ロード」をクリックします。
- 「AWRからのSQL計画のロード」パネルで:
- 「一般」セクションで、タスクを発行するには、次の情報を入力します:
- タスク名: タスクの自動移入名を確認し、必要に応じて変更します。
- 説明: オプションで、タスクの説明を入力します。
- 開始スナップショット: 範囲内の開始スナップショットの番号を入力し、ドロップダウン・リストから選択します。
- 終了スナップショット: 範囲内の終了スナップショットの番号を入力し、ドロップダウン・リストから選択します。
- SQLテキスト・フィルタ: オプションで、フィルタ基準を満たす計画のみをロードするSQLテキストを入力します。値を指定しない場合、AWR内の指定されたスナップショット範囲内のすべての計画が選択されます。
- プラン属性: 次のチェック・ボックスを選択して、プラン属性を指定します:
- 修正済: このチェック・ボックスを選択して、ロードされた計画が固定計画であることを示します。
- 有効: ロードされたプランが有効なプランであることを示す場合は、このチェック・ボックスを選択します。
- 「資格証明」セクションの「資格証明タイプ」ドロップダウン・リストで使用可能なオプションの1つを選択して、管理対象データベースに接続するデータベース資格証明を指定します。資格証明タイプの詳細は、資格証明を使用した診断および管理タスクの実行を参照してください。
- 「変更の保存」をクリックします。
- 「一般」セクションで、タスクを発行するには、次の情報を入力します:
カーソル・キャッシュからのSQL計画のロード
- 「SQL計画管理」セクションで、「SQL計画のロード」タブをクリックします。
- 「次からのSQL計画のロード」ドロップダウン・リストで、「カーソル・キャッシュ」を選択し、「ロード」をクリックします。
- 「カーソル・キャッシュからのSQL計画のロード」パネルで:
- 「一般」セクションで、タスクを発行するには、次の情報を入力します:
- タスク名: タスクの自動移入名を確認し、必要に応じて変更します。
- 説明: オプションで、タスクの説明を入力します。
- ベースラインのフェッチに使用: 次のいずれかのオプションを使用して、SQL計画をロードします:
- SQL ID: SQL計画をロードするカーソル・キャッシュ内のSQL文を識別するには、このラジオ・ボタンを選択します。このラジオ・ボタンを選択すると、次のフィールドが表示されます。
- SQL ID: SQL文IDを入力します。
- 計画ハッシュ値: オプションで、SQL計画の計画ハッシュ値を入力します。値を指定しない場合、SQL文のカーソル・キャッシュに存在するすべての計画がロードされます。
- 計画がロードされるSQL計画ベースラインの識別に使用されるパラメータ: オプションで、「SQLテキスト」または「SQLハンドル」ラジオ・ボタンを選択し、「パラメータ値」フィールドにパラメータ値を入力します。値が指定されていない場合、指定したSQL文のテキストはカーソル・キャッシュから抽出され、計画のロード先となるSQL計画ベースラインの特定に使用されます。SQL計画ベースラインが存在しない場合は作成されます。
- フィルタ名: このラジオ・ボタンを選択して、SQL文またはSQL文のセットを識別するフィルタを指定します。このラジオ・ボタンを選択すると、次のフィールドが表示されます。
- フィルタ名: ドロップダウン・リストからフィルタ名を選択します。
- フィルタ値: 対応するフィルタ値を入力します。
- SQL ID: SQL計画をロードするカーソル・キャッシュ内のSQL文を識別するには、このラジオ・ボタンを選択します。このラジオ・ボタンを選択すると、次のフィールドが表示されます。
- プラン属性: 次のチェック・ボックスを選択して、プラン属性を指定します:
- 修正済: このチェック・ボックスを選択して、ロードされた計画が固定計画であることを示します。固定計画は、優先としてマークされた承認済の計画です。これにより、オプティマイザはベースライン内の固定計画のみを考慮するようになります。
- 有効: ロードされたプランが有効なプランであることを示す場合は、このチェック・ボックスを選択します。Oracleオプティマイザでは、有効化された計画を使用できます。
- 「資格証明」セクションの「資格証明タイプ」ドロップダウン・リストで使用可能なオプションの1つを選択して、管理対象データベースに接続するデータベース資格証明を指定します。資格証明タイプの詳細は、資格証明を使用した診断および管理タスクの実行を参照してください。
- 「変更の保存」をクリックします。
- 「一般」セクションで、タスクを発行するには、次の情報を入力します:
SPM構成タスクの実行
「構成」タブで、SQL計画ベースラインおよび自動計画取得パラメータの有効化または無効化などのSPM構成タスクを実行できます。
「構成」タブには、次のメイン・セクションがあります:
- SQL計画ベースライン: SQL計画ベースラインを有効または無効にするオプションを提供します。SQL計画ベースラインが有効な場合、このセクションに次のパラメータが表示され、それらに変更を加えるには、「編集」をクリックします:
- 計画保持(週): 消去する前に未使用のSQL計画を保持する週の数。指定できる期間の範囲は5から523週で、デフォルトは53週です。
- 領域予算(%): SQL管理ベースが使用可能な
SYSAUX
領域の最大の割合。この制限の許容範囲は1から50%で、デフォルトは10%です。
- 計画の自動取得: 計画の自動取得を有効または無効にするオプションを提供します。自動フィルタにより、必要な文のみを取得して重要でない文を除外できます。この手法により、
SYSAUX
表領域の領域が削減されます。計画の自動取得が有効になっている場合は、次のフィルタがこのセクションに表示され、それらに変更を加えるには、「編集」をクリックします:
- 含める処理または除外する処理: 自動取得に含めるか除外する処理。
- 含めるモジュールまたは除外するモジュール: 自動取得に含めるモジュールまたは自動取得から除外するモジュール。
- 含めるスキーマ名の解析中または除外するスキーマ名の解析中: 自動取得の対象または除外する解析中のスキーマ名。
- 含めるSQLテキストまたは除外するSQLテキスト: 自動取得に含めるか除外するSQLテキスト。
ノート
選択的プラン取得(フィルタ)は、Oracle Databasesバージョン12.2以降でのみ使用できます。 - SPM展開アドバイザの自動タスク: SPM展開アドバイザの自動タスクを有効または無効にするオプションを提供します。自動SPM展開アドバイザ・タスクが有効になっている場合は、次のパラメータがこのセクションに表示され、それらに変更を加えるには、「編集」をクリックします:
- SPM展開アドバイザの高頻度自動タスク: SPM展開アドバイザの自動タスクをより頻繁に実行できるように有効化されているかどうかが表示されます。
- 代替プラン・ソース: 追加プランを検索するソース。
- 代替プラン・ベースライン: ロードする必要がある代替プラン。デフォルト値は「既存」です。
- 代替プラン制限: 合計でロードするプランの最大数。デフォルト値は「無制限」です。
- 計画の自動受入れ: 推奨された計画を自動的に受入れする必要があるかどうかが表示されます。
- 許可される時間制限(秒): グローバル時間制限(秒)。これは、タスクに許可される合計時間です。
ノート
- Autonomous Databasesの場合、SQL計画管理構成タスクは使用できません。
- SPM展開アドバイザの自動タスク・パラメータを編集するには、
SYSDBA
権限が必要です。 - SPM展開アドバイザの自動タスクは、Oracle Databasesバージョン12.2以降でのみ使用できます。
- 高頻度自動SPM展開アドバイザ・タスクは、Oracle Exadataプラットフォームで実行されているOracle Databases 19c以降でのみ使用できます。