| Oracle® Database SQLチューニング・ガイド 12c リリース1 (12.1) B71277-09 |
|
 前 |
 次へ |
SQL計画ベースラインは、DBMS_SPMパッケージを使用して管理できます。
この章の内容は次のとおりです。
関連項目:
SQL計画管理は、データベースで既知の計画または確認済の計画のみが使用されるようにオプティマイザで実行計画を自動的に管理する予防的なメカニズムです。
SQL計画管理では、SQL計画ベースラインと呼ばれるメカニズムを使用します。計画ベースラインは、SQL文に対してオプティマイザで使用できる一連の承認済の計画です。通常のユースケースの場合、データベースで計画の正常な実行が検証された後のみ、計画が計画ベースラインに承認されます。このコンテキストで、計画には、オプティマイザで実行計画を再現するために必要な計画に関連するすべての情報(たとえば、SQL計画識別子、一連のヒント、バインド値、オプティマイザ環境など)が含まれます。
SQL計画管理の主要コンポーネントは次のとおりです。
計画の取得
このコンポーネントは、一連のSQL文に対する計画に関連する情報を保存します。「計画の取得」を参照してください。
計画の選択
このコンポーネントは、保存された計画履歴に基づいたオプティマイザによる計画変更の検出、およびパフォーマンスが低下する可能性を回避するための適切な計画の選択でのSQL計画ベースラインの使用です。「計画の選択」を参照してください。
計画の展開
このコンポーネントは、手動または自動での、既存のSQL計画ベースラインへの新規計画の追加プロセスです。「計画の展開」を参照してください。
この項の内容は次のとおりです。
SQL計画管理の主な目的は、計画の変更によって発生するパフォーマンスの低下を防ぐことです。2つ目の目的は、計画変更を検証し、パフォーマンスを改善させる計画変更のみを承認することにより、新しいオプティマイザ統計や索引などの変更にスムーズに適応することです。
注意:
SQL計画ベースラインでは、索引の削除などによる元に戻せない実行計画の変更を防止することはできません。
SQL計画の管理により、データベース・アップグレードおよびシステムとデータの変更でSQLパフォーマンスを向上または維持できます。
特に、次の利点が得られます。
新しいバージョンのオプティマイザをインストールするデータベースのアップグレードでは、通常、ほんのわずかなSQL文に対して計画の変更が発生します。
ほとんどの計画変更において、パフォーマンスの向上が見られるか、または変化がありません。ただし、一部の計画の変更は、パフォーマンスの低下の原因となることがあります。SQL計画ベースラインにより、アップグレードがもたらす低下の可能性を最小限に抑えられます。
アップグレード時には、データベースにより計画ベースラインからの計画のみが使用されます。データベースは現在のベースラインに含まれない新しい計画を保持領域に入れ、後でそれらを評価し、ベースラインに含まれる現在の計画よりも少ないリソースを使用するかどうかを判断します。それらの計画のパフォーマンスがより優れている場合は、それらの計画をベースラインに昇格させます。そうでない場合は昇格させません。
継続的なシステムおよびデータの変更は、一部のSQL文の計画に影響を及ぼし、パフォーマンスの低下を招く可能性があります。
SQL計画ベースラインにより、パフォーマンスの低下を最小限に抑えて安定したSQLパフォーマンスを維持できます。
新しいアプリケーション・モジュールのデプロイメントは、新しいSQL文をデータベースに導入します。
アプリケーション・ソフトウェアは、新しい文に対して標準のテスト構成で作成された適切なSQL実行計画を使用できます。システム構成がテスト構成と大幅に異なる場合、データベースはSQL計画ベースラインを時間の経過とともに進化させてパフォーマンスを改善します。
関連項目:
Oracleデータベースのアップグレード方法については、『Oracle Databaseアップグレード・ガイド』を参照してください。
SQLプロファイルとSQL計画ベースラインのどちらも、オプティマイザで最適な計画のみが使用されるようにすることによりSQL文のパフォーマンスを向上させます。
プロファイルとベースラインはどちらも、ヒントを使用して内部的に実装されます。ただし、これらのメカニズムは次のような点で大きく異なります。
一般的に、SQL計画ベースラインは事前対応型であるのに対し、SQLプロファイルは事後対応型です。
通常、SQL計画ベースラインは重大なパフォーマンスの問題が発生する前に作成します。SQL計画ベースラインは、オプティマイザが最適ではない計画を将来的に使用することを防ぎます。
SQLプロファイルは、SQLチューニング・アドバイザを起動するとデータベースにより作成されます。通常、チューニング・アドバイザは、SQL文により高負荷の兆候が示された後にのみ起動します。SQLプロファイルは主に、最適ではない計画につながったオプティマイザのミスを継続的に解決するために使用できます。SQLプロファイル・メカニズムは事後対応であるため、データベースに重大な変更が生じた場合は安定したパフォーマンスを保証できません。
SQL計画ベースラインは特定の計画を再現するのに対し、SQLプロファイルはオプティマイザのコスト見積りを修正します。
SQL計画ベースラインは一連の承認済計画から構成されます。各計画は、特定の計画を完全に指定するアウトライン・ヒントのセットを使用して実装されます。SQLプロファイルもヒントを使用して実装されますが、これらのヒントでは特定の計画は指定されません。これらのヒントは、最適ではない計画につながったオプティマイザの見積りの計算ミスを修正します。たとえば、ヒントにより表のカーディナリティ予測が修正されます。
プロファイルによりオプティマイザは特定の1つの計画に制限されないため、SQLプロファイルはSQL計画ベースラインよりも柔軟です。たとえば、初期化パラメータやオプティマイザ統計を変更することで、オプティマイザはより最適な計画を選択できます。
SQLチューニング・アドバイザを使用することをお薦めします。これにより、SQLプロファイルと計画ベースラインのどちらが各SQL文に最適なメカニズムであるのかを自分で判断せずに、アドバイザの推奨事項に従うことができます。
SQL計画の取得とは、一連のSQL文に対して、計画に関連する情報を取得し、SQL管理ベースに保存するための方法を指します。計画の取得とは、SQL計画管理にこの計画を認識させることを意味します。
初期化パラメータを設定することにより初期計画の取得が自動的に発生するように構成するか、DBMS_SPMパッケージを使用して計画を手動で取得できます。
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初期化パラメータをtrueに設定することで(デフォルトはfalse)、初期計画の自動取得を有効にします。有効時には、データベースで実行される繰返し可能なSQL文のSQL計画ベースラインが自動的に作成されます。
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESとOPTIMIZER_USE_SQL_PLAN_BASELINESの設定は独立しています。たとえば、OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESがtrueの場合、OPTIMIZER_USE_SQL_PLAN_BASELINESの設定がtrueとfalseのいずれでも、初期計画ベースラインが作成されます。
関連項目:
OPTIMIZER_USE_SQL_PLAN_BASELINES初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください
SQL計画管理では、計画の手動取得とは、ユーザーによるSQL計画ベースラインへの既存計画の一括ロードを指します。
SQL文に対する実行計画をSQLチューニング・セット(STS)、共有SQL領域、ステージング表またはストアド・アウトラインからロードするには、Cloud ControlまたはPL/SQLを使用します。
次の図は、SQL計画ベースラインへの計画のロードを示しています。
ロード動作は、一括ロードに含まれる各文に対してSQL計画ベースラインが存在するかどうかによって異なります。
文に対するベースラインが存在しない場合、データベースは次の処理を行います。
文に対する計画履歴と計画ベースラインを作成します。
文に対する最初の計画を承認済とマークします。
計画を新しいベースラインに追加します。
文に対するベースラインが存在する場合、データベースは次の処理を行います。
ロードした計画を承認済とマークします。
計画のパフォーマンスを検証せずに、計画を文に対する計画ベースラインに追加します。
オプティマイザは管理者により手動でロードされた計画のパフォーマンスは許容範囲にあると見なすため、手動でロードされた計画は常に承認済とマークされます。
関連項目:
DBMS_SPM.LOAD_PLANS_FROM_%ファンクションの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
SQL 計画の選択とは、保存された計画履歴に基づいて計画変更を検出するオプティマイザの機能、およびパフォーマンスが低下する可能性を回避するために適切な計画を選択するSQL計画ベースラインの使用を指します。
データベースがSQL文のハード解析を実行すると、オプティマイザは最適なコスト計画を生成します。デフォルトでは、オプティマイザは次に、文に対するSQL計画ベースライン内で一致する計画を探します。計画ベースラインが存在しない場合は、データベースは最適なコスト計画を使用して文を実行します。
計画ベースラインが存在する場合は、オプティマイザの動作は、新たに生成された計画が計画ベースラインに含まれるかどうかによって決まります。
新しい計画がベースラインに含まれる場合、データベースは検出した計画を使用して文を実行します。
新しい計画がベースラインに含まれない場合、オプティマイザは新たに生成された計画を未承認とマークし、計画履歴に追加します。オプティマイザの動作は、計画ベースラインの内容によって決まります。
計画ベースラインに固定計画が含まれる場合、オプティマイザはコストが最も低い固定計画を使用します。
計画ベースラインに固定計画が含まれない場合、オプティマイザはコストが最も低いベースライン計画を使用します。
計画ベースライン内に再現可能な計画が含まれない場合(これはベースライン内のすべての計画が削除された索引を参照している場合に発生します)、オプティマイザは新たに生成されたコストベースの計画を使用します。
関連項目:
"固定計画"
一般的に、SQL計画の展開は、オプティマイザが新しい計画を検証して既存のSQL計画ベースラインに追加するためのプロセスです。
具体的には、計画の展開は次の個別のステップで構成されます。
承認されていない計画がSQL計画ベースラインに含まれる承認済の計画と少なくとも同じパフォーマンスであることを検証します(計画の検証と呼ばれます)。
データベースにより承認済の計画と同じパフォーマンスであることが証明された場合、承認されていない計画を承認済の計画として計画ベースラインに追加します。
計画の展開の標準的なケースでは、オプティマイザは前述の手順を順番に実行します。したがって、オプティマイザがSQL計画ベースラインと比較して計画のパフォーマンスを検証するまで、SQL計画管理で新しい計画は使用できません。ただし、 一方の手順を実行せずにもう一方の手順を実行するようにSQL計画管理を構成できます。次の図は、計画の展開で使用可能なパスを示しています。
通常、SQL文のSQL計画ベースラインは、単一の承認済の計画から開始します。ただし、様々な条件の異なる計画とともに実行されると、SQL文の実行が良くなる場合があります。
たとえば、値が異なる選択性になるバインド変数を使用したSQL文は、いくつかの最適な計画を持つ場合があります。マテリアライズド・ビューまたは索引の作成あるいは表の再パーティション化により、現在の計画が他の計画よりコストが高くなる場合があります。
新しい計画がSQL計画ベースラインに一度も追加されなかった場合、一部のSQL文のパフォーマンスが低下する可能性があります。したがって、新しい承認済の計画をSQL計画ベースラインに展開する必要がある場合があります。計画の展開は、SQL計画ベースラインに含める前に新しい計画のパフォーマンスを検証して、パフォーマンスの低下を防止します。
DBMS_SPMパッケージは、計画の展開で使用できるプロシージャとファンクションを提供します。
これらのサブプログラムはタスク・インフラストラクチャを使用します。たとえば、CREATE_EVOLVE_TASKは展開タスクを作成し、EXECUTE_EVOLVE_TASKはそれを実行します。すべてのタスク展開サブプログラムの名前には、文字列EVOLVE_TASKが含まれます。
展開プロシージャを必要に応じて使用するか、サブプログラムが自動的に実行されるように構成します。自動メンテナンス・タスクSYS_AUTO_SPM_EVOLVE_TASKは、スケジュールされているメンテナンス・ウィンドウで毎日実行されます。このタスクにより次の処理が自動的に実行されます。
承認されていない計画を選択し、検証用にランク付けします。
パフォーマンスしきい値を満たしていれば、各計画を承認します。
関連項目:
DBMS_SPMパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
SQL計画管理インフラストラクチャでは、解析された文のシグネチャおよび承認済の計画と未承認の計画の両方が記録されます。
この項の内容は次のとおりです。
SQL管理ベース(SMB)は、データ・ディクショナリ内の論理リポジトリです。
SMBには、次のものが含まれます。
SQL文のログ(SQL IDのみを含む)
SQL計画履歴(SQL計画ベースラインを含む)
SQLプロファイル
SQLパッチ
SMBには、SQLパフォーマンスの維持または向上のためにオプティマイザで使用できる情報が保存されます。
SMBはSYSAUX表領域内にあり、自動セグメント領域管理を使用します。SMB全体がSYSAUX表領域内に存在するため、この表領域が使用可能でない場合、データベースはSQL計画管理およびSQLチューニング機能を使用しません。
注意:
SMBをプラガブル・データベースとともに使用すると、データの可視性と権限の要件が一致しない場合があります。コンテナ・データベース(CDB)内での管理機能の動作をまとめた表が記載されている『Oracle Database管理者ガイド』を参照してください。
関連項目:
SYSAUX表領域の詳細は、『Oracle Database管理者ガイド』を参照してください。
SQL計画の自動取得が有効になっている場合、SQL文のログには、オプティマイザがこれまでに評価してきた文のシグネチャが含まれます。
SQLシグネチャは、大/小文字および空白が正規化されているSQL文のテキストを使用して計算されたハッシュ値です。オプティマイザは文を解析する際、シグネチャを作成します。
自動取得中、データベースはこのシグネチャをSQL文のログ(SQLLOG$)に照らして照合し、シグネチャが以前に検出されているかどうかを判別します。検出されていない場合、データベースはシグネチャをログに追加します。シグネチャがすでにログに存在する場合、データベースは文が繰返し可能なSQL文であることを確認します。
注意:
フィルタによって文が除外された場合は、そのシグネチャもログから除外されます。
例23-1 SQL文のロギング
この例は、データベースがどのように文のログ内で文を追跡し、繰返し可能な文に対して自動的にベースラインを作成するかを示しています。文のログの最初の問合せでは、追跡されたSQL文は1つもないことが示されます。AD_PRESに対するhr.jobsの問合せ後、ログには文が1つ追跡されていることが示されます。
SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true; System altered. SQL> SELECT * FROM SQLLOG$; no rows selected SQL> SELECT job_title FROM hr.jobs WHERE job_id = 'AD_PRES'; JOB_TITLE ----------------------------------- President SQL> SELECT * FROM SQLLOG$; SIGNATURE BATCH# ---------- ---------- 1.8096E+19 1
次に、セッションは別のjobs問合せを実行します。ログには2つの文が追跡されていることが示されます。
SQL> SELECT job_title FROM hr.jobs WHERE job_id='PR_REP'; JOB_TITLE ----------------------------------- Public Relations Representative SQL> SELECT * FROM SQLLOG$; SIGNATURE BATCH# ---------- ---------- 1.7971E+19 1 1.8096E+19 1
DBA_SQL_PLAN_BASELINESの問合せでは、どちらの文に対してもベースラインが存在しないことが示されます。これは、どちらの文も繰返し可能ではないためです。
SQL> SELECT SQL_HANDLE, SQL_TEXT 2 FROM DBA_SQL_PLAN_BASELINES 3 WHERE SQL_TEXT LIKE 'SELECT job_title%'; no rows selected
セッションがjob_id='PR_REP'に対する問合せを再度実行します。これでこの文は繰返し可能になり、またSQL計画の自動取得が有効になっているため、データベースによりこの文に対する計画ベースラインが作成されます。job_id='AD_PRES'に対する問合せは一度しか実行されていないため、これに対する計画ベースラインは存在しません。
SQL> SELECT job_title FROM hr.jobs WHERE job_id='PR_REP';
JOB_TITLE
-----------------------------------
Public Relations Representative
SQL> SELECT SQL_HANDLE, SQL_TEXT
2 FROM DBA_SQL_PLAN_BASELINES
3 WHERE SQL_TEXT LIKE 'SELECT job_title%';
SQL_HANDLE SQL_TEXT
-------------------- --------------------
SQL_f9676a330f972dd5 SELECT job_title FRO
M hr.jobs WHERE job_
id='PR_REP'
関連項目:
DBA_SQL_PLAN_BASELINESの詳細は、『Oracle Databaseリファレンス』を参照してください。
SQL計画履歴は、取得された一連のSQL実行計画です。この履歴には、SQL計画ベースラインおよび未承認の計画が含まれます。
SQL計画管理では、データベースが既存のSQL計画ベースラインの新しいSQL実行計画を検出し、展開(検証)できるように新しい計画を履歴に記録します。展開はデータベースによって自動的に開始されるか、DBAが手動で開始します。
Oracle Database 12c以降、すべてのSQL文の実行計画はSMBによりSQL計画履歴に保存されます。DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEファンクションは、SMBから計画をフェッチして表示します。Oracle Database 12cより前に作成された計画については、SMBに保存されていないため、ファンクションはSQL文をコンパイルして計画を生成する必要があります。
関連項目:
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEファンクションの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
有効な計画は、オプティマイザの使用対象となります。
enabledパラメータをYES (デフォルト)に設定して計画をロードした場合、データベースは結果のSQL計画ベースラインを、承認されていない場合でも、自動的に有効とマークします。有効な計画を手動で無効な計画に変更できます。この場合、計画が承認済でもオプティマイザはその計画を使用できなくなります。
承認済の計画は、SQL文のSQL計画ベースラインに存在し、オプティマイザで使用できる計画です。承認済の計画には、ヒント・セット、計画のハッシュ値、計画に関連するその他の情報が含まれます。
SQL文に対する計画履歴には、すべての計画(承認済と未承認の両方の計画)が含まれます。オプティマイザが計画ベースライン内に最初の承認済の計画を生成すると、それ以降のすべての承認されていない計画は計画履歴に追加され、検証を待ちますが、SQL計画ベースラインには追加されません。
固定計画は、優先としてマークされた承認済の計画です。これにより、オプティマイザはベースライン内の固定計画のみを考慮するようになります。固定計画は、オプティマイザの計画選択プロセスに影響します。
ある文に対するSQL計画ベースラインに3つの計画が存在するとします。オプティマイザに、その内の2つの計画のみを優先させたいとします。次の図に示すように、この2つの計画を固定とマークすることにより、オプティマイザがこれらの計画からの最適な計画のみを使用し、残りの計画は無視するようにします。
1つ以上の有効な固定計画を含むベースラインに新しい計画を追加した場合、それらの計画を手動で固定と宣言しないかぎり、オプティマイザはそれらの計画を使用することはできません。
DBMS_SPMパッケージには、Cloud Controlまたはコマンドラインを通してアクセスします。
Cloud Controlの「SQL計画管理」ページは、SQLプロファイル、SQLパッチおよびSQL計画ベースラインに関する情報を示すGUIです。
「SQL計画ベースライン」ページにアクセスする手順は次のとおりです。
適切な資格証明を使用してCloud Controlにログインします。
「ターゲット」メニューの下で、「データベース」を選択します。
データベース・ターゲットのリストで、管理対象のOracle Databaseインスタンスのターゲットを選択します。
データベースの資格証明の入力を求められた場合は、実行するタスクに必要な最小限の資格証明を入力します。
「パフォーマンス」メニューから、「SQL」、「SQL計画管理」の順に選択します。
「SQL計画管理」ページが表示されます。
「ファイル」をクリックして、図23-7に示す「SQL計画ベースライン」ページを表示します。
ほとんどのSQL計画管理タスクは、このページ、またはこのページからアクセスするページで実行できます。
関連項目:
「SQL計画ベースライン」サブページのオプションの詳細は、Cloud Control状況依存オンライン・ヘルプを参照してください。
コマンドラインでは、DBMS_SPMおよびDBMS_XPLAN PL/SQLパッケージを使用して、ほとんどのSQL計画管理タスクを実行します。
次の表に、SQL計画ベースラインを作成、削除およびロードするための主なDBMS_SPMプロシージャとファンクションを示します。
表23-1 DBMS_SPMプロシージャとファンクション
| パッケージ | プロシージャまたはファンクション | 説明 |
|---|---|---|
|
|
このプロシージャは、名前/値の形式でSMBに対する構成オプションを変更します。 |
|
|
このプロシージャは、SQL計画ベースラインを1つのデータベースから別のデータベースへと移植することを可能にするステージング表を作成します。 |
|
|
このファンクションは、計画ベースライン内の一部またはすべての計画を削除します。 |
|
|
このファンクションは、共有SQL領域(カーソル・キャッシュとも呼ばれます)内の計画をSQL計画ベースラインにロードします。 |
|
|
このファンクションは、STSに保存されている計画をSQL計画ベースラインにロードします。 |
|
|
このファンクションは、SQL計画ベースラインをパックします。つまり、SMBからステージング表にコピーします。 |
|
|
このファンクションは、SQL計画ベースラインをアンパックします。つまり、SQL計画ベースラインをステージング表からSMBにコピーします。 |
|
|
このファンクションは、SQLハンドルによって識別されるSQL文の1つ以上の実行計画を表示します。 |
関連項目:
SQL計画の展開に関連するファンクションの詳細は、「DBMS_SPM展開ファンクションについて」を参照してください。
DBMS_SPMパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
この項では、計画のパフォーマンスの低下を防ぎ、オプティマイザによる新しい計画の検討を可能にするためにSQL計画管理を使用する際の基本的なタスクについて説明します。
タスクは次のとおりです。
データベースがSQL計画ベースラインを取得して使用するかどうか、また新しい計画を展開するかどうかを制御する初期化パラメータを設定する。
「SQL計画管理の構成」を参照してください。
SQL計画ベースラインに計画を表示する。
「SQL計画ベースラインでの計画の表示」を参照してください。
計画をSQL計画ベースラインに手動でロードする。
SQLチューニング・セット、共有SQL領域、ステージング表またはストアド・アウトラインから計画をロードします。
「SQL計画ベースラインのロード」を参照してください。
計画をSQL計画ベースラインに手動で展開する。
PL/SQLを使用して、指定した計画のパフォーマンスを検証し、計画ベースラインに追加します。
「SQL計画ベースラインの手動での展開」を参照してください。
SQL計画ベースライン内の一部またはすべての計画を削除する。
「SQL計画ベースラインの削除」を参照してください。
SMBを管理する。
ディスク領域制限および計画保存ポリシーの期間を変更します。
「SQL管理ベースの管理」を参照してください。
ストアド・アウトラインをSQL計画ベースラインに移行する。
「ストアド・アウトラインのSQL計画ベースラインへの移行」を参照してください。
この項の内容は次のとおりです。
SQL計画管理は初期化パラメータを使用して制御します。
デフォルト値は次のとおりです。
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false
計画履歴に存在しない繰返し可能なSQL文の場合、その文に対する初期SQL計画ベースラインは自動的に作成されません。
OPTIMIZER_USE_SQL_PLAN_BASELINES=true
既存のSQL計画ベースラインがあるSQL文の場合、新しい計画が未承認の計画としてSQL計画ベースラインに自動的に追加されます。
注意:
前述のパラメータの設定は、互いに独立しています。たとえば、OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESがtrueの場合、OPTIMIZER_USE_SQL_PLAN_BASELINESがfalseでも、新しい文に対して初期計画ベースラインが作成されます。
デフォルト動作が希望の動作であれば、この項はスキップしてください。
関連項目:
計画履歴に存在しないSQL文に初期SQL計画ベースラインを自動的に作成するために必要な手順は、OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初期化パラメータをtrueに設定するのみです。このパラメータは、前に作成されたSQL計画ベースラインへの新しく検出された計画の自動的な追加を制御しません。
SQL計画管理に対して初期計画の自動取得を有効にする手順は次のとおりです。
注意:
自動ベースライン取得が有効になっていると、データベースはすべての再帰的SQLおよび監視SQLを含む、すべての繰返し可能な文に対してSQL計画ベースラインを作成します。したがって、自動取得により、非常に多くの計画ベースラインが作成される可能性があります。
SQL*Plusを起動し、必要な権限でデータベースにログインします。
SQL計画管理の現在の設定を表示します。
たとえば、管理者権限でSQL*Plusをデータベースに接続し、次のコマンドを実行します(出力例も示します)。
SQL> SHOW PARAMETER SQL_PLAN NAME TYPE VALUE ------------------------------------ ----------- ----- optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE
パラメータが希望どおりに設定されていれば、残りの手順はスキップしてください。
繰返し可能なSQL文の自動認識およびこれらの文に対するSQL計画ベースラインの生成を有効にするには、次の文を入力します。
SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
OPTIMIZER_USE_SQL_PLAN_BASELINES初期化パラメータをfalseに設定すると、データベースはデータベース内のいずれの計画ベースラインも使用しません。
通常は、1つまたは2つの計画ベースラインを無効化しますが、すべては無効化しません。可能なユースケースとしては、SQL計画管理の利点のテストがあげられます。
データベース内のすべてのSQL計画ベースラインを無効化する手順は次のとおりです。
SQL*Plusを適切な権限でデータベースに接続し、SQL計画管理に対する現在の設定を表示します。
たとえば、管理者権限でSQL*Plusをデータベースに接続し、次のコマンドを実行します(出力例も示します)。
SQL> SHOW PARAMETER SQL_PLAN NAME TYPE VALUE ------------------------------------ ----------- ----- optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE
パラメータが希望どおりに設定されていれば、残りの手順はスキップしてください。
すべての既存の計画ベースラインを無視するには、次の文を入力します。
SQL> ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES=false
関連項目:
SQL計画ベースライン初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。
SPM展開アドバイザは、SQL計画ベースラインに最近追加された計画を展開するSQLアドバイザです。アドバイザを使用することにより手動で実行する必要がなくなるため、計画の展開が容易になります。
SPM展開アドバイザの自動タスクに対する個別のスケジューラ・クライアントは存在しません。
1つのクライアントが、自動SQLチューニング・アドバイザと自動SPM展開アドバイザの両方を制御します。したがって、同じタスクにより両方のアドバイザが有効化または無効化されます。
関連項目:
自動SPM展開アドバイザの有効化および無効化方法については、「自動SQLチューニング・タスクの有効化と無効化」を参照してください
DBMS_SPMパッケージでは、SET_EVOLVE_TASK_PARAMETERプロシージャを使用してタスク・パラメータを指定することにより、計画の自動展開を構成できます。タスクはSYSが所有しているため、SYSのみがタスク・パラメータを設定できます。
ACCEPT_PLANSチューニング・タスク・パラメータは、推奨された計画を自動的に承認するかどうかを指定します。ACCEPT_PLANSがtrueに設定されている場合(デフォルト)、SQL計画管理はタスクにより推奨されたすべての計画を自動的に承認します。falseに設定されている場合は、タスクは計画を検証し、検証結果についてのレポートを生成しますが、計画の展開は行いません。
前提条件
この項のチュートリアルでは、次のことが前提となっています。
データベースにより自動的に計画を展開しない。
実行ごとに1200秒経過したら、タスクをタイムアウトする。
自動展開タスクのパラメータを設定する手順は次のとおりです。
SQL*Plusを適切な権限でデータベースに接続し、オプションで現在のタスク設定を問い合せます。
たとえば、管理者権限でSQL*Plusをデータベースに接続し、次の問合せを実行します。
COL PARAMETER_NAME FORMAT a25
COL VALUE FORMAT a10
SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
FROM DBA_ADVISOR_PARAMETERS
WHERE ( (TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK') AND
( (PARAMETER_NAME = 'ACCEPT_PLANS') OR
(PARAMETER_NAME = 'TIME_LIMIT') ) );
出力例は次のように表示されます。
PARAMETER_NAME VALUE ------------------------- ---------- ACCEPT_PLANS TRUE TIME_LIMIT 3600
次の形式のPL/SQLコードを使用してパラメータを設定します。
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
, parameter => parameter_name
, value => value
);
END;
/
たとえば、次のPL/SQLブロックは時間制限を20分に設定し、計画を自動的に承認します。
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
, parameter => 'LOCAL_TIME_LIMIT'
, value => 1200
);
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
, parameter => 'ACCEPT_PLANS'
, value => 'true'
);
END;
/
関連項目:
DBMS_SPMパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
特定の文についてSQL計画ベースラインに保存された計画を表示するには、DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEファンクションを使用します。このファンクションは、計画履歴に保存された計画の情報を使用して計画を表示します。
次の表は、DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEファンクションの関連するパラメータを示しています。
表23-2 DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINEパラメータ
| ファンクション・パラメータ | 説明 |
|---|---|
|
文のSQLハンドル。 |
|
文に対する計画の名前。 |
この項では、コマンドラインからベースライン内の計画を表示する方法について説明します。Cloud Controlを使用する場合は、「SQL計画ベースライン」サブページ(図23-7を参照)から計画ベースラインを表示します。
SQL計画ベースラインに計画を表示するには、次の手順を実行します。
SQL*Plusを適切な権限でデータベースに接続し、計画を表示する問合せのSQL IDを取得します。
たとえば、SQL IDが31d96zzzpcys9のSELECT文に対するSQL計画ベースラインが存在するとします。
SQL IDを使用して計画を問い合せます。
次の問合せは、SQL IDが31d96zzzpcys9の文に対する実行計画を表示します。
SELECT PLAN_TABLE_OUTPUT
FROM V$SQL s, DBA_SQL_PLAN_BASELINES b,
TABLE(
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(b.sql_handle,b.plan_name,'basic')
) t
WHERE s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE
AND b.PLAN_NAME=s.SQL_PLAN_BASELINE
AND s.SQL_ID='31d96zzzpcys9';
この問合せ例の結果は次のようになります。
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------- --------------------------------------------------------------------------- SQL handle: SQL_513f7f8a91177b1a SQL text: select * from hr.employees where employee_id=100 --------------------------------------------------------------------------- --------------------------------------------------------------------------- Plan name: SQL_PLAN_52gvzja8jfysuc0e983c6 Plan id: 3236529094 Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE --------------------------------------------------------------------------- Plan hash value: 3236529094 ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | | 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | -----------------------------------------------------
結果は、SQL ID 31d96zzzpcysに対する計画の名前はSQL_PLAN_52gvzja8jfysuc0e983c6であり、自動的に取得されたことを示しています。
関連項目:
DISPLAY_SQL_PLAN_BASELINEファンクションで使用する追加のパラメータの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
DBMS_SPMを使用すると、SQL計画ベースラインへの既存計画セットの一括ロードを開始できます。
この項の内容は次のとおりです。
SQLチューニング・セットは、1つ以上のSQL文、実行統計および実行コンテキストを含むデータベース・オブジェクトです。このトピックでは、STSから計画をロードする方法について説明します。
計画は、DBMS_SPM.LOAD_PLANS_FROM_SQLSETファンクションまたはCloud Controlを使用してロードできます。次の表では、ファンクション・パラメータの一部を説明します。
表23-3 LOAD_PLANS_FROM_SQLSETのパラメータ
| ファンクション・パラメータ | 説明 |
|---|---|
|
そこからSQL計画ベースラインに計画をロードするSTSの名前。 |
|
条件を満たす計画のみをロード対象として選択するためにSTSに適用されるフィルタ。このフィルタは、 |
|
デフォルトの |
この項では、コマンドラインから計画をロードする方法について説明します。Cloud Controlでは、「SQL計画ベースライン」サブページ(図23-7を参照)に移動し、「ロード」をクリックして、SQLチューニング・セットから計画ベースラインをロードします。
このチュートリアルでは、次のことが前提となっています。
ロードした計画は固定にしない。
次の問合せを実行済である。
SELECT /*LOAD_STS*/ * FROM sh.sales WHERE quantity_sold > 40 ORDER BY prod_id;
共有SQL領域から、ユーザーSPMにより所有され、SPM_STSという名前のSQLチューニング・セットに計画をロード済である。
SQLチューニング・セットから計画をロードする手順は次のとおりです。
SQL*Plusを適切な権限でデータベースに接続し、SQLチューニング・セットに含まれる計画を確認します。
たとえば、DBA_SQLSET_STATEMENTSでSTS名を問い合せます(出力例も示します)。
SELECT SQL_TEXT FROM DBA_SQLSET_STATEMENTS WHERE SQLSET_NAME = 'SPM_STS'; SQL_TEXT -------------------- SELECT /*LOAD_STS*/ * FROM sh.sales WHERE quantity_sold > 40 ORDER BY prod_id
出力は、select /*LOAD_STS*/文に対する計画がSTS内にあることを示しています。
STSからSQL計画ベースラインに計画をロードします。
たとえば、SQL*Plusで次のようにファンクションを実行します。
VARIABLE v_plan_cnt NUMBER
EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
sqlset_name => 'SPM_STS', -
basic_filter => 'sql_text like ''SELECT /*LOAD_STS*/%''' );
basic_filterパラメータは、関心のある問合せに対する計画のみをロードするWHERE句を指定します。変数v_plan_cntには、STSからロードされた計画の数が含まれます。
データ・ディクショナリを問い合せて、文に対するベースラインに計画がロードされたことを確認します。
次の文では、DBA_SQL_PLAN_BASELINESビューを問い合せます(出力例も示します)。
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
2 ORIGIN, ENABLED, ACCEPTED
3 FROM DBA_SQL_PLAN_BASELINES;
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
--------------------- --------------- ---------------- ----------- --- ---
SQL_a8632bd857a4a25e SELECT SQL_PLAN_ahstb MANUAL-LOAD YES YES
/*LOAD_STS*/* v1bu98ky1694fc6b
FROM sh.sales
WHERE
quantity_sold
> 40 ORDER BY
prod_id
出力は、計画が承認済であること、つまり計画ベースラインに含まれることを示しています。また、ロード元はMANUAL-LOADであり、これは計画は自動的に取得されたのではなく、エンド・ユーザーによりロードされたことを意味します。
オプションで、STSを削除します。
たとえば、DBMS_SQLTUNE.DROP_SQLSETを次のように実行して、SPM_STSチューニング・セットを削除します。
EXEC SYS.DBMS_SQLTUNE.DROP_SQLSET( sqlset_name=>'SPM_STS', sqlset_owner=>'SPM' );
関連項目:
DBA_SQL_PLAN_BASELINESビューの詳細は、『Oracle Databaseリファレンス』を参照してください
DBMS_SPM.LOAD_PLANS_FROM_SQLSETファンクションの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
このトピックでは、PL/SQLを使用してカーソル・キャッシュとも呼ばれる共有SQL領域から計画をロードする方法について説明します。
DBMS_SPMパッケージのLOAD_PLANS_FROM_CURSOR_CACHEファンクションを使用して計画をロードします。次の表では、ファンクション・パラメータの一部を説明します。
表23-4 LOAD_PLANS_FROM_CURSOR_CACHEのパラメータ
| ファンクション・パラメータ | 説明 |
|---|---|
|
SQL文の識別子。共有SQL領域内のSQL文を識別します。 |
|
デフォルトの |
この項では、コマンドラインを使用して計画をロードする方法について説明します。Cloud Controlでは、「SQL計画ベースライン」サブページ(図23-7を参照)に移動し、「ロード」をクリックして、共有SQL領域から計画ベースラインをロードします。
このチュートリアルでは、次のことが前提となっています。
次の問合せを実行済である。
SELECT /*LOAD_CC*/ * FROM sh.sales WHERE quantity_sold > 40 ORDER BY prod_id;
ロードした計画は固定にしない。
共有SQL領域から計画をロードする手順は次のとおりです。
SQL*Plusを適切な権限でデータベースに接続し、共有SQL領域内の関連する文のSQL IDを確認します。
たとえば、V$SQLでsh.sales問合せのSQL IDを問い合せます(出力例も示します)。
SELECT SQL_ID, CHILD_NUMBER AS "Child Num",
PLAN_HASH_VALUE AS "Plan Hash",
OPTIMIZER_ENV_HASH_VALUE AS "Opt Env Hash"
FROM V$SQL
WHERE SQL_TEXT LIKE 'SELECT /*LOAD_CC*/%';
SQL_ID Child Num Plan Hash Opt Env Hash
------------- ---------- ---------- ------------
27m0sdw9snw59 0 1421641795 3160571937
前述の出力は、文のSQL IDが27m0sdw9snw59であることを示しています。
指定した文に対する計画をSQL計画ベースラインにロードします。
たとえば、SQL*PlusでLOAD_PLANS_FROM_CURSOR_CACHEファンクションを実行して、SQL IDが27m0sdw9snw59の文に対する計画をロードします。
VARIABLE cnt NUMBER
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( -
sql_id => '27m0sdw9snw59');
前述の例では、変数cntにはロードされた計画の数が含まれます。
データ・ディクショナリを問い合せて、文に対するベースラインに計画がロードされたことを確認します。
次の文では、DBA_SQL_PLAN_BASELINESを問い合せます(出力例も示します)。
SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
ORIGIN, ENABLED, ACCEPTED
FROM DBA_SQL_PLAN_BASELINES;
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
--------------------- -------------------- --------------------- ----------- --- ---
SQL_a8632bd857a4a25e SELECT /*LOAD_CC*/ SQL_PLAN_gdkvzfhrgkda MANUAL-LOAD YES YES
* 71694fc6b
FROM sh.sales
WHERE quantity_sold
> 40
ORDER BY prod_id
出力は、計画が承認済であること、つまり文に対する計画ベースラインに含まれることを示しています。また、ロード元はMANUAL-LOADであり、これは文は自動的に取得されたのではなく、手動でロードされたことを意味します。
関連項目:
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHEファンクションの使用方法の詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
DBA_SQL_PLAN_BASELINESビューの詳細は、『Oracle Databaseリファレンス』を参照してください
最適な計画をソース・データベースから別の宛先データベースに転送することが必要になる場合があります。
たとえば、テスト・データベース上で一連の計画を調査し、高いパフォーマンスを発揮することを確認したとします。その後、これらの計画を本番データベースにロードすることを決定します。
ステージング表とは、計画がアンパック中に表から消えないように、その表が存在する間、計画を保存する表です。ステージング表を作成するには、DBMS.CREATE_STGTAB_BASELINEプロシージャを使用します。ステージング表のパック(行の挿入)およびアンパック(行の抽出)には、DBMS_SPMパッケージのPACK_STGTAB_BASELINEとUNPACK_STGTAB_BASELINEファンクションを使用します。Oracle Data Pump ImportとExportを使用すると、ステージング表を別のデータベースにコピーできます。
次の図は、基本的な手順を示しています。
前提条件
このチュートリアルでは、次のことが前提となっています。
ソース・データベース内にstage1という名前のステージング表を作成する。
ユーザーspmが所有するすべての計画をステージング表にロードする。
ステージング表を宛先データベースに転送する。
stage1内の計画を固定計画としてロードする。
あるデータベースから別のデータベースへとSQL計画ベースラインのセットを転送する手順は次のとおりです。
SQL*Plusを使用して適切な権限でソース・データベースにログインし、CREATE_STGTAB_BASELINEプロシージャを使用してステージング表を作成します。
次の例では、stage1というステージング表が作成されます。
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE (
table_name => 'stage1');
END;
/
ソース・データベースで、SQL管理ベースからステージング表にエクスポートするSQL計画ベースラインをパックします。
次の例では、ユーザーspmが作成した有効な計画ベースラインをステージング表stage1にパックします。計画名(plan_name)、SQLハンドル(sql_handle)またはその他の計画基準を使用して、SQL計画ベースラインを選択します。table_nameパラメータは必須です。
DECLARE
v_plan_cnt NUMBER;
BEGIN
v_plan_cnt := DBMS_SPM.PACK_STGTAB_BASELINE (
table_name => 'stage1'
, enabled => 'yes'
, creator => 'spm'
);
END;
/
Oracle Data Pump Exportを使用して、ステージング表stage1をダンプ・ファイルにエクスポートします。
ダンプ・ファイルを宛先データベースのホストに転送します。
宛先データベースで、Oracle Data Pump Importユーティリティを使用して、ステージング表stage1をダンプ・ファイルからインポートします。
宛先データベースで、SQL計画ベースラインをステージング表からSQL管理ベースにアンパックします。
次の例では、ステージング表stage1に格納された固定計画ベースラインがすべて解凍されます。
DECLARE
v_plan_cnt NUMBER;
BEGIN
v_plan_cnt := DBMS_SPM.UNPACK_STGTAB_BASELINE (
table_name => 'stage1'
, fixed => 'yes'
);
END;
/
関連項目:
DBMS_SPMパッケージの使用方法の詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
データ・ポンプ・エクスポートおよびインポート・ユーティリティの使用方法の詳細は、『Oracle Databaseユーティリティ』を参照してください。
SQL計画管理の展開タスクが自動的に実行されるように構成することをお薦めします。またPL/SQLまたはCloud Controlを使用して、承認されていない計画を手動で展開することにより、計画ベースライン内の現在のいずれの計画よりもパフォーマンスが優れているかどうかを判断することもできます。
この項の内容は次のとおりです。
関連項目:
このトピックでは、計画の展開を管理するための主なDBMS_SPMファンクションについて説明します。展開タスクは手動で実行するか、自動的に実行されるようにスケジュールします。
表23-5 計画の展開タスクを管理するためのDBMS_SPMファンクションとプロシージャ
| プロシージャまたはファンクション | 説明 |
|---|---|
|
このファンクションは、1つの計画をSQL計画ベースラインに展開するという1つの推奨事項を承認します。 |
|
このファンクションは、指定されたSQL文に対する1つ以上の計画の展開を準備するためのアドバイザ・タスクを作成します。入力パラメータは、SQLハンドル、計画名または計画名のリスト、時間制限、タスク名および説明になります。 |
|
このファンクションは展開タスクを実行します。入力パラメータは、タスク名、実行名および実行の説明になります。指定しない場合は、アドバイザによって名前が生成され、ファンクションによって戻されます。 |
|
このファンクションは展開タスクに対するすべての推奨事項を実装します。基本的にはこのファンクションは、推奨されるすべての計画に対して |
|
このファンクションは、展開タスクの結果を |
|
このファンクションは展開タスク・パラメータの値を更新します。このリリースでは、有効なパラメータは |
SPM展開アドバイザを構成して自動的に実行することをお薦めします。SQL計画ベースラインを手動で展開することもできます。次の図は、SQL計画管理タスクを管理する基本ワークフローを示しています。
通常、SQL計画の展開タスクを管理する順序は次のとおりです。
展開タスクを作成します。
オプションで、展開タスクのパラメータを設定します。
展開タスクを実行します。
タスク内の推奨事項を実装します。
タスクの結果をレポートします。
関連項目:
DBMS_SPMパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
このトピックでは、タスクを作成して実行し、その後その推奨事項を実装する典型的なユースケースについて説明します。
次の表では、CREATE_EVOLVE_TASKファンクションのパラメータの一部を説明します。
表23-6 DBMS_SPM.CREATE_EVOLVE_TASKのパラメータ
| ファンクション・パラメータ | 説明 |
|---|---|
|
文のSQLハンドル。デフォルトの |
|
計画の識別子。デフォルトの |
|
時間制限(分)。最初の承認されていない計画に対する時間制限は、入力値と等しくなります。2つ目の承認されていない計画に対する時間制限は、入力値から最初の計画の検証にかかった時間を引いた時間になります。以下同様です。デフォルトの |
|
展開タスクのユーザー指定名。 |
この項では、コマンドラインから計画ベースラインを展開する方法について説明します。Cloud Controlでは、「SQL計画ベースライン」サブページで、計画を選択してから「展開」をクリックします。
このチュートリアルでは、次のことが前提となっています。
自動展開タスクを有効にしていない(「SPM展開アドバイザのタスクの管理」を参照)。
次の問合せに対するSQL計画ベースラインを作成する。
SELECT /* q1_group_by */ prod_name, sum(quantity_sold) FROM products p, sales s WHERE p.prod_id = s.prod_id AND p.prod_category_id =203 GROUP BY prod_name;
問合せのパフォーマンスを向上させるための索引を2つ作成し、これらの索引を使用する計画のパフォーマンスが計画ベースライン内に現在含まれる計画よりも優れている場合は、その計画を展開する。
指定した計画を展開する手順は次のとおりです。
次の手順で初期設定を実行します。
SQL*Plusを管理者権限でデータベースに接続し、共有プールとバッファ・キャッシュをフラッシュすることによりチュートリアルの準備を行います。
ALTER SYSTEM FLUSH SHARED_POOL; ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL計画ベースラインの自動取得を有効にします。
たとえば、次の文を入力します。
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
ユーザーshとしてデータベースに接続し、SQL*Plusの表示パラメータを設定します。
CONNECT sh -- enter password SET PAGES 10000 LINES 140 SET SERVEROUTPUT ON COL SQL_TEXT FORMAT A20 COL SQL_HANDLE FORMAT A20 COL PLAN_NAME FORMAT A30 COL ORIGIN FORMAT A12 SET LONGC 60535 SET LONG 60535 SET ECHO ON
SQL計画管理により取得されるように、SELECT文を実行します。
SELECT /* q1_group_by */文を実行します(1回目)。
データベースは繰返し可能な文に対する計画のみを取得するため、この文に対する計画ベースラインは空になります。
データ・ディクショナリを問い合せて、計画ベースライン内に計画がないことを確認します。
たとえば、次の問合せを実行します(出力例も示します)。
SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED,
ACCEPTED, FIXED, AUTOPURGE
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE '%q1_group%';
no rows selected
SQL計画管理は繰返し可能な文に対する計画のみを取得するため、この結果は予想どおりです。
SELECT /* q1_group_by */文を実行します(2回目)。
データ・ディクショナリを問い合せて、文に対する計画ベースラインに計画がロードされたことを確認します。
次の文では、DBA_SQL_PLAN_BASELINESを問い合せます(出力例も示します)。
SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
ORIGIN, ENABLED, ACCEPTED, FIXED
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE '%q1_group%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC FIX
-------------------- ---------------- ------------------------------ ------------ --- --- ---
SQL_07f16c76ff893342 SELECT /* q1_gro SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES NO
up_by */ prod_na
me, sum(quantity
_sold) FROM
products p,
sales s WHERE
p.prod_id =
s.prod_id AND
p.prod_category
_id =203 GROUP
BY prod_name
出力は、計画が承認済であること、つまり文に対する計画ベースラインに含まれることを示しています。また、ロード元はAUTO-CAPTUREであり、これは文は手動でロードされたのではなく、自動的に取得されたことを意味します。
文に対する計画をEXPLAINし、オプティマイザによりこの計画が使用されていることを確認します。
たとえば、次のように計画をEXPLAINしてから表示します。
EXPLAIN PLAN FOR SELECT /* q1_group_by */ prod_name, sum(quantity_sold) FROM products p, sales s WHERE p.prod_id = s.prod_id AND p.prod_category_id =203 GROUP BY prod_name; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic +note'));
出力例は次のように表示されます。
Plan hash value: 1117033222 ------------------------------------------ | Id | Operation | Name | ------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | HASH GROUP BY | | | 2 | HASH JOIN | | | 3 | TABLE ACCESS FULL | PRODUCTS | | 4 | PARTITION RANGE ALL| | | 5 | TABLE ACCESS FULL | SALES | ------------------------------------------ Note ----- - SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement
注意事項は、オプティマイザが前の手順でリストされていた計画名で示されている計画を使用していることを示しています。
SELECT /* q1_group_by */文のパフォーマンスを向上させるために2つの索引を作成します。
たとえば、次の文を使用します。
CREATE INDEX ind_prod_cat_name ON products(prod_category_id, prod_name, prod_id); CREATE INDEX ind_sales_prod_qty_sold ON sales(prod_id, quantity_sold);
SELECT /* q1_group_by */文を再度実行します。
自動取得が有効になっているため、この文に対する新しい計画が計画ベースラインに移入されます。
データ・ディクショナリを問い合せて、文に対するSQL計画ベースラインに計画がロードされたことを確認します。
次の文では、DBA_SQL_PLAN_BASELINESを問い合せます(出力例も示します)。
SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_HANDLE IN ('SQL_07f16c76ff893342')
ORDER BY SQL_HANDLE, ACCEPTED;
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ ------------ --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu20135fd6c AUTO-CAPTURE YES NO
y */ prod_name, sum(
quantity_sold)
FROM products p, s
ales s
WHERE p.prod_id = s
.prod_id
AND p.prod_catego
ry_id =203
GROUP BY prod_name
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES
y */ prod_name, sum(
quantity_sold)
FROM products p, s
ales s
WHERE p.prod_id = s
.prod_id
AND p.prod_catego
ry_id =203
GROUP BY prod_name
出力は、新しい計画が承認されていないこと、つまり文の履歴には含まれているが、SQL計画ベースラインに含まれていないことを示しています。
文に対する計画をEXPLAINし、オプティマイザにより元の索引なしの計画が使用されていることを確認します。
たとえば、次のように計画をEXPLAINしてから表示します。
EXPLAIN PLAN FOR SELECT /* q1_group_by */ prod_name, sum(quantity_sold) FROM products p, sales s WHERE p.prod_id = s.prod_id AND p.prod_category_id =203 GROUP BY prod_name; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic +note'));
出力例は次のように表示されます。
Plan hash value: 1117033222 ------------------------------------------ | Id | Operation | Name | ------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | HASH GROUP BY | | | 2 | HASH JOIN | | | 3 | TABLE ACCESS FULL | PRODUCTS | | 4 | PARTITION RANGE ALL| | | 5 | TABLE ACCESS FULL | SALES | ------------------------------------------ Note ----- - SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement
注意事項は、オプティマイザが手順3でリストされていた計画名で示されている計画を使用していることを示しています。
管理者として接続し、承認されていない計画を持つすべてのSQL文を考慮する展開タスクを作成します。
たとえば、DBMS_SPM.CREATE_EVOLVE_TASKファンクションを実行し、その後タスクの名前を取得します。
CONNECT / AS SYSDBA VARIABLE cnt NUMBER VARIABLE tk_name VARCHAR2(50) VARIABLE exe_name VARCHAR2(50) VARIABLE evol_out CLOB EXECUTE :tk_name := DBMS_SPM.CREATE_EVOLVE_TASK( sql_handle => 'SQL_07f16c76ff893342', plan_name => 'SQL_PLAN_0gwbcfvzskcu20135fd6c'); SELECT :tk_name FROM DUAL;
次の出力例は、タスクの名前を示しています。
:EVOL_OUT -------------------------------------------------------------------------- TASK_11
これでタスクが作成され、一意の名前が付けられたので、タスクを実行できます。
タスクを実行します。
たとえば、DBMS_SPM.EXECUTE_EVOLVE_TASKファンクションを実行します(出力例も示します)。
EXECUTE :exe_name :=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:tk_name); SELECT :exe_name FROM DUAL; :EXE_NAME --------------------------------------------------------------------------- EXEC_1
レポートを表示します。
たとえば、DBMS_SPM.REPORT_EVOLVE_TASKファンクションを実行します(出力例も示します)。
EXECUTE :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:tk_name, execution_name=>:exe_name );
SELECT :evol_out FROM DUAL;
GENERAL INFORMATION SECTION
--------------------------------------------------------------------------
Task Information:
---------------------------------------------
Task Name : TASK_11
Task Owner : SYS
Execution Name : EXEC_1
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 01/09/2012 12:21:27
Finished : 01/09/2012 12:21:29
Last Updated : 01/09/2012 12:21:29
Global Time Limit : 2147483646
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------
SUMMARY SECTION
---------------------------------------------------------------------------
Number of plans processed : 1
Number of findings : 1
Number of recommendations : 1
Number of errors : 0
---------------------------------------------------------------------------
DETAILS SECTION
---------------------------------------------------------------------------
Object ID : 2
Test Plan Name : SQL_PLAN_0gwbcfvzskcu20135fd6c
Base Plan Name : SQL_PLAN_0gwbcfvzskcu242949306
SQL Handle : SQL_07f16c76ff893342
Parsing Schema : SH
Test Plan Creator : SH
SQL Text : SELECT /*q1_group_by*/ prod_name,
sum(quantity_sold)
FROM products p, sales s
WHERE p.prod_id=s.prod_id AND p.prod_category_id=203
GROUP BY prod_name
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- ------------------------
Elapsed Time (s): .044336 .012649
CPU Time (s): .044003 .012445
Buffer Gets: 360 99
Optimizer Cost: 924 891
Disk Reads: 341 82
Direct Writes: 0 0
Rows Processed: 4 2
Executions: 5 9
FINDINGS SECTION
---------------------------------------------------------------------------
Findings (1):
-----------------------------
1. The plan was verified in 2.18 seconds. It passed the benefit criterion
because its verified performance was 2.01 times better than that of the
baseline plan.
Recommendation:
-----------------------------
Consider accepting the plan. Execute
dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_11', object_id => 2,
task_owner => 'SYS');
EXPLAIN PLANS SECTION
---------------------------------------------------------------------------
Baseline Plan
-----------------------------
Plan Id : 1
Plan Hash Value : 1117033222
---------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes |Cost | Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 861 | 924 | 00:00:12|
| 1 | HASH GROUP BY | | 21 | 861 | 924 | 00:00:12|
| *2| HASH JOIN | |267996|10987836 | 742 | 00:00:09|
| *3| TABLE ACCESS FULL | PRODUCTS | 21 | 714 | 2 | 00:00:01|
| 4 | PARTITION RANGE ALL | |918843| 6431901 | 662 | 00:00:08|
| 5 | TABLE ACCESS FULL | SALES |918843| 6431901 | 662 | 00:00:08|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 3 - filter("P"."PROD_CATEGORY_ID"=203)
Test Plan
-----------------------------
Plan Id : 2
Plan Hash Value : 20315500
---------------------------------------------------------------------------
|Id| Operation | Name | Rows | Bytes | Cost| Time |
---------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 21| 861|891|00:00:11|
| 1| SORT GROUP BY NOSORT| | 21| 861|891|00:00:11|
| 2| NESTED LOOPS | |267996|10987836|891|00:00:11|
|*3| INDEX RANGE SCAN |IND_PROD_CAT_NAME | 21| 714| 1|00:00:01|
|*4| INDEX RANGE SCAN |IND_SALES_PROD_QTY| 12762| 89334| 42|00:00:01|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("P"."PROD_CATEGORY_ID"=203)
* 4 - access("P"."PROD_ID"="S"."PROD_ID")
このレポートは、2つの新しい索引を使用する新しい実行計画のパフォーマンスが元の計画よりも優れていることを示しています。
展開タスクの推奨事項を実装します。
たとえば、IMPLEMENT_EVOLVE_TASKファンクションを実行します。
EXECUTE :cnt := DBMS_SPM.IMPLEMENT_EVOLVE_TASK( task_name=>:tk_name, execution_name=>:exe_name );
データ・ディクショナリを問い合せて、新しい計画が承認されたことを確認します。
問合せにより、次のサンプル出力が提供されます。
SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_HANDLE IN ('SQL_07f16c76ff893342')
ORDER BY SQL_HANDLE, ACCEPTED;
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ ------------ --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu20135fd6c AUTO-CAPTURE YES YES
y */ prod_name, sum(
quantity_sold)
FROM products p, s
ales s
WHERE p.prod_id = s
.prod_id
AND p.prod_catego
ry_id =203
GROUP BY prod_name
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES
y */ prod_name, sum(
quantity_sold)
FROM products p, s
ales s
WHERE p.prod_id = s
.prod_id
AND p.prod_catego
ry_id =203
GROUP BY prod_name
出力は、新しい計画が承認されたことを示しています。
例の後のクリーンアップを実行します。
たとえば、次の文を入力します。
EXEC :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_07f16c76ff893342');
EXEC :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_9049245213a986b3');
EXEC :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_bb77077f5f90a36b');
EXEC :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_02a86218930bbb20');
DELETE FROM SQLLOG$;
CONNECT sh
-- enter password
DROP INDEX IND_SALES_PROD_QTY_SOLD;
DROP INDEX IND_PROD_CAT_NAME;
関連項目:
DBMS_SPM展開ファンクションの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください
SQL計画ベースラインから一部またはすべての計画を削除できます。この方法は、SQL計画管理のテスト時に役立つことがあります。
計画を削除するには、DBMS_SPM.DROP_SQL_PLAN_BASELINEファンクションを使用します。このファンクションは、削除された計画の数を戻します。次の表では、入力パラメータを説明します。
表23-7 DROP_SQL_PLAN_BASELINEのパラメータ
| ファンクション・パラメータ | 説明 |
|---|---|
|
SQL文の識別子。 |
|
特定の計画の名前。デフォルトの |
この項では、コマンドラインからベースラインを削除する方法について説明します。Cloud Controlでは、「SQL計画ベースライン」サブページで、計画を選択してから「削除」をクリックします。
このチュートリアルでは、次のSQL文に対するすべての計画を削除することにより、実質的にSQL計画ベースラインを削除するものとします。
SELECT /* repeatable_sql */ COUNT(*) FROM hr.jobs;
SQL計画ベースラインを削除する手順は次のとおりです。
SQL*Plusを適切な権限でデータベースに接続し、データ・ディクショナリで計画ベースラインを問い合せます。
次の文では、DBA_SQL_PLAN_BASELINESを問い合せます(出力例も示します)。
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN,
2 ENABLED, ACCEPTED
3 FROM DBA_SQL_PLAN_BASELINES
4 WHERE SQL_TEXT LIKE 'SELECT /* repeatable_sql%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ -------------- --- ---
SQL_b6b0d1c71cd1807b SELECT /* repeatable SQL_PLAN_bdc6jswfd303v2f1e9c20 AUTO-CAPTURE YES YES
_sql */ count(*) fro
m hr.jobs
文に対するSQL計画ベースラインを削除します。
次の例は、SQLハンドルがSQL_b6b0d1c71cd1807bの計画ベースラインを削除し、削除された計画の数を戻します。計画名(plan_name)、SQLハンドル(sql_handle)またはその他の計画基準を使用して、計画ベースラインを指定します。table_nameパラメータは必須です。
DECLARE
v_dropped_plans number;
BEGIN
v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
sql_handle => 'SQL_b6b0d1c71cd1807b'
);
DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
END;
/
計画が削除されたことを確認します。
たとえば、次の問合せを実行します。
SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN,
ENABLED, ACCEPTED
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE 'SELECT /* repeatable_sql%';
no rows selected
関連項目:
DROP_SQL_PLAN_BASELINEファンクションの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
SQL管理ベース(SMB)は、SYSAUX表領域にあるデータ・ディクショナリの一部です。SMBには、文のログ、計画履歴、SQL計画ベースラインおよびSQLプロファイルが格納されます。
この項では、SMB用のディスク領域使用量パラメータの変更方法およびSMBでの計画の保存時間の変更方法について説明します。
DBA_SQL_MANAGEMENT_CONFIGビューには、SMBの現在の構成設定が表示されます。表23-8に、PARAMETER_NAME列のパラメータを示します。
表23-8 DBA_SQL_MANAGEMENT_CONFIG.PARAMETER_NAMEのパラメータ
| パラメータ | 説明 |
|---|---|
|
SQL管理ベースが使用可能な |
|
消去するまで未使用の計画を保持する週の数。デフォルトは53です。 |
週に1回のバックグラウンド・プロセスによって、SMBが使用する総領域が測定されます。定義した制限を超過すると、アラート・ログに警告が書き込まれます。SMB領域の制限が引き上げられるか、SYSAUX表領域のサイズが引き上げられるか、またはSQL管理オブジェクト(SQL計画ベースラインまたはSQLプロファイル)の消去によりSMBによって使用されている領域が減るまで、データベースは週に1回アラートを生成し続けます。このタスクでは、DBMS_SPM.CONFIGUREプロシージャを使用して制限を変更する方法について説明します。
前提条件
このチュートリアルでは、次のことが前提となっています。
現在のSMB領域制限がデフォルトの10%である。
制限の比率を30%に変更する。
SMBの制限の比率を変更する手順は次のとおりです。
SQL*Plusを適切な権限でデータベースに接続し、データ・ディクショナリを問い合せて、現在の領域配分の比率を確認します。
たとえば、次の問合せを実行します(出力例も示します)。
SELECT PARAMETER_NAME, PARAMETER_VALUE AS "%_LIMIT",
( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'SYSAUX' ) AS SYSAUX_SIZE_IN_MB,
PARAMETER_VALUE/100 *
( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'SYSAUX' ) AS "CURRENT_LIMIT_IN_MB"
FROM DBA_SQL_MANAGEMENT_CONFIG
WHERE PARAMETER_NAME = 'SPACE_BUDGET_PERCENT';
PARAMETER_NAME %_LIMIT SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB
-------------------- ---------- ----------------- -------------------
SPACE_BUDGET_PERCENT 10 211.4375 21.14375
比率設定を変更します。
たとえば、設定を30%に変更するには次のコマンドを実行します。
EXECUTE DBMS_SPM.CONFIGURE('space_budget_percent',30);
データ・ディクショナリを問い合せて、変更を確認します。
たとえば、次の結合を実行します(出力例も示します)。
SELECT PARAMETER_NAME, PARAMETER_VALUE AS "%_LIMIT",
( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'SYSAUX' ) AS SYSAUX_SIZE_IN_MB,
PARAMETER_VALUE/100 *
( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'SYSAUX' ) AS "CURRENT_LIMIT_IN_MB"
FROM DBA_SQL_MANAGEMENT_CONFIG
WHERE PARAMETER_NAME = 'SPACE_BUDGET_PERCENT';
PARAMETER_NAME %_LIMIT SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB
-------------------- ---------- ----------------- -------------------
SPACE_BUDGET_PERCENT 30 211.4375 63.43125
関連項目:
CONFIGUREプロシージャの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください
週次にスケジュールされた消去タスクにより、SQL計画の管理で使用されるディスク領域が管理されます。このタスクは、メンテナンス期間に自動化タスクとして実行されます。データベースは、計画保存期間を超える間使用されていない計画を消去します。消去対象は、SMBに保存されたその計画のLAST_EXECUTEDタイムスタンプで識別されます。デフォルトの保存期間は53週です。指定できる期間の範囲は5から523週です。
このタスクでは、DBMS_SPM.CONFIGUREプロシージャを使用して計画保存期間を変更する方法について説明します。Cloud Controlでは、「SQL計画ベースライン」サブページ(図23-7を参照)で計画保存ポリシーを設定します。
SMBの計画保存期間を変更する手順は次のとおりです。
SQL*Plusを適切な権限でデータベースに接続し、データ・ディクショナリを問い合せて、現在の計画保存期間を確認します。
たとえば、次の問合せを実行します(出力例も示します)。
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE 2 FROM DBA_SQL_MANAGEMENT_CONFIG 3 WHERE PARAMETER_NAME = 'PLAN_RETENTION_WEEKS'; PARAMETER_NAME PARAMETER_VALUE ------------------------------ --------------- PLAN_RETENTION_WEEKS 53
保存期間を変更します。
たとえば、CONFIGUREプロシージャを実行して期間を105週に変更します。
EXECUTE DBMS_SPM.CONFIGURE('plan_retention_weeks',105);
データ・ディクショナリを問い合せて、変更を確認します。
たとえば、次の問合せを実行します。
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE
2 FROM DBA_SQL_MANAGEMENT_CONFIG
3 WHERE PARAMETER_NAME = 'PLAN_RETENTION_WEEKS';
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
PLAN_RETENTION_WEEKS 105
関連項目:
CONFIGUREプロシージャの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください
