Oracle Database 11gのSQL Plan Management

Oracle Database 11g の SQL Plan Management Oracle ホワイト ペーパー 2007 年 6 月

注 : 本書は オラクルの一般的な製品の方向性を示すことが目的です また 情報提供を唯一の目的とするものであり いかなる契約にも組み込むことはできません 下記の事項は マテリアルやコード 機能の提供を確約するものではなく また 購買を決定する際の判断材料とはなりえません オラクルの製品に関して記載されている機能の開発 リリース および時期については 弊社の裁量により決定いたします Oracle Database 11g の SQL Plan Management 2

Oracle Database 11g の SQL Plan Management 注 :... 2 はじめに... 4 SQL PLAN MANAGEMENT... 4 計画取得... 5 自動計画取得 - " 迅速な処理 "... 5 バルク ロード... 6 計画選択... 9 計画の展開... 10 SQL MANAGEMENT BASE の使用および管理... 10 初期化パラメータ... 10 SQL Management Base の使用領域の管理... 10 SQL PLAN MANAGEMENT の監視... 11 Oracle Enterprise Manager... 11 最初に... 11 init.ora パラメータ値の変更... 12 計画のバルク ロード... 13 属性の変更... 13 SQL 計画ベースラインの実行計画の参照... 14 未承認の計画の展開... 14 DBA ビューを使用した SPM の監視... 15 自動的な SQL チューニングの統合... 16 結論... 16 Oracle Database 11g の SQL Plan Management 3

SQL Plan Management はじめに データベース アプリケーションのパフォーマンスは その問合せ実行に大きく依存します Oracle Optimizer は ユーザーの介入なしで最適な計画を評価するのに非常に適しています ただし SQL 文の実行計画では オプティマイザ統計の再収集 オプティマイザ パラメータまたはスキーマやメタデータ定義の変更など さまざまな理由で予期しない変更が発生する可能性があります 計画が常に良い方向に変更されるとは限らないため 実行計画を固定 ( ストアド アウトライン ) したり オプティマイザ統計をロックしたりする顧客もいます しかし それにより SQL 文のパフォーマンスを向上させる新しいオプティマイザ機能やアクセス パスを使用できなくなります 環境の変更中に現在の実行計画を保存して良い方向への変更だけを承認することが問題の解決になります Oracle Database 11g は 販売されているデータベースの中でこの課題の解決が可能な最初のデータベースです SQL Plan Management(SPM) は 実行計画を展開する 完全に透過的に制御されたフレームワークを提供します SPM を使用すると オプティマイザは 実行計画を自動的に管理し 既知の計画または検証済みの計画だけを使用できます SQL 文に新しい計画がある場合 現在の計画と同等かそれ以上のパフォーマンスを実現する計画であることがデータベースによって検証されるまで 使用されません SQL PLAN MANAGEMENT SQL Plan Management(SPM) は 実行計画の変更によるランタイム パフォーマンスの低下を防止します これを実現するため 検証された ( 信頼できる ) 実行計画だけを使用します 実行計画の変更は追跡され 後で評価されて 新しい実行計画で実行時間が変わらない場合または実行時間が向上する場合にのみ検証済みとして承認されます SQL Plan Management には 以下の 3 つの主要コンポーネントがあります 1. 計画取得 : 関連するすべてのSQL 文の検証された ( 信頼できる ) 実行計画を示すSQL 計画ベースラインを作成します SQL 計画ベースラインは SYSAUX 表領域のSQL Management Baseに保存されます 2. 計画選択 : 検証済みの実行計画だけが SQL 計画ベースラインの文に使用されていることを確認し 文の計画履歴にあるすべての新しい実行計画を追跡します Oracle Database 11g の SQL Plan Management 4

計画履歴は 検証済みの実行計画 未検証の実行計画 および拒否された実行計画で構成されます 3. 計画の展開 : 計画履歴の特定の文に対する未検証の実行計画を評価して 承認または拒否を設定します 計画取得 SQL Plan Management を有効にするには 現在のコストベースの実行計画を SQL Management Base に移入する必要があります これが 各 SQL 文の SQL 計画ベースラインになります SQL Management Base を移入するには 以下の 2 つの異なる方法があります 実行計画の自動取得実行計画または既存の SQL 計画ベースラインのバルク ロード 自動計画取得 - " 迅速な処理 " init.oraパラメータのoptimizer_capture_sql_plan_baselinesをtrueに設定すると 自動計画取得をオンに切り替えることができます ( デフォルト値は FALSE) 自動計画取得を有効にすると SPMリポジトリに 繰り返し実行されるSQL 文が自動的に移入されます 繰り返し実行されるSQL 文を識別するため オプティマイザは 最初にコンパイルされる際に 各 SQL 文のID(SQL_ID) をステートメント ログに格納します SQL 文を再処理 ( 実行またはコンパイル ) する場合 ステートメント ログに存在するIDは 繰り返し可能な文であることを示します SQL 計画履歴は 文に対して作成されます SQLテキスト アウトライン バインド変数 コンパイル環境など 実行計画を再作成するためにオプティマイザによって使用される情報が含まれます 現在のコストベースの計画は 最初のSQL 計画ベースラインとして追加されます この計画は 承認済み ( 検証済み ) として記録されます 承認された計画だけが使用されます その後 新しい計画がこのSQL 文に対して使用される場合 その実行計画は 計画履歴に追加され 検証対象として記録されます 記録された実行計画は 現在のSQL 計画ベースラインと同等またはそれ以上のパフォーマンスの場合のみ 承認済みとしてマークされます Oracle Database 11g の SQL Plan Management 5

図 1 繰り返し可能な SQL 文のステートメント ログと 計画履歴で構成される SQL Management Base バルク ロード実行計画または SQL 計画ベースラインのバルク ロードは データベースを以前のバージョンから Oracle Database 11g にアップグレードする場合や新しいアプリケーションが配置さる場合に特に役立ちます バルク ロードは 自動計画取得と連携するか またはその代わりとして 使用できます バルク ロードされる SQL 計画は 自動的に承認され SQL 計画ベースラインとして既存または新規の計画履歴に追加されます 以下の 3 つの異なる技術を使用して SQL Management Base をバルク ロードできます a) 特定の SQL Tuning Set(STS) 用の実行計画の移入 b) 現在カーソル キャッシュに格納されている実行計画の使用 c) ステージング表からの既存の SQL 計画ベースラインのインポート SQL Tuning Set(STS) から ( 重要な )SQLワークロードの計画をSQL Tuning Set(STS) に取り込み PL/SQL プロシージャのDBMS_SPM.LOAD_PLANS_FROM_SQLSETまたはOracle Enterprise Managerを使用して これらの計画をSQL 計画ベースラインとしてSQL Management Baseにロードできます これらの文を次に実行する際にSQL 計画ベースラインが使用されます Oracle Database 11g の SQL Plan Management 6

STS からの実行計画のバルク ロードは データベース アップグレードの一部として計画変更の必要がない優れた方法です 以下の 4 つの手順を実行します 1. Oracle Database 10g Release 2 で 各 SQL 文の実行計画を含む STS を作成します 2. STS をステージング表にロードし ステージング表をエクスポートします 3. ステージング表を Oracle Database 11g にインポートし STS をアンロードします 4. Oracle Enterprise ManagerまたはDBMS_SPM.LOAD_PLANS_FROM_SQLSET を使用して 実行計画をSQL Management Base(SMB) にインポートします 図 2 STS を使用したデータベース アップグレードの SMB のバルク ロード SQL 計画ベースラインが作成されると Oracle Database 10g Release 2 と 11g Release 1 間で計画の変更が無いことを保証するために使用されます Oracle Database 11g のオプティマイザには異なる実行計画が付属しますが この計画は 計画履歴に追加され 検証対象として記録されます 現在の SQL 計画ベースライン (Oracle 10g Release 2 計画 ) と同等またはそれ以上のパフォーマンスの場合のみ 承認済みとして記録されます カーソル キャッシュから Oracle Database 11gでは カーソル キャッシュのすべての文の計画を直接 SQL 計画リポジトリにロードできます 文のセットを減らすため モジュール名 スキーマ またはSQL_IDに対するフィルタをオプションで適用できます PL/SQLプロシージャのDBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE またはOracle Enterprise Managerを使用して 計画をロードできます これらの文を次に実行する際にSQL 計画ベースラインが使用されます Oracle Database 11g の SQL Plan Management 7

ステージング表からのベースライン計画のインポート新しいアプリケーション モジュールの配置は 完全に新しいSQL 文がデータベースに導入されることを意味します サード パーティのソフトウェア ベンダーは Oracle Database 11gと導入される新しいSQLの適切なSQL 計画ベースラインを含むアプリケーション ソフトウェアを出荷できます これによって SQL 計画ベースラインの一部であるすべてのSQL 文が標準のテスト構成で適切なパフォーマンスを提供する計画で最初に実行されます 別の方法として アプリケーションが内部で開発またはテストされる場合 テスト システム用に現在の計画をエクスポートして DBMS_SPM.UNPACK_STGTAB_BASELINE 関数またはOracle Enterprise Managerを使用して本番システムにインポートできます 図 3 新しいアプリケーションを実装する場合のテスト システムからの SQL 計画ベースラインのインポート Oracle Database 11g の SQL Plan Management 8

計画選択 SQL 文をコンパイルするたびに オプティマイザは まず従来のコストベースの検索方法を使用して 最適なコスト計画を構築します 初期化パラメータの OPTIMIZER_USE_PLAN_BASELINESがTRUE( デフォルト値 ) に設定されている場合 コストベースの計画を実行する前に オプティマイザは SQL 文の計画ベースラインから一致する計画を検索します メモリー内の処理として実行されるので アプリケーションに測定可能なオーバーヘッドは発生しません 一致する計画がある場合 その計画で続行します 一致する計画がない場合 新しく生成された計画が計画履歴に追加されます この計画は SQL 計画ベースラインとして承認される前に検証する必要があります 新しく生成された計画を実行する代わりに オプティマイザは SQL 文の承認された各 SQL 計画ベースラインのコストを見積もって もっとも低コストのものを選択します (1 つの計画には 特定の文に対して複数の検証済み計画および承認済み計画を使用できます ) ただし 索引の削除などのシステム変更によって承認された計画がすべて再作成できなくなる場合 オプティマイザは 新しく生成されたコストベースの計画を使用します 図 4 SPM で SQL 実行計画を選択する方法 SQL 文の計画ベースラインから計画を選択している場合 オプティマイザの計画の選択に影響する可能性もあります SQL 計画ベースラインは 固定として記録できます 固定 SQL 計画ベースラインは 任意のオプティマイザを示します Oracle Database 11g の SQL Plan Management 9

オプティマイザが SQL 計画ベースラインのコストを見積もり 計画のいずれかが固定されている場合 オプティマイザは 固定計画だけを見積もり 再作成可能な場合にその計画を使用します 固定計画が再作成できない場合 オプティマイザは 戻って残りの SQL 計画ベースラインを見積もり もっとも低コストのものを選択します 計画の見積もりは 厳密な解析のように高価ではありません オプティマイザは すべての可能なアクセス方法ではなく単一の特定のアクセス パスを参照します 計画の展開オプティマイザはSQL 文の新しい計画を検出すると SQL 計画ベースラインに追加する前に検証または展開する必要のある未承認の計画として その計画が計画履歴に追加されます Oracle Enterprise Managerまたはコマンドライン関数の DBMS_SPM. EVOLVE_SQL_PLAN_BASELINEを使用して SQL 文の実行計画を展開できます このいずれかの方法を使用して 新しい計画に対して選択した計画ベースラインと同等またはそれ以上のパフォーマンスかどうかを評価します 十分なパフォーマンスの場合 新しい計画は承認された計画としてSQL 計画ベースラインに追加されます パフォーマンスが不足している場合 新しい計画は 未承認の計画として計画履歴に残ります フォーマットされたテキスト レポートは 関数によって返されます これには 新しい計画と元のSQL 計画ベースラインのパフォーマンス統計を並べた表示と 関数によって実行されたアクションが含まれます SQL MANAGEMENT BASE の使用および管理 初期化パラメータ SQL Plan Management を制御する 2 つの新しい init.ora パラメータがあります optimizer_capture_sql_plan_baselinesは 繰り返し可能なSQL 文のSQL 計画ベースラインの自動取得を制御します Oracle Database 11g Release 1 のデフォルト値は falseです optimizer_use_sql_plan_baselines は SQL 計画ベースラインの使用を制御します 有効な場合 オプティマイザは コンパイルされる SQL 文の SQL 計画ベースラインを検索します 検出されると オプティマイザは 各計画ベースラインを見積もって もっとも低コストのものを選択します Oracle Database 11g Release 1 のデフォルト値は true です SQL Management Base の使用領域の管理ステートメント ログ 計画履歴 およびSQL 計画ベースラインは SQL Management Baseに保存されます SQL Management Baseは SYSAUX 表領域に保存されるデータベース ディクショナリの一部です SQL Management Baseのデフォルトの領域制限は SYSAUX 表領域のサイズの 10% までです ただし PL/SQL プロシージャのDBMS_SPM.CONFIGUREを使用して 1~50% の間に制限を変更できます 週単位のバックグラウンド プロセスは SQL Management Baseが占有する領域を測定します 定義された制限を超える場合 プロセスはアラート ログに警告を生成します Oracle Database 11g の SQL Plan Management 10

SQL Management Base 内の SPM によって 使用されるディスク領域を管理する週単位のスケジュールのパージング タスクもあります このタスクは メンテナンス ウィンドウで自動的に実行されます 53 週以上使用されていない計画がパージされるので 1 年に 1 度実行される SQL 文は保護されます DBMS_SPM.CONFIGURE または Oracle Enterprise Manager を使用して 未使用の計画の保存期間を変更できます 値の範囲は 5~523 週 (10 年と少し ) です SQL Management Base が SYSAUX 表領域に保存されるため この表領域が使用できない場合 SPM は使用されません 図 5 Oracle Enterprise Manager の計画保存設定の変更 SQL PLAN MANAGEMENT の監視 Oracle Database 11g の SQL Plan Management 機能を監視するため 複数の新しい Oracle Enterprise Manager 画面と DBA ビューが導入されました Oracle Enterprise Manager SQL 計画ベースラインのあらゆる管理と監視は Oracle Enterprise Manager Database Control を通じて実行できます 最初に SQL 計画ベースライン ページに移動する手順 1. Oracle Enterprise Manager の Database ホームページにアクセスします 2. ページ上部の Server をクリックして Server ページを表示します 3. Query Optimizer セクションの SQL Plan Control をクリックします 4. SQL Plan Control ページが表示されます このページの詳細については オンライン ヘルプを参照してください 5. ページ上部の SQL Patch をクリックして SQL 計画ベースライン サブページを表示します Oracle Database 11g の SQL Plan Management 11

図 6 Oracle Enterprise Manager Database Control の SQL 計画ベースライン ホームページ メイン ページで init.ora パラメータの制御 ロードのスケジュール ジョブの 展開 および既存の SQL 計画ベースラインのすべての属性の変更を行うことがで きます init.ora パラメータ値の変更メインの SQL 計画ベースライン ページの左上に SQL Plan Management を制御するパラメータを一覧表示する設定セクションがあります このセクションに素早く目を通すと 自動計画取得がオンかどうか または計画ベースラインが使用されているかどうかがわかります init.ora パラメータの値を変更する手順 1. パラメータの値をクリックします 2. 初期化パラメータ ページが開きます ドロップダウン メニューからパラメータを変更する値を選択します 3. OK をクリックします 図 7 Oracle Enterprise Manager の SPM init.ora パラメータの設定 Oracle Database 11g の SQL Plan Management 12

計画のバルク ロード右側の SQL 計画ベースラインのリストの上にあるロード ボタンを使用して カーソル キャッシュから計画を直接ロードできます カーソル キャッシュのすべての文の計画をロードできます または 計画のサブセットを選択できます 1. Load ボタンをクリックします 2. SQL 計画ベースラインのロード ページが表示されます "Load Plans from cursor cache" のラジオ ボタンを選択します 3. 1 つ以上の SQL_ID を手動で入力するか 懐中電灯アイコンをクリックしてカーソル キャッシュの各計画の SQL_ID と SQL のリストを表示します 4. SQL_ID を選択した後 ジョブ スケジュール情報 ( ただちにデフォルト ロード ) を設定します 5. OK をクリックします 図 8 Oracle Enterprise Manager のカーソル キャッシュからの SQL 計画ベースラインのバルク ロード 属性の変更 SQL 計画ベースラインのメイン ページから 計画ベースラインの属性を変更できます 属性を変更する手順 Oracle Database 11g の SQL Plan Management 13

1. 計画ベースラインのチェック ボックスをクリックします 2. 変更する属性ボタンをクリックします 3. 選択内容を確認するダイアログ ボックスが表示されます OK をク リックします SQL 計画ベースラインの実行計画の参照 SQL 計画ベースラインの実際の実行計画を参照するには 計画名をクリックします 特定の SQL 文の SQL 計画ベースラインの実行計画を参照するには SQL テキストをクリックします 未承認の計画の展開 SQL 計画ベースラインのメイン ページから 承認済みの計画と未承認の計画を確認できます 未承認の計画を展開する場合 1. 計画のチェック ボックスをクリックし リストの上の展開ボタンを選択します 2. SQL 計画ベースラインの展開ページが 3 つのラジオ ボタン オプションとともに開きます a. Verify Performance - 未承認の計画が既存のSQL 計画ベースラインと同等またはそれ以上のパフォーマンスであることを確認する場合は YESを選択します 未承認の計画が十分なパフォーマンスであることをすでに把握していてチェックを省略する場合は NOを選択します b. Time Limit - Verify PerformanceでYesを選択した場合にのみ適用されます Autoは 未承認の計画のパフォーマンスを検証する期間をOracleが決定することを意味します Unlimitedは 計画検証プロセスを最後まで実行することを意味します Specifyは 計画検証プロセスの時間制限を設定する必要があることを意味します c. Action - 新しい計画を自動的に承認するか または新しい計画を承認するかどうかの決定に基づく検証プロセスの結果をレポートします 3. OK をクリックします 4. SQL 計画ベースラインのメイン ページが表示されます ページの右上のジョブ セクションに表示された展開ジョブを確認できます ( 必要に応じて 更新ボタンをクリックします ) Oracle Database 11g の SQL Plan Management 14

図 9 計画の展開 DBA ビューを使用した SPM の監視 DBA_SQL_PLAN_BASELINES ビューは 特定の SQL 文に対して現在作成されてい る SQL 計画ベースラインの情報を表示します Select signature, plan_id, sql_text, plan_name, origin, enabled, accepted, fixed, auto_purge From dba_sql_plan_baselines; 上記の SELECT 文は 以下の行を返します SIGNATURE PLAN_ID SQL_TEXT PLAN_NAME ORIGIN ENA ACC FIX AUT --------- ------- ---------- ------------- ------- --- --- --- --- 8.062E+18 8992199 select... SYS_SQL_PLAN_1ea AUTO-CAP YES NO NO YES 8.062E+18 3803407 select... SYS_SQL_PLAN_4be AUTO-CAP YES YES NO YES この例の SQL 文には 自動的に取得された 2 つの計画があります 計画の 1 つ (SYS_SQL_PLAN_4be) は 承認されている有効な計画なので計画ベースラインです もう 1 つの計画 (SYS_SQL_PLAN_1ea) は 展開または検証のために格納されている未承認の計画です 検証するために自動的に取得され 格納されています 承認 (accepted) 列の値は NO に設定されています どちらの計画も固定ではなく 自動パージされる可能性があります また V$SQLビューをDBA_SQL_PLAN_BASELINESビューに結合できます Oracle Database 11g の SQL Plan Management 15

Select s.sql_text, b.plan_name, b.origin, b.accepted From dba_sql_plan_baselines b, v$sql s Where s. exact_matching_signature = b.signature And s.sql_plan_baseline = b.plan_id; 自動的な SQL チューニングの統合 Oracle Database 11g のチューニングおよび診断パックの一部である SQL Tuning Advisor は メンテナンス ウィンドウで自動的に実行されます この自動 SQL チューニング タスクは 高負荷な SQL 文を対象とします このような文は Automatic Workload Repository(AWR) スナップショットで収集された実行パフォーマンス データによって識別されます SQL Tuning Advisor は SQL 文のより良い実行計画を検出すると SQL プロファイルを作成します これらの高負荷な SQL 文の一部では SQL 計画ベースラインがすでに作成されている可能性があります 自動 SQL チューニング タスクによる SQL プロファイルの推奨事項が実装される場合 承認された SQL 計画ベースラインとしてこのプロファイルが追加されます また 特定の SQL 文に SQL Tuning Set を作成して 手動で SQL Tuning Advisor を呼び出すことができます SQL Tuning Advisor が文の SQL プロファイルを推奨して手動で実装される場合 承認済みの計画として SQL 文の計画ベースラインが存在する場合にこのプロファイルが追加されます 結論 Oracle Database 11g の新機能である SQL Plan Management(SPM) は 実行計画の展開を制御します SPM を使用すると オプティマイザは 実行計画を自動的に管理し 既知の計画または検証済みの計画だけを使用できます SQL 文に新しい計画がある場合 現在の計画と同等かそれ以上のパフォーマンスを実現する計画であることが検証されるまで使用されません Oracle Database 11g の SQL Plan Management 16

Oracle Database 11g の SQL Plan Management 2007 年 6 月著者 :Maria Colgan 共著者 :Oracle Corporation Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065 U.S.A. 海外からのお問合せ窓口 : 電話 :+1.650.506.7000 ファクシミリ :+1.650.506.7200 www.oracle.com Copyright 2007, Oracle.All rights reserved. 本文書は情報提供のみを目的として提供されており ここに記載される内容は予告なく変更されることがあります 本文書は一切間違いがないことを保証するものではなく さらに 口述による明示または法律による黙示を問わず 特定の目的に対する商品性もしくは適合性についての黙示的な保証を含み いかなる他の保証や条件も提供するものではありません オラクル社は本文書に関するいかなる法的責任も明確に否定し 本文書によって直接的または間接的に確立される契約義務はないものとします 本文書はオラクル社の書面による許可を前もって得ることなく いかなる目的のためにも 電子または印刷を含むいかなる形式や手段によっても再作成または送信することはできません Oracle JD Edwards PeopleSoft および Siebel は 米国 Oracle Corporation およびその子会社 関連会社の登録商標です その他の名称はそれぞれの会社の商標です