| Oracle® Database SQLチューニング・ガイド 12c リリース1 (12.1) B71277-09 |
|
 前 |
 次へ |
オプティマイザのデフォルト設定はほとんどの操作に適していますが、すべての操作に適しているわけではありません。
オプティマイザにとって不明な情報が含まれている場合や、特定の種類の文またはワークロードに合うようにオプティマイザをチューニングする必要がある場合もあります。そのような場合は、オプティマイザに与える影響によりパフォーマンスを改善できることがあります。
この章の内容は次のとおりです。
SQLプロファイル、SQL計画管理、初期化パラメータおよびヒントなど、様々な手法を使用してオプティマイザに影響を与えることができます。
次の図には、オプティマイザに影響を与える主な手法が示されています。
この図の四角形の重なりは、SQL計画管理によって、初期化パラメータとヒントの両方が使用されることを示しています。また、SQLプロファイルにも技術的にはヒントが含まれています。
次の手法を使用してオプティマイザに影響を与えることができます。
初期化パラメータ
パラメータは、オプティマイザの多くの種類の動作にデータベース・インスタンスやセッション・レベルで影響を与えます。最も重要なパラメータについては、「初期化パラメータによるオプティマイザへの影響」で説明します。
ヒント
ヒントは、SQL文でコメント化された命令です。ヒントで広範囲の動作が制御されます。「ヒントによるオプティマイザへの影響」を参照してください。
DBMS_STATS
このパッケージではオプティマイザ統計を更新および管理します。統計をより正確にすることで、オプティマイザの見積りもより改善されます。
この章では、DBMS_STATSについては扱いません。「オプティマイザ統計の管理: 基本的なトピック」を参照してください。
SQLプロファイル
SQLプロファイルは、SQL文に固有の補足統計を含むデータベース・オブジェクトです。概念上、SQL文に対するSQLプロファイルの役割は、表または索引に対するオブジェクト・レベルの統計情報の役割と同じです。SQLプロファイルにより、SQLチューニングの間に見つかった最適ではないオプティマイザの見積りを修正できます。
この章では、SQLプロファイルについては扱いません。「SQLプロファイルの管理」を参照してください。
SQL計画管理とストアド・アウトライン
SQL計画管理は、データベースで既知の計画または確認済の計画のみが使用されるようにオプティマイザで実行計画を自動的に管理する予防的なメカニズムです。
この章では、SQL計画管理については扱いません。「SQL計画ベースラインの管理」を参照してください。
注意:
ストアド・アウトラインは、SQL計画ベースラインとよく似た目的を果たすレガシーな手法です。ストアド・アウトラインのSQL計画ベースラインへの移行方法の詳細は、「ストアド・アウトラインのSQL計画ベースラインへの移行」を参照してください。
場合によっては、複数の手法で同じ動作を最適化します。たとえば、初期化パラメータとヒントの両方を使用してオプティマイザの目標を設定できます。
この章では、最適化に影響を与える初期化パラメータ、およびそれらの設定方法について説明します。
この項の内容は次のとおりです。
Oracle Databaseには、オプティマイザ動作に影響を与えることができる様々な初期化パラメータが含まれています。
次の表に、最も重要な初期化パラメータの一部を示します。
表14-1 オプティマイザ動作を制御する初期化パラメータ
| 初期化パラメータ | 説明 |
|---|---|
|
SQL文のリテラル値をバインド変数に変換します。値を変換するとカーソル共有が改善され、SQL文の実行計画は影響を受けます。オプティマイザは、実際のリテラル値でなくバインド変数の有無に基づいて実行計画を生成します。
|
|
全表スキャンまたは高速全索引スキャン時に単一I/Oで読み取られるブロックの個数を指定します。オプティマイザは、このパラメータの値を使用して、全表スキャンと高速全索引スキャンのコストを計算します。値が大きいほど全表スキャンのコストは低くなり、結果として、オプティマイザは索引スキャンより全表スキャンを選択することになります。 このパラメータのデフォルト値は、データベースを効果的に実行できる最大I/Oサイズに対応しています。この値は、プラットフォームに依存し、ほとんどのプラットフォームで1MBになります。このパラメータはブロック単位で表されるため、効率的に実行できる最大I/Oサイズを標準ブロック・サイズで除算した値に設定されます。セッション数が非常に大きい場合は、表スキャン・バッファが多すぎてバッファ・キャッシュがあふれないように、マルチブロック読取りカウントの値が減らされます。 |
|
自動再最適化と適応計画のレポート作成モードを制御します(「適応問合せ計画」を参照)。デフォルトでは、レポート作成モードはオフ(
|
|
データベース・インスタンスの起動時にオプティマイザ・モードを設定します。 |
|
ネステッド・ループとともに索引プローブのコスト分析を制御します。値 |
|
索引プローブのコストを調整します。値の範囲は、 |
|
このパラメータでは、インメモリー、表拡張、ブルーム・フィルタなどのコスト・モデルを含むすべてのインメモリー・オプティマイザ機能を有効( |
|
非表示の索引の使用を有効化または無効化します。 |
|
データベースで使用するSQL問合せ結果キャッシュの対象を、すべての問合せにするのか、または結果キャッシュ・ヒントで注釈付けされた問合せのみにするのかを制御します。 このパラメータを設定する場合は、結果キャッシュでPL/SQLファンクションを処理する方法について検討します。データベースでは、PL/SQLファンクションのデータ依存性を追跡するのと同じメカニズムを使用して結果キャッシュ内の問合せ結果が無効化されますが、それ以外の場合は、PL/SQLファンクションを含む問合せをキャッシュできます。PL/SQLファンクション結果キャッシュの無効化では全種類の依存性(順序、 |
|
結果キャッシュに割り当てられているメモリーを変更します。このパラメータを |
|
すべての単一の結果で使用可能なキャッシュ・メモリーの最大値を指定します。デフォルト値は5%ですが、 |
|
リモート・データベース・オブジェクトに依存する結果の有効時間(分単位)を指定します。デフォルトは |
|
オプティマイザはスター・クエリーのスター型変換のコストを計算できます( |
関連項目:
前述の初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください
問合せ結果キャッシュのチューニング方法の詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください
OPTIMIZER_FEATURES_ENABLE初期化パラメータ(またはヒント)は一連のオプティマイザ関連機能をデータベース・リリースに応じて制御します。
パラメータによって、11.2.0.4または12.1.0.2など、リリース番号に対応する有効な文字列値のいずれかを受け入れます。このパラメータを使用して、データベースのアップグレード後もオプティマイザの以前の動作を保持できます。たとえば、Oracle Database 11gリリース2 (11.2.0.4)からOracle Database 12c リリース1 (12.1.0.2)にアップグレードすると、OPTIMIZER_FEATURES_ENABLEパラメータのデフォルト値は11.2.0.4から12.1.0.2に変更されます。
下位互換性のため、新規リリースの新しいオプティマイザ機能という理由で実行計画の変更を必要とはしない場合もあります。そのような場合は、OPTIMIZER_FEATURES_ENABLEを以前のバージョンに設定できます。新しいリリースにアップグレードする場合や、新しいリリースの機能を有効化する必要がある場合は、OPTIMIZER_FEATURES_ENABLE初期化パラメータを明示的に設定する必要はありません。
注意:
OPTIMIZER_FEATURES_ENABLE初期化パラメータを以前のリリースに明示的に設定することはお薦めしません。実行計画の変更から生じる可能性があるSQLパフォーマンスの低下を回避するには、かわりに、SQL計画管理の使用を検討してください。「SQL計画ベースラインの管理」を参照してください。
前提条件
このチュートリアルでは、次のことが前提となっています。
最近、データベースをOracle Database 11g リリース2 (11.2.0.4)からOracle Database 12c リリース1 (12.1.0.2)にアップグレードしました。
以前のリリースのオプティマイザ動作を保持したいと思っています。
特定のリリースの問合せオプティマイザ機能を有効化する手順は次のとおりです。
SQL*Plusを起動し、適切な権限でデータベースにログインします。
現在のオプティマイザ機能設定を問い合せます。
たとえば、次のSQL*Plusコマンドを実行します。
SQL> SHOW PARAMETER optimizer_features_enable NAME TYPE VALUE ------------------------------------ ----------- -------- optimizer_features_enable string 11.2.0.4
インスタンスまたはセッション・レベルでオプティマイザ機能設定を設定します。
たとえば、次のSQL文を実行してオプティマイザ・バージョンを12.1.0.2に設定します。
SQL> ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE='12.1.0.2';
この文では、Oracle Database 12c リリース1 (12.1.0.2)に存在するオプティマイザ機能をリストアします。
関連項目:
OPTIMIZER_FEATURES_ENABLEを異なるリリースの値に設定する際に有効化されるオプティマイザ機能の詳細は、『Oracle Databaseリファレンス』を参照してください。
オプティマイザの目標は、オプティマイザによるリソース使用を優先順位付けしたものです。
OPTIMIZER_MODE初期化パラメータを使用して、次のオプティマイザの目標を設定できます。
最高のスループット(デフォルト)
OPTIMIZER_MODE値をALL_ROWSに設定すると、データベースでは、文がアクセスしたすべての行の処理に必要な最小限のリソース量が使用されます。
Oracle Reportsなどのバッチ・アプリケーションでは、最高のスループットを目標に最適化します。通常、バッチ・アプリケーションでスループットがより重要になるのは、ユーザーが、アプリケーションの完了に必要な時間にのみ関心を持っているためです。アプリケーションの実行中に、ユーザーは個々の文の結果を検証しないため、レスポンス時間はそれほど重要ではありません。
最短のレスポンス時間
OPTIMIZER_MODE値をFIRST_ROWS_nに設定すると、データベースでは、最初のn行を戻す最短のレスポンス時間を目標に最適化されます。ここでのnは1、10、100または1000です。
Oracle FormsまたはSQL*Plusの対話型アプリケーションでは、レスポンス時間に対して最適化します。通常、レスポンス時間が重要なのは、文がアクセスした最初の行または数行を確認するために対話型ユーザーが待機しているためです。
前提条件
このチュートリアルでは、次のことが前提となっています。
プライマリ・アプリケーションが対話型であるため、データベース・インスタンスにオプティマイザの目標を設定してレスポンス時間を最小化します。
現行のセッションの場合のみ、レポート作成を実行し、スループットに対して最適化します。
特定のリリースの問合せオプティマイザ機能を有効化する手順は次のとおりです。
適切な権限でSQL*Plusをデータベースに接続し、現行のオプティマイザ・モードを問い合せます。
たとえば、次のSQL*Plusコマンドを実行します。
dba1@PROD> SHOW PARAMETER OPTIMIZER_MODE NAME TYPE VALUE ------------------------------------ ----------- -------- optimizer_mode string ALL_ROWS
インスタンス・レベルで、レスポンス時間に対して最適化します。
たとえば、次のSQL文を実行して、最初の10行をできるかぎり迅速に取得するようにシステムを構成します。
SQL> ALTER SYSTEM SET OPTIMIZER_MODE='FIRST_ROWS_10';
セッション・レベルでのみ、レポート作成を実行する前にスループットに対して最適化します。
たとえば、次のSQL文を実行して、スループットに対して最適化するようにこのセッションのみを構成します。
SQL> ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS';
関連項目:
OPTIMIZER_MODE初期化パラメータの詳細は、Oracle Databaseリファレンスを参照してください
Oracle Databaseの適応問合せ最適化は、実行時に収集された統計に基づいてオプティマイザで実行計画を採用するためのプロセスです。
適応最適化は次の条件で有効化されます。
OPTIMIZER_FEATURES_ENABLE初期化パラメータが12.1.0.1以降に設定されている。
OPTIMIZER_ADAPTIVE_REPORTING_ONLY初期化パラメータがfalse(デフォルト)に設定されている。
OPTIMIZER_ADAPTIVE_REPORTING_ONLYがtrueに設定されている場合は、レポート作成のみのモードで適応最適化が実行されます。この場合は、データベースで適応最適化に必要な情報が収集されるものの、計画は変更されません。適応計画では常にデフォルトの計画が選択されますが、パラメータがfalseに設定されているかのように実行に関する情報がデータベースで収集されます。
前提条件
このチュートリアルでは、次のことが前提となっています。
OPTIMIZER_FEATURES_ENABLE初期化パラメータが12.1.0.1以降に設定されている。
OPTIMIZER_ADAPTIVE_REPORTING_ONLY初期化パラメータがfalse(デフォルト)に設定されている。
テスト目的で適応最適化を無効化し、データベースでレポートのみが生成されるようにします。
適応最適化を無効化し、レポートを表示する手順は次のとおりです。
SYSTEMとしてSQL*Plusをデータベースに接続し、現行の設定を問い合せます。
たとえば、次のSQL*Plusコマンドを実行します。
SHOW PARAMETER OPTIMIZER_ADAPTIVE_REPORTING_ONLY NAME TYPE VALUE ------------------------------------ ----------- ----- optimizer_adaptive_reporting_only boolean FALSE
セッション・レベルで、OPTIMIZER_ADAPTIVE_REPORTING_ONLY初期化パラメータをtrueに設定します。
たとえば、SQL*Plusで次のSQL文を実行します。
ALTER SESSION SET OPTIMIZER_ADAPTIVE_REPORTING_ONLY=true;
問合せを実行します。
DBMS_XPLAN.DISPLAY_CURSORを実行して、レポートを表示します。
注意:
DBMS_XPLAN.DISPLAY_CURSORに渡す引数の形式には、+REPORTパラメータを含める必要があります。このパラメータが設定されると、自動最適化が有効な場合、オプティマイザが選択する計画がレポートに表示されます。
関連項目:
OPTIMIZER_ADAPTIVE_REPORTING_ONLY初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。
DBMS_XPLAN.DISPLAY_CURSORのファンクションの+REPORTパラメータの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
オプティマイザ・ヒントは、命令をオプティマイザに渡すSQL文の特別なコメントです。オプティマイザは、なんらかの状況に妨害されないかぎり、ヒントを使用して文の実行計画を選択します。
この項の内容は次のとおりです。
注意:
すべてのSQLヒントの完全な詳細は、『Oracle Database SQL言語リファレンス』を参照してください
ヒントは、オプティマイザ・モード、問合せの転送、アクセス・パス、結合順序および結合方法に影響を与えるために使用します。
たとえば、次の図には、ヒントを使用してオプティマイザに特定の索引を使用するよう指示する方法が示されています。
ヒントの利点は、通常はオプティマイザによって行われる決定をユーザーが決定できることです。テスト環境では、特定のアクセス・パスのパフォーマンスをテストする場合にヒントが役に立ちます。たとえば、図14-2のように、ある問合せに対しては、特定の索引を選択する方がよいとわかっている場合もあります。この場合、ヒントによってオプティマイザがより適切な計画を生成できるようになる可能性があります。
ヒントの短所は、管理、チェックおよび制御が必要な追加のコードであることです。ヒントが導入されたのはOracle7からですが、当時は、オプティマイザによって生成された計画が最善ではない場合にユーザーが取れる手段はほとんどありませんでした。データベースやホスト環境内の変更によってヒントが非推奨となるか、負の結果をもたらす可能性があるため、ヒントを使用したテストが望ましいものの、他の手法を使用して実行計画を管理することがあります。
オプティマイザでは解決されないパフォーマンスの問題に対処するために、SQLチューニング・アドバイザ、SQL計画管理、SQLパフォーマンス・アナライザなどの複数のツールが用意されています。これらのツールにはデータやデータベース環境の変更に対応した新しいソリューションが用意されているため、ヒントのかわりにこれらを使用することを強くお薦めします。
関連項目:
機能カテゴリ別の最も一般的なヒントは、『Oracle Database SQL言語リファレンス』を参照してください。
ヒントを表、問合せブロックおよび文に使用できます。
ヒントは次の型に分類されます。
単一表
単一表ヒントは、1つの表またはビュー上で指定します。単一表ヒントの例として、INDEXおよびUSE_NLがあります。次の文では単一表ヒントが使用されています。
SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id
FROM employees
WHERE department_id > 50;
複数表
複数表ヒントは単一表ヒントに類似していますが、ヒントで複数の表またはビューを指定できる点が異なります。複数表ヒントの例には、LEADINGがあります。次の文では複数表ヒントが使用されています。
SELECT /*+ LEADING(e j) */ *
FROM employees e, departments d, job_history j
WHERE e.department_id = d.department_id
AND e.hire_date = j.start_date;
注意:
USE_NL(table1 table2)が複数表ヒントとみなされていないのは、USE_NL(table1)とUSE_NL(table2)のショートカットであるためです。
問合せブロック
問合せブロック・ヒントでは、単一の問合せブロックが処理されます。問合せブロック・ヒントの例として、STAR_TRANSFORMATIONおよびUNNESTがあります。次の文では問合せブロック・ヒントが使用されています。
SELECT /*+ STAR_TRANSFORMATION */ s.time_id, s.prod_id, s.channel_id
FROM sales s, times t, products p, channels c
WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id
AND s.channel_id = c.channel_id AND c.channel_desc = 'Tele Sales';
文
文ヒントはSQL文全体に適用されます。文ヒントの例には、ALL_ROWSがあります。次の文では文ヒントが使用されています。
SELECT /*+ ALL_ROWS */ * FROM sales;
ヒントを指定すると、ヒントに表示される文ブロックのみが最適化され、インスタンス・レベルまたはセッション・レベルのすべてのパラメータが上書きされます。
文ブロックは次のいずれかです。
単純なMERGE、SELECT、INSERT、UPDATEまたはDELETE文
複合文の親文または副問合せ
集合演算子(UNION、MINUS、INTERSECT)を使用した問合せの一部
例14-1 集合演算子を使用した問合せ
次の問合せは、2つのコンポーネント問合せとUNION演算子で構成されます。
SELECT /*+ FIRST_ROWS(10) */ prod_id, time_id FROM 2010_sales UNION ALL SELECT /*+ ALL_ROWS */ prod_id, time_id FROM current_year_sales;
前述の文には2つのブロックがあり、それぞれが各コンポーネント問合せに対応しています。最初のコンポーネント問合せのヒントはその最適化のみに適用され、2番目のコンポーネント問合せの最適化には適用されません。たとえば、2015年の第1週に現行年度と昨年度の売上を問い合せるとします。昨年度(2014)の売上の問合せにはFIRST_ROWS(10)を適用し、今年度(2015)の売上の問合せにはALL_ROWSヒントを適用します。
関連項目:
ヒントの概要は、『Oracle Database SQL言語リファレンス』を参照してください
SQLコメント内のヒントは囲む必要があります。ヒント・コメントは、SQL文ブロックの最初のキーワードのすぐ後ろに続ける必要があります。スラッシュスター(/*)またはダッシュのペア(--)のいずれかのコメント・スタイルを使用できます。次のフラグメントのように、プラス記号(+)ヒント・デリミタは、コメント・デリミタのすぐ後に続く必要があります。
SELECT /*+ hint_text */ ...
データベースでは、間違って指定されたヒントは無視されます。また、矛盾したヒントの組合せは、たとえそれらが適切に指定されたとしてもデータベースでは無視されます。あるヒントが間違って指定されたものの、同じコメントのヒントが適切に指定されている場合は、データベースで適切なヒントとみなされます。
注意:
データベースでは、無視したヒントに対してエラー・メッセージは発行されません。
文ブロックでは、ヒントを含むコメントは1つしか持つことができませんが、空白区切りのヒントは多数含むことができます。たとえば、複合問合せには複数の表結合が含まれることがあります。指定された表のINDEXヒントのみを指定する場合は、オプティマイザで残りのアクセス・パスと対応する結合方法を決定する必要があります。オプティマイザがINDEXヒントを使用しないことがあるのは、結合方法とアクセス・パスによってそれが妨げられるためです。例14-2では、複数のヒントを使用して正確な結合順序が指定されています。
例14-2 複数のヒント
SELECT /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1 emp_emp_id_pk)
USE_MERGE(j) FULL(j) */
e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1, employees e2, job_history j
WHERE e1.employee_id = e2.manager_id
AND e1.employee_id = j.employee_id
AND e1.hire_date = j.start_date
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal;
関連項目:
コメントおよびヒントの構文ルールの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
結合順序は、SQL文のパフォーマンスに大きな影響を与えることがあります。場合によっては、結果に影響を及ぼさない行にはアクセスしないように、SQL文で結合順序のヒントを指定することができます。
結合における駆動表は、その他の表が結合された表です。通常、駆動表には、表内を最も高い割合で排除するフィルタ条件が含まれています。
次のガイドラインを考慮してください。
索引によって、要求された行がより効率的に取得される場合は、全表スキャンを実行しないでください。
少数の行をフェッチする索引を使用できる場合には、駆動表から多数の行をフェッチする索引を使用するようなことは避けてください。
結合順序の後ろへ行くほど結合する行が少なくなるように結合順序を選択してください。
次の例は、結合順序を効果的にチューニングする方法を示しています。
SELECT * FROM taba a, tabb b, tabc c WHERE a.acol BETWEEN 100 AND 200
AND b.bcol BETWEEN 10000 AND 20000 AND c.ccol BETWEEN 10000 AND 20000 AND a.key1 = b.key1 AND a.key2 = c.key2;
駆動表と駆動索引(存在する場合)を選択します。
前述の例における最初の3つの条件はそれぞれ、1つの表に適用されるフィルタ条件です。最後の2つの条件は結合条件です。
フィルタ条件は、駆動表と駆動索引の選択を左右します。通常、駆動表には、行を最も高い割合で排除するフィルタ条件が含まれています。この例では、100から200の範囲はacolの範囲に比べて狭く、10000から20000の範囲は相対的に大きいため、tabaは駆動表であり、他はすべて同じです。
ネステッド・ループ結合の場合、結合索引を介して結合します。この結合索引は、主キーまたは外部キーに付けられているもので、その表を結合ツリー内のそれより前にある表に結び付けるために使用します。駆動表を除いて、非結合条件にこの結合索引を使用することはほとんどありません。そのため、tabaを駆動表として選択すると、b.key1とc.key2の索引を使用してtabbとtabcをそれぞれ駆動します。
未使用の最適なフィルタを最初に駆動する最適な結合順序を選択します。
最適な未使用フィルタを持つ表に先に結合することで、その後の結合の作業を削減できます。したがって、bcol BETWEEN ...がccol BETWEEN ...よりも限定的な(行をより高い比率で排除する)場合は、tabbをtabcよりも前に結合すると、最後の結合が容易になります(行数が少なくなります)。
ORDEREDまたはSTARヒントを使用して、結合順序を強制的に設定できます。
関連項目:
OPTIMIZER_MODEの詳細は、『Oracle Databaseリファレンス』を参照してください
