SQLチューニングの基盤となる統計情報：Oracle SQLチューニング講座（5）（1/4 ページ）

本連載では、Oracleデータベースのパフォーマンス・チューニングの中から、特にSQLのチューニングに注目して、実践レベルの手法を解説する。読者はOracleデータベースのアーキテクチャを理解し、運用管理の実務経験を積んでいることが望ましい。対象とするバージョンは現状で広く使われているOracle9iの機能を基本とするが、Oracle 10gで有効な情報も随時紹介していく。（編集局）

[亀田明裕，株式会社アゲハ]

連載目次

　前回「チューニングが必要なSQLを洗い出す」では、動的パフォーマンスビューを使用してチューニング対象となり得るSQLを洗い出す方法を説明しましたが、チューニングを行うためには、SQLの実行計画など、より詳細な情報が必要となります。今回は、これらの情報を取得する方法、また収集した情報の分析方法について説明していきます。

SQL詳細情報の取得

　SQLチューニングを行う際に重要となる情報としては、SQLの実行計画や実行時のパフォーマンスに関する統計情報があります。

　これまでに説明してきたように、SQLの実行速度は、その実行計画によって大幅に異なってきます。そのため、SQL実行時の実行計画やSQLが使用するリソースなどを調査し、その実行計画が適切なものであるかを評価する必要があります。SQLチューニングに必要な情報を取得するにはいくつかの方法がありますが、代表的な以下の3つに関して説明します。

	実行計画の取得	取得の手軽さ	アプリケーション処理全体の情報取得	各SQLの実行時間に関する情報	取得によるシステム負荷の低さ	情報取得範囲
SQLトレース＋TKPROFユーティリティ	◎	△	◎	◎	△	インスタンス、または特定のセッションが実行する全SQL
SQL*PlusのAUTOTRACE機能	○	◎	×	△(*)	◎	自セッションのSQL
動的パフォーマンスビューの利用 （V$SQL、 V$SQL_TEXT、V$SQL_PLAN）	○	○	△	○	○	インスタンスで実行されたSQL
表1　SQL詳細情報を取得する3つの方法の比較 (*) SQL*Plusでset timing onを設定することで代替可能

　表1は、各方法の特徴をまとめたものです。使用目的に応じて、適切な取得方法を選択してください。例えば、チューニングの効果をより正確に測るためには、該当アプリケーション、もしくはSQLのSQLトレースを取得します。SQLの実行計画を簡単に確認したい場合には、SQL*PlusのAUTOTRACE機能を使用するのが効率的でしょう。V$SQLなどで調査したSQLなど、過去に実行されたSQLの実行計画を調べるためには動的パフォーマンスビューを使用します。それでは、それぞれの方法について説明していきます。

SQLトレース、TKPROFユーティリティの使用方法

　SQLトレースは、実行されたSQLの実行計画やパフォーマンス統計情報などをテキストファイルに出力する機能で、特定のセッション、もしくはインスタンスの全セッションの情報を取得できます。SQLトレースの出力結果は、そのままでは非常に分かりにくいため、TKPROFユーティリティを使用してファイルの内容を見やすい書式に整形します。

SQLトレース、TKPROFユーティリティの実行手順

　それでは実際にSQLトレース、TKPROFユーティリティを使用して、SQLの詳細情報を取得する手順を説明します。なお、下記手順はSQL*Plusからの実行例ですが、同様の文をアプリケーション中に埋め込むことで、アプリケーションで実行されるSQLのトレースを取得することも可能です。

1. SQLの実行ユーザー（ここではSCOTTで接続）でデータベースに接続する

$ sqlplus scott/パスワード

　Oracle Net経由にてSQLトレースを取得する場合には、接続方法（専用サーバ接続、共有サーバ接続）によってトレースファイルの作成ディレクトリが異なりますので注意が必要です。

• 専用サーバ接続：初期化パラメータUSER_DUMP_DESTで指定されたディレクトリ
• 共有サーバ接続：初期化パラメータBACKGROUND_DUMP_DESTで指定されたディレクトリ

2. 時間に関連する統計の収集を行うように設定する

SQL> ALTER SESSION SET TIMED_STATISTICS=TRUE;

　Oracle9i以降では、STATISTICS_LEVELパラメータの設定値が「ALL」または「TYPICAL（デフォルト値）」の場合には、TIMED_STATISTICSパラメータは「TRUE」に設定されるため、明示的な設定は必要ありません。

3. SQLトレースの取得を開始する

SQL> ALTER SESSION SET SQL_TRACE=TRUE;

　セッションが終了、もしくは明示的にSQLトレースの取得を終了するまで、該当セッションで実行されるすべてのSQLに関してトレースが取得されます。

4. チューニング対象SQLを実行する

SQL> SELECT COUNT(*) FROM orders   2  WHERE o_orderdate BETWEEN '96-04-01' AND '97-04-01' AND ⇒O_ORDERSTATUS='O';

（注：⇒記号は表示の都合で折り返していることを表します）

　一度に大量のSQLを実行してSQLトレースを取得する場合には、その分のトレースファイルが出力できるだけの十分なディスク容量があることを確認してください。また、「MAX_DUMP_FILE_SIZE」パラメータに明示的に値を設定している場合には、トレースファイルの最大サイズが「設定値×OSブロックサイズ」に制限されるため、同一セッション内で大量のSQLを実行する際には注意が必要です（Oracle R8.1.6以降からMAX_DUMP_FILE_SIZEのデフォルト値はUNLIMITEDです）。

5. SQLトレースの取得を停止し、SQL*Plusを終了する

SQL> ALTER SESSION SET SQL_TRACE=FALSE; SQL> exit

6. TKPROFユーティリティの実行

$ tkprof ora_11111.trc 11111.prf explain=scott/パスワード ⇒aggregate=no sys=no sort=fchela

（注：⇒記号は表示の都合で折り返していることを表します）

　トレースファイルの出力先ディレクトリに移動し、作成されたトレースファイルを整形します（対象となるトレースファイルは、SQLトレースを取得した時刻を基に特定してください）。

　TKPROFユーティリティは、トレースファイルを見やすいように整形する際に、さまざまなオプションを指定できます。大量のSQLがトレースファイルに含まれている場合には、オプションを指定することで、より効率よくチューニング作業を進めることができます。

　上記の例では、「EXPLAIN」オプションで実行計画を出力し、「AGGREGATE」オプションで重複SQLを個別に出力、「SYS」オプションでリカーシブコール注1 を排除、そして「SORT」オプションでフェッチ時の経過時間順にSQLを並べ替えるというようにトレースファイルを整形しています。

注1：リカーシブコール

SQL文を処理するために、内部的に発行されるSQL文を指します。例えば、表の存在や権限のチェックなどを行うためのSQLなどがあります。

　表2に便利なオプションをまとめましたが、そのほかのオプションについては、マニュアル「データベース・パフォーマンス・チューニング・ガイドおよびリファレンス」を参照してください。

オプション名	説明
EXPLAIN	TKPROFユーティリティ実行時の実行計画を出力するためのユーザー名／パスワードを指定する
AGGREGATE	DEFAULT：YES YESを指定した場合、同一のSQLは集計されて、1回だけ出力されるNOを指定した場合、SQL単位の集計は行われず、実行された回数分出力される。SQLを個別に調査したい場合には、NOを設定する
SORT	指定したオプションによって降順でSQLが出力される EXEELA：実行時の経過時間順 EXEDSK：実行時のディスクアクセスブロック数順 EXEQRY：実行時のアクセスブロック数順 FCHELA：フェッチ時の経過時間順 FCHDSK：フェッチ時のディスクアクセスブロック数順 FCHQRY：フェッチ時のアクセスブロック数順（そのほかにも多数のオプションがある）
SYS	DEFAULT：YESNOを指定すると、リカーシブコールを整形したファイルに含めない
表2　TKPROFユーティリティの主要オプション