| Oracle® Databaseパフォーマンス・チューニング・ガイド 11gリリース2 (11.2) B56312-06 |
|
 前 |
 次 |
この章では、SQL計画の管理を使用してSQL実行計画を管理する方法について説明します。SQL計画の管理を使用すると、SQL計画の情報を取得、選択および改良するためのコンポーネントが用意されているため、SQL文の実行計画に対する突然の変更によるパフォーマンスの低下を回避できます。
この章には次の項があります。
SQL計画管理は、SQL文の実行計画を時間の経過とともに記録し、評価を行う予防メカニズムです。このメカニズムにより、承認されたSQL文の計画セット、SQL計画ベースラインを構築できます。承認された計画は、適切に機能することが証明されています。
SQL計画ベースラインの目的は、データベースの変更にかかわらず、対応するSQL文のパフォーマンスを維持することです。たとえば、次のような変更が発生する場合があります。
新しいバージョンのオプティマイザ
オプティマイザ統計およびオプティマイザ・パラメータに対する変更
スキーマ定義およびメタデータ定義に対する変更
システム設定に対する変更
SQLプロファイルの作成
SQL計画ベースラインでは、索引の削除などによる元に戻せない実行計画の変更を防止することはできません。
Oracle DatabaseのSQLチューニング機能は、オプティマイザが適切なチューニング計画を作成できるようにSQLプロファイルを生成します。ただし、このメカニズムは事後対応であり、データベースに重大な変更が生じた場合は安定したパフォーマンスを保証できません。SQLチューニングは、パフォーマンスの問題が発生して特定された後にしか解決できません。たとえば、計画が変更されてSQL文の負荷が高くなった場合でも、SQLチューニングでは計画の変更が発生した後にのみ問題を解決できます。
SQL計画の管理によってSQLパフォーマンスを向上または維持できる一般的なシナリオは、次のとおりです。
新しいバージョンのオプティマイザをインストールするデータベースのアップグレードでは、通常、ほんのわずかなSQL文に対して計画の変更が発生します。ほとんどの計画の変更においてパフォーマンスに変化や向上は見られません。ただし、一部の計画の変更は、パフォーマンスの低下の原因となることがあります。SQL計画ベースラインにより、アップグレードがもたらす低下の可能性を最小限に抑えられます。
継続的なシステムおよびデータの変更は、一部のSQL文の計画に影響を及ぼし、パフォーマンスの低下を招く可能性があります。SQL計画ベースラインにより、パフォーマンスの低下を最小限に抑えて安定したSQLパフォーマンスを維持できます。
新しいアプリケーション・モジュールのデプロイメントは、新しいSQL文をデータベースに導入することを意味します。アプリケーション・ソフトウェアは、新しい文に対して標準のテスト構成で作成された適切なSQL実行計画を使用できます。システム構成がテスト構成と大幅に異なる場合、データベースはSQL計画ベースラインを時間の経過とともに進化させてパフォーマンスを改善します。
SQL計画ベースラインには、次の情報を含む1つ以上の承認済の計画が含まれます。
ヒント・セット
計画のハッシュ値
計画に関連する情報
計画履歴は、時間の経過とともにSQL文に対して生成された承認済および未承認の計画のセットです。SQL計画ベースラインには承認された計画のみが含まれるため、ベースラインの計画は計画履歴のサブセットです。たとえば、SQL計画ベースラインの最初の承認計画がオプティマイザによって生成された場合、それ以降の計画は計画履歴の一部になりますが、計画ベースラインには含まれません。
SQL計画ベースラインに計画を追加するプロセスが、計画の改良です。計画の改良の対象とするには、オプティマイザによる計画の使用を有効にしておく必要があります。
図15-1では、SQL計画ベースラインに、1つのSELECT文に対する2つの承認済の計画があります。SQL計画履歴には、適切に機能することが立証されていない他の2つの計画が含まれます。
SQL管理ベース(SMB)は、データ・ディクショナリの一部で、SYSAUX表領域でSQL計画ベースラインと計画履歴を格納します。SMBはSQLプロファイルも含みます。SMBでは、自動領域管理を使用しています。
SQL計画ベースラインの取得フェーズでは、データベース管理者が計画を改良(検証)することができるように、計画の変更が検出されて新しい計画が記録されます。そのために、データベースでは各SQL文の計画履歴を管理します。非定型SQL文は繰り返し使用されず、パフォーマンスが低下することはないため、データベースでは繰返し可能なSQL文のみの計画履歴を管理します。
繰返し可能なSQL文を認識するため、データベースはオプティマイザによって評価された様々なSQL文のSQL IDを含む文ログを維持します。データベースは、SQL文がログに記録された後に再度解析または実行されると、そのSQL文を繰返し可能と認識します。
繰返し可能な各SQL文は、オプティマイザによって生成されたすべての計画が格納された計画履歴で管理されます。計画履歴にあるすべての承認済の計画セットがSQL計画ベースラインです。
繰返し可能なSQL文の計画履歴およびSQL計画ベースラインを自動で取得するようにSQL計画ベースラインの取得フェーズを構成できます。または、計画をSQL計画ベースラインとして手動でロードすることもできます。
この項では、次の項目について説明します。
計画の自動取得を有効にすると、オプティマイザが提供する情報を使用して、SQL文の計画履歴が自動的に作成され管理されます。計画履歴には、実行計画を再作成するためにオプティマイザが使用する関連情報(SQLテキスト、アウトライン、バインド変数、コンパイル環境など)が含まれます。
オプティマイザは、SQL文の最初の計画を承認済としてマークし、それを計画履歴およびSQL計画ベースラインとして追加します。計画履歴にはそれ以降のすべての計画が含まれます。SQL計画ベースラインの改良フェーズでは、データベースはパフォーマンスの低下が生じないことが検証された計画をベースラインに追加します。
計画の自動取得を使用可能にするには、OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初期化パラメータをTRUEに設定します。デフォルトでは、このパラメータはFALSEです。
SQL計画ベースラインを作成するには、既存のSQL文セットの計画を計画ベースラインとして手動でロードする方法もあります。データベースは手動でロードされた計画のパフォーマンスについて検証しませんが、既存または新規のSQL計画ベースラインに容認された計画として追加します。計画の手動ロードは、計画の自動取得とともに使用するか、自動取得のかわりに使用することもできます。
手動で計画をロードするには、次の操作を実行します。
SQLチューニング・セットから計画をロードするには、DBMS_SPMパッケージのLOAD_PLANS_FROM_SQLSETファンクションを使用します。次の例では、tset1というSQLチューニング・セットに保存された計画をロードします。
DECLARE my_plans PLS_INTEGER; BEGIN my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'tset1'); END; /
自動ワークロード・リポジトリ(AWR)から計画をロードするには、LOAD_PLANS_FROM_SQLSETファンクションを使用する前に、AWRスナップショットに保存された計画をSQLチューニング・セットにロードします。詳細はこの項で後述します。
|
関連項目:
|
共有SQL領域から計画をロードするには、次のようにDBMS_SPMパッケージのLOAD_PLANS_FROM_CURSOR_CACHEファンクションを使用します。次の例では、sql_idで識別されるSQL文について共有SQL領域で検索された計画がOracle Databaseによってロードされます。
DECLARE my_plans PLS_INTEGER; BEGIN my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '99twu5t2dn5xd'); END; /
共有SQL領域の計画を識別するには、次の要素を使用します。
SQL識別子(SQL_ID)
SQLテキスト(SQL_TEXT)
次のいずれかの属性
PARSING_SCHEMA_NAME
MODULE
ACTION
|
関連項目: LOAD_PLANS_FROM_CURSOR_CACHEファンクションの使用方法の詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
SQL計画ベースラインの選択フェーズでは、格納された計画履歴に基づいて計画の変更を検出し、SQL文セットのパフォーマンスが低下する可能性を回避する計画を選択します。
各SQL文のコンパイル時に、オプティマイザは次のことを行います。
コストベースの検索方法を使用してコストが最適な計画を構築します。
SQL計画ベースラインで一致する計画を検索します。
一致する計画が見つかったかどうかに応じて、次のいずれかを行います。
見つかった場合、オプティマイザは一致する計画を使用します。
見つからなかった場合は、オプティマイザはSQL計画ベースラインの容認された計画ごとにコストを評価し、コストが最も低い計画を選択します。
SQL文の計画履歴に一致する計画が見つからなかった場合、最良のコスト計画は新しい計画として追加されます。データベースはこの計画を未承認の計画として計画履歴に追加します。新しい計画は、パフォーマンスを低下させないことが検証されるまで、使用されません。しかし、システムの変更(削除された索引など)によって、承認済の計画がすべて再生不可能になった場合、オプティマイザは最適なコスト計画を選択します。このように、SQL計画ベースラインが存在するために、オプティマイザはSQL文に対して慎重な計画の選択方法を使用することになります。
SQL計画ベースラインを使用可能にするには、OPTIMIZER_USE_SQL_PLAN_BASELINES初期化パラメータをTRUEに設定します(デフォルト)。
SQL計画ベースラインの改良フェーズでは、新しい計画のパフォーマンスを評価し、より優れたパフォーマンスの計画をSQL計画ベースラインに組み込みます。
オプティマイザがSQL文の新しい計画を見つけた場合、その計画は未承認の計画として計画履歴に追加されます。データベースはSQL計画ベースラインのパフォーマンスと比較してその計画のパフォーマンスを検証します。承認されていない計画の検証では、承認されていない計画のパフォーマンスとSQL計画ベースラインから選択された計画のパフォーマンスを比較し、承認されていない計画のパフォーマンスの方が良好であることが確認されると、検証が成功します。未承認の計画がパフォーマンスの低下の原因にならないことが検証されると、承認された計画に変更され、ベースラインに統合されます。
この項では、SQL計画ベースラインの改良方法について説明します。この項には、次の項目があります。
既存のSQL計画ベースラインは、共有SQL領域またはSQLチューニング・セットから手動でロードして計画を改良できます。計画を手動でSQL計画ベースラインにロードすると、ロードされた計画は、承認された計画として追加されます。
PL/SQLファンクションDBMS_SPM.EVOLVE_SQL_PLAN_BASELINEは、オプティマイザが既存の計画ベースラインの計画履歴に追加した新しい計画の改良を試みます。ファンクションにより、新規計画のパフォーマンスの方が対応するSQL計画ベースラインから選択された計画より優れていると判断されると、その新規計画は承認された計画として追加されます。
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINEファンクションの次の例では、SQLハンドルで識別されるSQL文の新しい計画を改良します。このSQLハンドルは文字列形式の一意のSQL識別子です。SQLハンドルは、DBA_SQL_PLAN_BASELINES.SQL_HANDLEの問合せによって見つけられます。
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SYS_SQL_593bc74fca8e6738');
DBMS_OUTPUT.PUT_LINE(report);
END;
/
次の出力は、Oracle Databaseによって計画が正常に改良されたことを示します。
REPORT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
--------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_593bc74fca8e6738
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SYS_SQL_PLAN_ca8e6738a57b5fc2
-----------------------------------
Plan was verified: Time used .07 seconds.
Passed performance criterion: Compound improvement ratio >= 7.32.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 40 40
Elapsed Time(ms): 23 8 2.88
CPU Time(ms): 23 8 2.88
Buffer Gets: 450 61 7.38
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.
または、DBMS_SPM.EVOLVE_SQL_PLAN_BASELINEを使用して次のものを指定できます。
改良する特定の計画の名前
改良する計画のリスト
値なし
値を指定しないで、SMBで現在容認されていないすべての計画を改良できます。
|
関連項目: DBMS_SPM.EVOLVE_SQL_PLAN_BASELINEファンクションの使用方法の詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
SQLチューニング・アドバイザでSQL文をチューニングする場合、アドバイザによって、チューニングされた計画のパフォーマンスが対応するSQL計画ベースラインから選択された計画より優れていると判断されると、SQLプロファイルを受け入れるよう推奨事項が作成されます。SQLプロファイルが受け入れられると、データベースはチューニング済の計画を対応するSQL計画ベースラインに追加します。ただし、SQLチューニング・アドバイザは、計画履歴にある既存の未承認の計画を検証しません。
Oracle Database 11gでは、メンテナンス期間中に自動構成されたタスクによってSQLチューニング・アドバイザが実行されます。このタスクの対象は高負荷SQL文です。高負荷SQL文は、自動ワークロード・リポジトリ(AWR)スナップショットで収集される実行パフォーマンス・データによって識別されます。自動SQLチューニング・タスクは、SQLチューニング・アドバイザによって作成されたSQLプロファイルの推奨事項を実装します。このため、チューニング済の計画は、識別された高負荷SQL文のSQL計画ベースラインに自動的に追加されます。
SQL計画ベースラインにFIXED属性がYESに設定された使用可能な計画が1つでも含まれる場合、そのSQL計画ベースラインは固定です。固定SQL計画ベースラインは、任意のSQL文に対応する使用可能な計画のセット(通常は1つの計画)を固定する場合や、固定計画としてアウトライン計画をロードすることにより既存のストアド・アウトラインを移行する場合に使用できます。
固定SQL計画ベースラインに固定されていない計画も含まれる場合、オプティマイザは、固定されていない計画より固定計画を優先します。このため、オプティマイザは、固定されていない計画のコストの方が低い場合でも、最小コストの固定計画を選択します。再生可能な固定計画が存在しない場合は、固定されていない計画のうち最良のものを選択します。
オプティマイザは、固定SQL計画ベースラインに新規計画を追加しません。オプティマイザによって新しい計画が自動的に追加されないため、DBMS_SPM.EVOLVE_SQL_PLAN_BASELINEを実行する際に、固定SQL計画ベースラインは改良されません。ただし、新規計画を共有SQL領域またはSQLチューニング・セットから手動で固定SQL計画ベースラインにロードして改良することができます。
SQLチューニング・アドバイザを使用して固定SQL計画ベースラインを持つSQL文をチューニングする場合、SQLプロファイルの推奨は特別な意味を持ちます。SQLプロファイルが受け入れられると、チューニング済の計画は、固定されていない計画として固定SQL計画ベースラインに追加されます。ただし、前述のとおり、再生可能な固定計画が存在する場合、オプティマイザはチューニング済の計画を使用しません。したがって、SQLチューニングのメリットは実現されない可能性があります。チューニング済の計画を使用可能にするには、FIXED属性をYESに設定し、チューニング済の計画を手動で固定計画に変更します。
特定の文についてSQL計画ベースラインに保存された計画を表示するには、DBMS_XPLANパッケージのDISPLAY_SQL_PLAN_BASELINEファンクションを使用します。次の例では、ハンドル(sql_handle)で指定したSQL文について1つ以上の実行計画を表示します。
SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_209d10fabbedc741',
format=>'basic'));
または、計画名(plan_name)を指定して1つの計画を表示することもできます。
このファンクションは、SQL管理ベースに保存された計画の情報を使用して計画の詳細を表示します。この例では、DISPLAY_SQL_PLAN_BASELINEファンクションは、ハンドルSYS_SQL_209d10fabbedc741で指定されたSQL文の実行計画を表示します。
SQL handle: SYS_SQL_209d10fabbedc741
SQL text: select cust_last_name, amount_sold from customers c,
sales s where c.cust_id=s.cust_id and cust_year_of_birth=:yob
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_bbedc741a57b5fc2
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
----------------------------------------------------------------------------------
Plan hash value: 2776326082
----------------------------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS |
| 3 | BITMAP CONVERSION TO ROWIDS | |
| 4 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX |
| 5 | PARTITION RANGE ALL | |
| 6 | TABLE ACCESS FULL | SALES |
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_bbedc741f554c408
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
----------------------------------------------------------------------------------
Plan hash value: 4115973128
----------------------------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS |
| 4 | BITMAP CONVERSION TO ROWIDS | |
| 5 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX |
| 6 | PARTITION RANGE | |
| 7 | BITMAP CONVERSION TO ROWIDS | |
| 8 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX |
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID | SALES |
----------------------------------------------------------------------------------
また、次の例に示すように、DBA_SQL_PLAN_BASELINESビューに対してSELECT文を直接使用してSQL計画ベースラインの情報を表示することもできます。
SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED FROM DBA_SQL_PLAN_BASELINES; SQL_HANDLE PLAN_NAME ENA ACC FIX ------------------------------------------------------------------------ SYS_SQL_209d10fabbedc741 SYS_SQL_PLAN_bbedc741a57b5fc2 YES NO NO SYS_SQL_209d10fabbedc741 SYS_SQL_PLAN_bbedc741f554c408 YES YES NO
|
関連項目: DISPLAY_SQL_PLAN_BASELINEファンクションで使用する追加のパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
SQL管理ベース(SMB)は、SYSAUX表領域にあるデータ・ディクショナリの一部です。SMBには、文のログ、計画履歴、SQL計画ベースラインおよびSQLプロファイルが格納されます。週次で未使用の計画およびログを消去できるようにするには、SMBで自動領域管理を使用します。
また、一連のSQL文について計画をSMBに手動で追加することもできます。この機能を使用すると、新しいバージョンのオプティマイザを使用して計画が低下することを最小限に抑えることができるため、Oracle Database 11gより前のバージョンからデータベースをアップグレードする場合に特に便利です。
SMB全体がSYSAUX内に存在するため、この表領域が使用可能でない場合、データベースはSQL計画管理およびSQLチューニング機能を使用しません。
SMBで使用されるディスク領域は、SYSAUX表領域のサイズに基づいた制限と定期的に照合されます。デフォルトでは、SMBの制限はSYSAUXのサイズの10%程度です。この制限の許容範囲は1から50%です。
週に1回のバックグラウンド・プロセスによって、SMBが使用する総領域が測定されます。定義した制限を超過すると、アラート・ログに警告が書き込まれます。次のいずれかの条件を満たすまで、毎週アラートが生成されます。
SMB領域の上限が上げられた場合
SYSAUX表領域のサイズが増やされた場合
SQL管理オブジェクト(SQL計画ベースラインまたはSQLプロファイル)を消去してSMBで使用されるディスク領域が減らされた場合
制限の比率を変更するには、次のようにDBMS_SPMパッケージのCONFIGUREプロシージャを使用します。次の例では、領域の制限が30%に変更されます。
BEGIN
DBMS_SPM.CONFIGURE('space_budget_percent',30);
END;
/
|
関連項目: CONFIGUREプロシージャで使用する追加のパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
週次にスケジュールされた消去タスクにより、SQL計画の管理で使用されるディスク領域は管理されます。このタスクは、メンテナンス期間に自動化タスクとして実行されます。
データベースは使用されていない期間が53週を超える計画を消去します。LAST_EXECUTED消去対象は、SMBに保存されたその計画のLAST_EXECUTEDタイムスタンプで識別されます。53週という期間により、年次のSQL処理の間、計画の情報は使用可能であることが保証されます。未使用計画の保存期間は、5から523週(約10年)です。
保存期間を構成するには、次のようにDBMS_SPM PL/SQLパッケージのCONFIGUREプロシージャを使用します。次の例では、保存期間が105週間に変更されます。
BEGIN DBMS_SPM.CONFIGURE( 'plan_retention_weeks',105); END; /
|
関連項目: CONFIGUREプロシージャで使用する追加のパラメータの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
SQL管理ベースの現在の構成設定は、DBA_SQL_MANAGEMENT_CONFIGビューを使用して表示できます。次の問合せにより、この情報が表示されます。
SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG; PARAMETER_NAME PARAMETER_VALUE ------------------------------ --------------- SPACE_BUDGET_PERCENT 30 PLAN_RETENTION_WEEKS 105
Oracle Databaseでは、Oracle Data Pump ImportおよびOracle Data Pump Exportユーティリティを使用したSQL計画ベースラインのエクスポートとインポートをサポートしています。DBMS_SPMパッケージを使用して、SQL計画ベースラインの圧縮および解凍に使用できるステージング表を定義します。
システム間で一連のSQL計画ベースラインをインポートするには、次のようにします。
元のデータベースで、次のようにCREATE_STGTAB_BASELINEプロシージャを使用してステージング表を作成します。
次の例では、stage1というステージング表が作成されます。
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'stage1');
END;
/
SQL管理ベースからエクスポートするSQL計画ベースラインを、次のようにPACK_STGTAB_BASELINEファンクションを使用してステージング表に圧縮します。
次の例では、ユーザーdba1が作成した有効な計画ベースラインをステージング表stage1に圧縮します。SQL計画ベースラインは、計画名(plan_name)、SQLハンドル(sql_handle)またはその他の計画基準を使用して選択できます。table_nameパラメータは必須です。
DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'stage1',
enabled => 'yes',
creator => 'dba1');
END;
/
Oracle Data Pump Exportユーティリティを使用して、ステージング表stage1をフラット・ファイルにエクスポートします。
このフラット・ファイルをターゲット・システムに送信します。
Oracle Data Pump Importユーティリティを使用して、ステージング表stage1をフラット・ファイルからインポートします。
ターゲット・システムで、次のようにUNPACK_STGTAB_BASELINEファンクションを使用してSQL計画ベースラインをステージング表からSQL管理ベースに解凍します。
次の例では、ステージング表stage1に格納された固定計画ベースラインがすべて解凍されます。
DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE(
table_name => 'stage1',
fixed => 'yes');
END;
/
|
関連項目:
|
この項では、ストアド・アウトラインの移行の概要およびタスクについて説明します。この項では、次の項目について説明します。
ストアド・アウトラインは、SQL文のヒントのセットです。ヒントは、文に対する特定の計画を選択するようオプティマイザに指示します。ストアド・アウトラインは、計画の安定性を提供するためのレガシー・テクニックです。
ストアド・アウトラインの移行は、保存されたアウトラインをSQL計画ベースラインに変換するためにユーザーが開始するプロセスです。SQL計画ベースラインは、優れたパフォーマンスを提供することが立証された計画のセットです。
この項では、次の項目について説明します。
この項では、計画の安定性を維持し、パフォーマンスの低下を防止するために、ストアド・アウトラインに依存していることが前提となっています。この項の目的は、ストアド・アウトラインからSQL計画ベースラインに安全に移行する便利な方法を提供することです。移行した後、ストアド・アウトラインを使用した場合と同じ計画の安定性を維持し、SQL計画管理フレームワークで提供された拡張機能を使用できます。
この項では、特に、次の問題の対処方法を説明します。
ストアド・アウトラインは、時間の経過とともに自動的に進化しません。したがって、ストアド・アウトラインは、作成した時点では最適であっても、データベースの変更後に劣悪な計画となってパフォーマンスの低下をもたらす場合があります。
ストアド・アウトラインのヒントは、無効になる場合があります(たとえば、削除された索引に対する索引ヒントなど)。この場合、データベースでは、無効なヒントを除外してアウトラインを使用し続け、多くの場合、元の計画またはオプティマイザによって生成される現在の最適なコスト計画より劣悪な計画が生じます。
SQL文では、現在指定されているカテゴリでストアド・アウトラインに定義された計画のみを選択できます。パフォーマンスが向上する場合でも、異なるカテゴリのストアド・アウトラインからの選択、または現在のコストベースの計画は選択できません。
ストアド・アウトラインは、事後対応型のチューニング・テクニックです。したがって、パフォーマンス問題が発生した後にのみストアド・アウトラインを使用して問題に対処します。たとえば、高負荷になったSQL文の計画を修正するストアド・アウトラインを実装できます。この場合、高負荷になる前に予防的に文をチューニングせずに、ストアド・アウトラインを使用します。
ストアド・アウトライン移行PL/SQL APIは、次のように前述の問題を解決するのに役立ちます。
SQL計画ベースラインにより、オプティマイザが同じ最適な計画を使用し、時間の経過とともに計画を進化させることができます。
指定したSQL文の新しい計画は、パフォーマンスの低下が生じないことを検証した後にSQL計画ベースラインとして追加できます。
SQL計画ベースラインは、無効なヒントのために計画が劣悪になることを防止します。
計画ベースラインに格納されたヒントが無効になった場合、その計画は再生可能な計画ではありません。この場合は、代替の再生可能な計画ベースラインまたはオプティマイザによって生成された現在の最適なコスト計画が選択されます。
特定のSQL文に対する複数の計画ベースラインを管理できます。
オプティマイザは、特定のSQL文の計画を最適な計画のセットから選択できるため、ストアド・アウトラインでのカテゴリごとに1つの計画といった要件に制限されることはありません。
この項では、ストアド・アウトラインをSQL計画ベースラインに移行する方法について説明します。この情報は、ストアド・アウトラインの移行タスクの実行に関する重要な情報です。
次の図は、ストアド・アウトラインの移行における主なステージを示しています。
移行プロセスには、次のステージがあります。
ユーザーが、移行するアウトラインを指定するファンクションを起動します。
データベースでアウトラインが次のように処理されます。
データベースが計画ベースラインに必要なアウトラインの情報をコピーします。
データベースでは、情報を直接コピーするか、アウトラインの情報に基づいて計算します。たとえば、SQL文のテキストは両方のスキーマに存在するので、テキストをアウトラインからベースラインにコピーできます。
データベースでは、ヒントを再解析してアウトラインにない情報を取得します。
計画のハッシュ値および計画のコストは、アウトラインの既存情報から得られないため、ヒントの再解析が必要です。
データベースがベースラインを作成します。
データベースでは、初めてSQL計画ベースラインを選択して同じSQL文を実行する際に、不足している情報を取得します。
コンパイル環境および実行統計は、実行する際に、計画ベースラインを解析およびコンパイルするときにのみ取得できます。
これらのフェーズが完了した後、移行が完了します。
アウトラインはヒントのセットであり、SQL計画ベースラインは計画のセットです。これらは異なる技術であるため、アウトラインの一部の機能は、ベースラインの機能に正確にマップされません。たとえば、1つのSQL文に、カテゴリの異なる複数のアウトラインが存在する可能性がありますが、現在ベースラインに存在するカテゴリはDEFAULTのみです。
アウトラインのカテゴリは、SQL計画ベースラインのモジュールに相当します。表15-1に、アウトラインのカテゴリがどのようにモジュールにマップするかを説明します。
表15-1 アウトラインのカテゴリ
| 概念 | 説明 | デフォルト値 |
|---|---|---|
|
アウトライン・カテゴリ |
ユーザーにより定義されたストアド・アウトライン・セットの分類。 カテゴリを使用して、ストアド・アウトラインの異なるSQL文を管理できます。たとえば、1つの文が、 各SQL文には、1つ以上のストアド・アウトラインがあります。各ストアド・アウトラインは、1つのカテゴリにのみ分類されます。1つの文にはカテゴリの異なる複数のストアド・アウトラインがありますが、各文には1つのカテゴリにつき1つのストアド・アウトラインのみ存在します。 移行の際に、各アウトライン・カテゴリは、SQL計画ベースライン・モジュールにマップされます。 |
|
|
ベースライン・モジュール |
実行されている高度な機能。 SQL計画ベースラインは、1つのモジュールにのみ所属できます。 |
アウトラインのSQL計画ベースラインへの移行後、デフォルトのモジュール名はアウトライン・カテゴリ名です。 |
|
ベースライン・カテゴリ |
SQL計画ベースラインには、カテゴリが1つのみ存在します。それは 1つの文は、 |
|
Oracle Databaseでは、ストアド・アウトラインをSQL計画ベースラインに移行する際に、すべてのアウトライン・カテゴリを同名のSQL計画ベースライン・モジュールにマップします。次の図に示すように、アウトラインのOLTPカテゴリは、ベースラインのOLTPモジュールにマップされます。移行後、DEFAULTは、すべてのSQL計画ベースラインを含むスーパー・カテゴリです。
DBMS_SPMパッケージを使用してストアド・アウトラインを移行できます。表15-2に、このパッケージの関連ファンクションを示します。
表15-2 ストアド・アウトラインの移行に関連するDBMS_SPMファンクション
| DBMS_SPMファンクション | 説明 |
|---|---|
|
|
既存のストアド・アウトラインを計画ベースラインに移行します。 次のいずれかの形式を使用します。
|
|
|
SQL文に関連付けられた1つの計画またはすべての計画の属性を変更します。 |
|
|
SQL計画ベースラインに移行されたストアド・アウトラインを削除します。
|
ストアド・アウトラインおよび計画ベースラインの動作は、初期化パラメータおよびセッション・パラメータで制御できます。表15-3に、関連パラメータを示します。これらのパラメータ設定の相互作用の説明は、表15-5および表15-6を参照してください。
表15-3 ストアド・アウトラインの移行に関連するパラメータ
| 初期化またはセッション・パラメータ | 説明 |
|---|---|
|
|
セッション中に発行された各問合せのアウトラインを自動的に作成して保存するかどうかを決定します。 |
|
|
繰返し可能なSQL文の自動認識およびそれらの文のSQL計画ベースラインの生成を有効または無効にします。 |
|
|
オプティマイザが実行計画の生成にストアド・アウトラインを使用するかどうかを決定します。 注意: これは初期化パラメータではなく、セッション・パラメータです。 |
|
|
SQL管理ベースに格納されたSQL計画ベースラインの使用を有効または無効にします。 |
データベース・ビューを使用して、ストアド・アウトラインの移行に関連する情報にアクセスできます。表15-4に、主なビューを示します。
表15-4 ストアド・アウトラインの移行に関連するビュー
| ビュー | 説明 |
|---|---|
|
|
データベースのすべてのストアド・アウトラインについて説明します。
|
|
|
特定のSQL文に関して現在作成されているSQL計画ベースラインの情報を表示します。
|
|
関連項目:
|
この項では、PL/SQL APIを使用してストアド・アウトラインを移行する場合の基本ステップを示します。基本ステップは、次のとおりです。
ストアド・アウトラインの移行の準備をします。
移行の前提条件を確認し、移行後の計画ベースラインの動作を決定します。
「ストアド・アウトラインの移行の準備」を参照してください。
次のいずれかを実行します。
ベースラインに移行して、SQL計画管理の機能を使用します。
「アウトラインの移行によるSQL計画管理機能の使用」を参照してください。
ストアド・アウトラインの動作を正確に維持して、ベースラインに移行します。
「アウトラインの移行でのストアド・アウトライン動作の保存」を参照してください。
移行後の確認およびクリーンアップを実行します。
「ストアド・アウトラインの移行後のフォーローアップ・タスクの実行」を参照してください。
この項では、ストアド・アウトラインの移行の準備について説明します。
ストアド・アウトラインの移行の準備を行うには、次の手順を実行します。
SQL*Plusを起動し、SYSDBA権限またはDBMS_SPMパッケージのEXECUTE権限を持つユーザーとしてログオンします。
たとえば、オペレーティング・システム認証を使用してデータベースにSYSとしてログオンするには、次のコマンドを実行します。
% sqlplus /nolog SQL> CONNECT / AS SYSDBA
データベースのストアド・アウトラインの問合せを実行します。
次の例では、SQL計画ベースラインに移行されていないすべてのストアド・アウトラインを問い合せます。
SELECT NAME, CATEGORY, SQL_TEXT FROM DBA_OUTLINES WHERE MIGRATED = 'NOT-MIGRATED';
どのストアド・アウトラインが次の前提条件を満たして移行の対象となるかを判断します。
文は、ランタイムのINSERT AS SELECT文ではない。
文は、リモート・オブジェクトを参照していない。
この文は、プライベート・ストアド・アウトラインではない。
すべてのアウトラインを移行するか、指定したストアド・アウトラインか、指定したアウトライン・カテゴリに属するアウトラインかを決定します。
一部のアウトラインを移行する場合は、移行するアウトラインまたはカテゴリをリストします。
SQL計画ベースラインに移行するストアド・アウトラインで固定計画を使用するか、非固定計画かを決定します。
固定計画
固定計画は、固定されています。固定計画が、計画ベースラインに格納されたヒントを使用して再生可能な計画である場合、オプティマイザは、固定でない計画ベースラインよりも最小コストの固定計画ベースラインを常に選択します。本質的に、固定計画ベースラインは、有効なヒントを含むストアド・アウトラインとして機能します。
計画ベースラインに格納されたヒントに基づいて文を解析し、計画ベースラインと同じ計画ハッシュ値を使用して計画を作成できる場合、固定計画は再生可能です。1つ以上のヒントが無効になった場合、同じ計画ハッシュ値を使用して計画を作成することはできません。この場合、計画は非再生可能です。
ヒントを使用して解析したとき、固定計画を再生できなかった場合、オプティマイザは別の計画を選択します。次のいずれかを選択できます。
SQL計画ベースラインの別の計画
オプティマイザによって作成された現在のコストベースの計画
場合によっては、計画が異なるためにパフォーマンスが低下し、SQLチューニングが必要になります。
非固定計画
計画ベースラインに固定計画が含まれていない場合、SQL計画管理は、SQL文の計画を選択する際に、計画を同等に検討します。
実際の移行を開始する前に、Oracleデータベースが次の前提条件を満たしていることを確認します。
データベースはEnterprise Editionである。
データベースはオープンしている必要があり、一時停止状態であってはならない。
データベースは、制限付きアクセス(DBA)、読取り専用または移行モードであってはならない。
OCIが使用可能である。
|
関連項目:
|
このタスクの目的は、次のとおりです。
移行後、SQL文に対して同じ固定計画を適用するのではなく、SQL計画管理で計画ベースラインのすべての計画から選択できるようにする。
ベースラインに新しい計画を追加して、データベースの変更があった場合にSQL計画ベースラインが進化できるようにする。
この項のシナリオは、次のことが前提となっています。
すべてのアウトラインを移行する。
特定のアウトラインを移行する場合、DBMS_SPM.MIGRATE_STORED_OUTLINEファンクションの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
ベースラインのモジュール名と移行するアウトラインのカテゴリ名を同じ名前にする。
SQL計画を固定しない。
デフォルトでは、生成される計画は固定ではなく、SQL計画管理では、SQL文の計画を選択する際にすべての計画を同等に検討します。この状況により、計画を改良する拡張機能では、SQL文の新しい計画を取得し、パフォーマンスを検証して新しい計画を計画ベースラインに受け入れることが可能になります。
ストアド・アウトラインをSQL計画ベースラインに移行するには、次の手順を実行します。
SQL*Plusで、PL/SQLファンクションMIGRATE_STORED_OUTLINEをコールします。
次のPL/SQLブロック・サンプルは、すべてのストアド・アウトラインを固定ベースラインに移行します。
DECLARE my_report CLOB; BEGIN my_report := DBMS_SPM.MIGRATE_STORED_OUTLINE( attribute_name => 'all' ); END; /
|
関連項目:
|
このタスクの目標は、ストアド・アウトラインをSQL計画ベースラインに移行し、固定計画ベースラインを作成して元のストアド・アウトラインの動作を保持することです。固定計画は、同じSQL文に対する他の計画よりも優先順位が高くなります。計画が固定されている場合、計画ベースラインは進化できません。計画ベースラインに固定計画が含まれていると、新しい計画は追加されません。
この項では、次のことが前提となっています。
firstrowというカテゴリのストアド・アウトラインのみを移行する。
DBMS_SPM.MIGRATE_STORED_OUTLINEファンクションの構文およびセマンティクスは、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
ベースラインのモジュール名と移行するアウトラインのカテゴリ名を同じ名前にする。
ストアド・アウトラインを計画ベースラインに移行するには、次の手順を実行します。
SQL*Plusで、PL/SQLファンクションMIGRATE_STORED_OUTLINEをコールします。
次のPL/SQLブロック・サンプルは、firstrowカテゴリのストアド・アウトラインを固定ベースラインに移行します。
DECLARE
my_report CLOB;
BEGIN
my_outlines := DBMS_SPM.MIGRATE_STORED_OUTLINE(
attribute_name => 'category',
attribute_value => 'firstrow',
fixed => 'YES' );
END;
/
移行後、SQL計画ベースラインのモジュールはfirstrow、カテゴリはDEFAULTです。
|
関連項目:
|
このタスクの目的は、次のとおりです。
SQL計画ベースラインに移行されたストアド・アウトラインで、ストアド・アウトラインではなく、計画ベースラインを使用するようにデータベースを構成する。
ストアド・アウトラインではなく、SQL計画ベースラインを作成して、今後のSQL文で使用する。
SQL計画ベースラインに移行されたストアド・アウトラインを削除する。
この項では、次のことが前提となっています。
ストアド・アウトラインの移行の基本ステップが完了している。
一部のストアド・アウトラインは、Oracle Database 10gより前のリリースで作成されている可能性がある。
Oracle Database 10gより前のリリースでは、ローカル・ヒント形式のヒントを使用しています。移行後、計画ベースラインに格納されたヒントは、Oracle Database 10gで導入されたグローバル・ヒント形式を使用します。
この項では、ストアド・アウトラインおよび計画ベースラインに関連する初期化パラメータの設定について説明します。OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESおよびCREATE_STORED_OUTLINES初期化パラメータは、ストアド・アウトラインおよびSQL計画ベースラインを作成する方法および時期を指定します。表15-5に、これらのパラメータ間の相互作用を示します。
表15-5 アウトラインおよびベースラインの作成
| CREATE_STORED_OUTLINES初期化パラメータ | OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初期化パラメータ | データベースの動作 |
|---|---|---|
|
|
|
SQL文を実行する際に、ストアド・アウトラインもSQL計画ベースラインも作成されません。 |
|
|
|
繰返し可能なSQL文の自動認識およびこれらの文のSQL計画ベースラインの生成は有効です。SQL文を実行する際に、その文に対して |
|
|
|
セッション中に発行された各問合せのアウトラインが自動的に作成され、保存されます。SQL文を実行する際に、その文に対して |
|
|
|
SQL文を実行する際に、その文に対して指定されたカテゴリ名の新規ストアド・アウトラインのみが作成されます(存在しない場合)。 |
|
|
|
セッション中に発行された各問合せのアウトラインが自動的に作成され、保存されます。繰返し可能なSQL文の自動認識およびこれらの文のSQL計画ベースラインの生成も有効です。 SQL文を実行する際に、 |
|
|
|
セッション中に発行された各問合せのアウトラインが自動的に作成され、保存されます。繰返し可能なSQL文の自動認識およびこれらの文のSQL計画ベースラインの生成も有効です。 SQL文を実行する際に、指定されたカテゴリ名のストアド・アウトラインが作成され、 |
USE_STORED_OUTLINESセッション・パラメータ(これは初期化パラメータではない)およびOPTIMIZER_USE_SQL_PLAN_BASELINES初期化パラメータは、ストアド・アウトラインおよび計画ベースラインの使用方法を指定します。表15-6に、これらのパラメータの相互作用を示します。
表15-6 ストアド・アウトラインおよびSQL計画ベースラインの使用方法
| USE_STORED_OUTLINESセッション・パラメータ | OPTIMIZER_USE_SQL_PLAN_BASELINES初期化パラメータ | データベースの動作 |
|---|---|---|
|
|
|
SQL文の計画を選択する際に、ストアド・アウトラインも計画ベースラインも使用しません。 |
|
|
|
SQL文の計画を選択する際に、SQL計画ベースラインのみを使用します。 |
|
|
|
SQL文の計画を選択する際に、 |
|
|
|
SQL文の計画を選択する際に、指定されたカテゴリ名のストアド・アウトラインを使用します。 指定されたカテゴリ名のストアド・アウトラインが存在しない場合は、 |
|
|
|
SQL文の計画を選択する際に、計画ベースラインよりもストアド・アウトラインが優先されます。 その文に |
|
|
|
SQL文の計画を選択する際に、計画ベースラインよりもストアド・アウトラインが優先されます。 その文に、指定されたカテゴリ名または |
移行後に、データベースを適切な状態に設定するには、次の手順を実行します。
移行によって、SQL計画ベースラインが作成されたことを確認します。
計画が使用可能であり、承認されていることを確認します。たとえば、次の問合せを入力します(サンプル出力の一部が含まれます)。
SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, MODULE FROM DBA_SQL_PLAN_BASELINES; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX MODULE ------------------------------ ---------- -------------- --- --- --- ------ SYS_SQL_f44779f7089c8fab STMT01 STORED-OUTLINE YES YES NO DEFAULT . . .
オプションとして、SQL計画ベースラインの属性を変更します。
たとえば、次の文を使用して、指定したSQL文のベースラインのステータスをfixedに変更します。
DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_f44779f7089c8fab',
attribute_name=>'FIXED',
attribute_value=>'NO');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/
元のストアド・アウトラインのステータスを確認します。
たとえば、次の問合せを入力します(サンプル出力の一部が含まれます)。
SELECT NAME, OWNER, CATEGORY, USED, MIGRATED FROM DBA_OUTLINES ORDER BY NAME; NAME OWNER CATEGORY USED MIGRATED ---------- ---------- ---------- ------ ------------ STMT01 SYS DEFAULT USED MIGRATED STMT02 SYS DEFAULT USED MIGRATED . . .
SQL計画ベースラインに移行されたすべてのストアド・アウトラインを削除します。
たとえば、次の文を使用して、DBA_OUTLINESのMIGRATEDステータスのすべてのストアド・アウトラインを削除します。
DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.DROP_MIGRATED_STORED_OUTLINE();
DBMS_OUTPUT.PUT_LINE('Migrated stored outlines dropped: ' || v_cnt);
END;
/
初期化パラメータを次のように設定します。
SQL文を実行する際に、計画ベースラインを作成し、ストアド・アウトラインを作成しないように設定します。
同等のSQL計画ベースラインが存在しない場合、ストアド・アウトラインのみを使用するように設定します。
たとえば、次のSQL文は、SQL文を実行する際に、ストアド・アウトラインではなくSQL計画ベースラインを作成するようデータベースに指示します。また、allrowsまたはDEFAULTカテゴリのストアド・アウトラインは、それが存在し、SQL計画ベースラインに移行されていない場合にのみ適用するようデータベースに指示しています。それ以外の場合は、かわりにSQL計画ベースラインを適用します。
ALTER SYSTEM SET CREATE_STORED_OUTLINE = false; ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true; ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES = true; ALTER SESSION SET USE_STORED_OUTLINES = allrows;
|
関連項目:
|
