プライマリ・コンテンツに移動
Oracle® Database SQLチューニング・ガイド
12c リリース1 (12.1)
B71277-09
目次へ移動
目次
索引へ移動
索引

前
次
次へ

1 SQLチューニングの概要

SQLチューニングは、パフォーマンス基準を満たさないSQL文を診断および修復する試みです。

この章の内容は次のとおりです。

SQLチューニングについて

SQLチューニングは、SQL文のパフォーマンスを、特定の測定可能かつ達成可能な目標に合うように向上させる反復プロセスです。

SQLチューニングには、デプロイ済アプリケーションの問題の解決も暗黙的に含まれます。対照的に、アプリケーション設計では、アプリケーションをデプロイするにセキュリティおよびパフォーマンスの目標を設定します。

関連項目:

SQLチューニングの目的

事前定義された測定可能な基準に沿ってSQL文を実行できない場合、そのSQL文は問題になります。

問題を特定した後、一般的なチューニング・セッションの目標は次のいずれかになります。

  • ユーザー・レスポンス時間の短縮。これは、ユーザーが文を発行してからレスポンスを受け取るまでの時間を短縮することを意味します。

  • スループットの向上。これは、文がアクセスするすべての行を処理するために必要なリソースの使用量を最小限に抑えることを意味します。

レスポンス・タイムの問題については、顧客がショッピング・カートを更新すると、3分間ハングしてしまうオンライン書店のアプリケーションを考えてみましょう。データベース・ホストのCPUをすべて消費する、データ・ウェアハウスにおける3分間のパラレル問合せ(他の問合せの実行は阻止されます)と対比してみてください。いずれのケースでも、ユーザー・レスポンス・タイムは3分間ですが、問題の原因は異なっており、したがってチューニングの目標も異なります。

SQLチューニングの前提条件

SQLチューニングには、データベース知識の基礎が必要です。

SQLをチューニングする場合、このマニュアルでは、読者に次の知識とスキルがあることを前提としています。

  • データベース・アーキテクチャに精通している

    データベース・アーキテクチャは、管理者のみが関係する領域ではありません。開発者としてOracle Databaseを使用するアプリケーションの開発にかかる時間を最小限に抑えるには、データベースのアーキテクチャおよび機能を利用する必要があります。たとえば、Oracle Databaseの並行性制御およびマルチバージョン読取りの一貫性について理解していないと、アプリケーションでデータの整合性を損なったり、アプリケーションの実行が遅くなったり、スケーラビリティが低下するおそれがあります。

    『Oracle Database概要』で、Oracle Databaseの基本的なリレーショナル・データ構造、トランザクション管理、記憶域構造およびインスタンス・アーキテクチャについて説明しています。

  • SQLおよびPL/SQLの知識

    GUIベースのツールがあるため、SQLに関する知識がなくても、アプリケーションを作成し、データベースを管理することは可能です。ただし、SQLに関する知識なしにアプリケーションまたはデータベースをチューニングすることはできません。

    『Oracle Database概要』には、Oracle SQLおよびPL/SQLの概要が含まれています。『Oracle Database SQL言語リファレンス』『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』および『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』の実用的な知識も必要です。

  • データベースが提供するSQLチューニング・ツールに精通していること

    データベースにより、パフォーマンス統計が生成され、それらの統計を解釈するためのSQLチューニング・ツールが提供されます。

    主要なSQLチューニング・ツールの概要は、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』を参照してください。

SQLチューニングのタスクとツール

チューニング・セッションの目標(たとえば、ユーザー・レスポンス・タイムを3分から1秒未満に短縮するなど)を設定したら、問題は、どうやってその目標を達成するかです。

Oracle推奨のチューニング方法の詳細は、「SQLパフォーマンスの方法」を参照してください。

SQLチューニング・タスク

チューニング・セッションの仕様は、事前チューニングを行うか、事後チューニングを行うかなど、多数の要因によって異なります。

事前のSQLチューニングでは通常、SQLチューニング・アドバイザを使用して、SQL文のパフォーマンスを向上させることができるかどうかを判断します。事後のSQLチューニングでは、ユーザーが経験したSQL関連の問題を解決します。

事前のチューニングを行うか、事後のチューニングを行うかに関係なく、一般的なSQLチューニング・セッションには、次のタスクのすべてまたはほとんどが含まれています。

  1. 高負荷のSQL文の識別

    過去の実行履歴を検討し、アプリケーションのワークロードおよびシステム・リソースの大部分に関係している文を識別します。

  2. パフォーマンス関連のデータの収集

    オプティマイザ統計は、SQLチューニングにとって非常に重要です。これらの統計が存在しないか、正確でない場合、オプティマイザは、最適な計画を生成することができません。SQLパフォーマンスに関連する他のデータには、文がアクセスした表やビューの構造、および文で使用可能な索引の定義などがあります。

  3. 問題の原因の特定

    通常、SQLパフォーマンスの問題の原因には、次のようなものがあります。

    • SQL文の設計が非効率的である

      SQL文が不要な作業を実行するように記述されている場合、パフォーマンスの改善のためにオプティマイザができることはあまりありません。非効率的な設計パターンの例を次に示します。

      • 結合条件が追加されていないため、デカルト結合が発生する。

      • ヒントによって大きな表を結合内の駆動表として指定する。

      • UNION ALLではなくUNIONを指定する。

      • 外部問合せの各行に対して副問合せを実行する。

    • 最適ではない実行計画

      問合せオプティマイザ(オプティマイザとも呼ばれます)は、どの実行計画が最も効率的かを判断する内部ソフトウェアです。オプティマイザは、データベースからデータを取り出すための手段として、アクセス・パスが最適ではない計画を選択することもあります。たとえば、選択性が低い問合せ述語に対する計画では、索引ではなく大きな表の全表スキャンを使用することが考えられます。

      SQL文を最適に実行する実行計画と、最適ではないパフォーマンスの文の計画を比較できます。この比較を行うと、データ量の変化などの情報とあわせて、パフォーマンス低下の原因の識別に役立ちます。

    • SQLアクセス構造がない

      SQLパフォーマンスが最適なものとならない一般的な理由は、索引やマテリアライズド・ビューなど、SQLアクセス構造がないことです。アクセス構造の最適なセットにより、SQLパフォーマンスを桁違いに向上させることができます。

    • 失効したオプティマイザ統計

      DBMS_STATSによって収集された統計は、自動または手動の統計メンテナンス操作が、DMLによる表データの変更に対応できていない場合に失効します。失効した表の統計は、表データを正確に反映していないため、オプティマイザによって誤った情報に基づいた決定が行われ、最適ではない実行計画が生成される可能性があります。

    • ハードウェアの問題

      最適ではないパフォーマンスは、メモリー、I/OおよびCPUの問題に関係する場合があります。

  4. 問題の範囲の定義

    ソリューションの範囲は、問題の範囲と一致している必要があります。データベース・レベルと文レベルで問題を検討してください。たとえば、共有プールが小さすぎると、カーソルのエージ・アウトが速くなり、その結果、ハード解析が何度も行われます。初期化パラメータを使用して共有プールのサイズを増やすと、データベース・レベルの問題は解決され、すべてのセッションのパフォーマンスが向上します。ただし、単一のSQL文で有用な索引が使用されていない場合、データベース全体に対してオプティマイザの初期化パラメータを変更すると、全体的なパフォーマンスに悪影響を及ぼすおそれがあります。単一のSQL文に問題がある場合は、適切な範囲のソリューションでその文のその問題にのみ対処します。

  5. 最適ではないパフォーマンスのSQL文に対する修正アクションの実装

    これらのアクションは状況によって異なります。たとえば、SQL文をより効率的なものにリライトすることが考えられます。バインド変数を使用するように文をリライトすることで、不要なハード解析を回避します。また、等価結合を使用する、WHERE句からファンクションを削除する、複雑なSQL文を複数の単純な文に分けるといったことも考えられます。

    場合によっては、文のリライトによってではなく、スキーマ・オブジェクトの再構築によってSQLパフォーマンスが向上することもあります。たとえば、新しいアクセス・パスの索引付けや、連結索引内の列の並替えなどです。また、表のパーティション化や導出値の導入に加えて、データベース設計の変更さえも考えられます。

  6. SQLパフォーマンスの低下の回避

    最適なSQLパフォーマンスを確保するには、実行計画が最適なパフォーマンスを実現し続けていることを確認し、より適切な計画が使用可能になったらそれらを採用します。オプティマイザ統計、SQLプロファイルおよびSQL計画ベースラインを使用して、これらの目標を達成できます。

SQLチューニング・ツール

SQLチューニング・ツールは、自動または手動のいずれかです。

このコンテキストでは、データベース自体が分析、アドバイスまたは修正アクションを提供できるものは自動ツールになります。手動ツールでは、これらの操作すべてをユーザーが実行する必要があります。

すべてのチューニング・ツールは、データベース・インスタンスが収集する動的パフォーマンス・ビュー、統計およびメトリックの基本ツールに依存します。SQL文のチューニングに必要なデータとメタデータは、データベース自体に含まれています。

自動SQLチューニング・ツール

Oracle Databaseには、SQLチューニングに関連するいくつかのアドバイザが用意されています。

さらに、SQL計画管理は、パフォーマンスの低下を回避し、SQLパフォーマンスの向上にも役立つメカニズムです。

すべての自動SQLチューニング・ツールで、入力としてSQLチューニング・セットを使用できます。SQLチューニング・セット(STS)は、1つ以上のSQL文がその実行統計および実行コンテキストとともに含まれているデータベース・オブジェクトです。

自動データベース診断モニター(ADDM)

ADDMは、Oracle Databaseに組み込まれている自己診断ソフトウェアです。

ADDMでは、パフォーマンスに関する問題の根本原因の特定、修正のための推奨事項の提示、および期待される利益の数値化を自動的に行うことができます。また、ADDMはアクションを必要としない領域を識別します。

ADDMおよび他のアドバイザでは、自動ワークロード・リポジトリ(AWR)が使用されます。これは、データベース・コンポーネントに統計の収集、保持および使用のためのサービスを提供するインフラストラクチャです。ADDMは、AWR内の統計を調べて分析し、高負荷SQLなど、パフォーマンスの問題となる可能性のある事柄を特定します。

たとえば、夜間実行されるようにADDMを構成できます。朝、担当者は、最新のADDMレポートを調べて、問題の原因と考えられることや推奨される解決策の有無を確認できます。レポートでは、たとえば、特定のSELECT文がCPUを大量に使用していることが示され、SQLチューニング・アドバイザの実行が推奨されています。

関連項目:

  • Oracle Database 2日でパフォーマンス・チューニング・ガイド

  • Oracle Databaseパフォーマンス・チューニング・ガイド

SQLチューニング・アドバイザ

SQLチューニング・アドバイザは、問題のあるSQL文を特定し、文のパフォーマンスの改善方法を提示する内部診断ソフトウェアです。

SQLチューニング・アドバイザが自動メンテナンス・タスクとしてデータベース・メンテナンス・ウィンドウに実行される場合、自動SQLチューニング・アドバイザと呼ばれます。

SQLチューニング・アドバイザは、入力として1つ以上のSQL文を取得し、自動チューニング・オプティマイザを起動して文に対するSQLチューニングを実行します。アドバイザは、次のタイプの分析を行います。

  • 欠落している統計または失効している統計の有無をチェックする

  • SQLプロファイルの構築

    SQLプロファイルは、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パフォーマンスの低下時にデータベースのチューニングを行うことや、SQLパフォーマンスが向上することによって得られる利益を検証および評価することが可能です。

関連項目:

「Oracle Database Testingガイド」

手動SQLチューニング・ツール

状況によっては、自動ツールに加えて手動ツールの実行が必要になる場合もあります。または、自動ツールを使用できないことも考えられます。

実行計画

実行計画は、手動SQLチューニングの主要な診断ツールです。たとえば、計画を表示して、オプティマイザが想定どおりの計画を選択しているかどうかを確認したり、表での索引作成の効果を特定したりすることができます。

複数の方法で実行計画を表示できます。最もよく使用されるツールは次のとおりです。

  • 「EXPLAIN PLAN」

    このSQL文では、実際にSQL文を実行せずに、オプティマイザがSQL文の実行に使用する実行計画を表示できます。『Oracle Database SQL言語リファレンス』を参照してください。

  • AUTOTRACE

    SQL*PlusのAUTOTRACEコマンドは、実行計画と、問合せのパフォーマンスに関する統計を生成します。このコマンドにより、ディスク読取りやメモリー読取りなどの統計が提供されます。『SQL*Plusユーザーズ・ガイドおよびリファレンス』を参照してください。

  • V$SQL_PLANおよび関連ビュー

    これらのビューには、まだ共有プール内に存在する実行済SQL文に関する情報、およびそれらの実行計画が含まれています。『Oracle Databaseリファレンス』を参照してください。

DBMS_XPLANパッケージ・メソッドを使用して、EXPLAIN PLANコマンドおよびV$SQL_PLANの問合せによって生成された実行計画を表示できます。

リアルタイムなSQL監視およびリアルタイムなデータベース操作

OracleデータベースのリアルタイムSQL監視機能では、SQL文の実行中にそのパフォーマンスを監視できます。デフォルトでは、文がパラレルで実行される場合、または1回の実行で5秒以上のCPUまたはI/O時間を消費している場合に、SQL監視が自動的に開始されます。

データベース操作は、バッチ・ジョブまたはデータ抽出、変換およびロード(ETL)処理など、エンド・ユーザーまたはアプリケーション・コードで定義された一連のデータベース・タスクです。データベース操作の定義、監視およびレポート生成が可能です。リアルタイムなデータベース操作では、複合操作を自動的に監視できます。データベースは、パラレルな問合せ、DML文およびDDL文を、実行の開始と同時に自動的に監視します。

Oracle Enterprise Manager Cloud Control (Cloud Control)には、使いやすいSQL監視ページが用意されています。または、V$SQL_MONITORおよびV$SQL_PLAN_MONITORビューを使用して、SQL関連の統計を監視することができます。これらのビューを次のビューとともに使用して、監視対象の実行に関する詳細情報を取得できます。

  • V$ACTIVE_SESSION_HISTORY

  • V$SESSION

  • V$SESSION_LONGOPS

  • V$SQL

  • V$SQL_PLAN

関連項目:

アプリケーションのトレース

SQLトレース・ファイルには、解析件数、物理および論理読取り、ライブラリ・キャッシュでのミスなど、個々のSQL文に関するパフォーマンス情報が記録されています。この情報を使用して、SQLパフォーマンスの問題を診断できます。

DBMS_MONITORまたはDBMS_SESSIONパッケージを使用して、特定のセッションのSQLトレースを有効または無効にできます。Oracle Databaseでは、トレース・メカニズムが有効化されたときに各サーバー・プロセスのトレース・ファイルを生成することによって、トレースを実装します。

Oracle Databaseでは、トレース・ファイルの分析のために次のコマンドライン・ツールが用意されています。

  • TKPROF

    このユーティリティは、SQLトレース機能によって生成されたトレース・ファイルを入力として受け入れ、フォーマットされた出力ファイルを生成します。

  • trcsess

    このユーティリティでは、セッションID、クライアントID、サービスIDなどの基準に基づいて、複数のトレース・ファイルからのトレース出力が統合されます。trcsessによりトレース情報が単一の出力ファイルにマージされた後、TKPROFを使用して出力ファイルをフォーマットできます。trcsessは、パフォーマンスまたはデバッグの目的で特定のセッションのトレースを統合する際に役立ちます。

エンドツーエンド・アプリケーションのトレースは、複数層環境のパフォーマンス上の問題の診断プロセスを単純化します。これらの環境では、エンド・クライアントからのリクエストは中間層により様々なデータベース・セッションにルーティングされるため、異なるデータベース・セッション間でクライアントを追跡するのは困難です。エンドツーエンド・アプリケーションのトレースでは、クライアントIDを使用して、データベースへのすべての層を通じて特定のエンドクライアントを一意にトレースします。

関連項目:

DBMS_MONITORおよびDBMS_SESSIONの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

オプティマイザ・ヒント

ヒントは、SQL文のコメントを介してオプティマイザに渡される命令です。ヒントを使用すると、通常はオプティマイザによって自動的に行われる決定をユーザーが行うことが可能になります。

テスト環境または開発環境で、ヒントを使用して特定のアクセス・パスのパフォーマンスをテストできます。たとえば、ある問合せに対しては、特定の索引を選択する方がよい場合もあります。この場合は、次の例に示すように、ヒントを使用して、オプティマイザが適切な実行計画を使用するように指示できます。

SELECT /*+ INDEX (employees emp_department_ix) */ 
       employee_id, department_id 
FROM   employees
WHERE  department_id > 50;

SQLチューニング・ツールへのユーザー・インタフェース

Cloud Controlは、データベース環境を集中管理するためのシステム管理ツールです。Cloud Controlは、ほとんどのチューニング・ツールにアクセスできます。

グラフィカル・コンソール、Oracle Management Server、Oracle Intelligent Agent、共通サービスおよび管理ツールを組み合せることで、Cloud Controlは、包括的なシステム管理プラットフォームを提供します。

コマンドライン・インタフェースを使用して、すべてのSQLチューニング・ツールにアクセスすることもできます。たとえば、DBMS_ADVISORパッケージは、SQLチューニング・アドバイザのためのコマンドライン・インタフェースです。

データベース管理およびチューニングのための最適なインタフェースとしてCloud Controlを使用することをお薦めします。コマンドライン・インタフェースのほうが特定の概念やタスクをよりわかりやすく説明できる場合、このマニュアルではコマンドラインの例を使用しています。ただし、その場合、チューニング・タスクには、タスクに関連付けられている主要なCloud Controlページへの参照が含まれます。