4.5 パフォーマンスアップ

4.5.3 実行計画の確認方法

次に実際に実行された実行計画を確認し、どのくらいリソースが使用されたか、どのようなアクセスパスが使用されたかを確認します。
実行計画の確認方法として、以下のような方法があります。

EXPLAIN PLAN文（1SQL文単位に確認）
SQLトレースの取得＋TKPROFによる解析
STATSPACKレポート
AUTO TRACEオプションの使用
v$sql_planを参照
Oracle Enterprise ManageのGUlツールの使用

上記について、使用方法、特徴、サンプルを紹介します。
まず、それぞれの文を実行する前に、実行するユーザの所有表として、実行計画および結果を格納するPLAN_TABLEを作成します。
ORACLE_HOME/rdbms/admin/utlxplan.sqlを実行して作成します。

EXPLAIN PLAN文（1SQL文単位に確認）

SQL*Plusにて、次の文を実行します。

【STEP1】
EXPLAIN PLAN FOR分析したいSQL文；
その結果がPLAN_TABLE表に格納されます。
実行結果は取得されず、解析のみを行います。
格納された情報はそのままでは理解しにくいため、結果を成形し、実行結果を確認します。
結果を成形するには以下のSQL文を実行します。

【STEP2】
ORACLE_HOME/rdbms/admin/utlxpls.sql

EXPLAIN PLAN文と、utlxpls.salで成形した実行計画は、次のように表示されます。

表SQLトレースの取得＋TKPROFによる解析（複数のSQLをまとめて分析）

この方法は、実際に処理にかかったCPUタイムやアクセスしたブロック数が、各フェーズ（解析、実行、フエッチ）ごとに表示される点が特徴です。
SQLのトレースを取得する範囲は、初期化パラメータで指定します。
SQL_TRACEパラメータの値をTRUEにしてからFALSEに戻すまでの間のSQL文のトレースがすべて取得されます。

SQLを実行しているインスタンスレベルで取得したい場合には初期化パラメータファイルでSQLTRACE=TRUEを指定します。
SQL文を実行するセッション単位でトレースを取得するには、以下を指定します。

ALTER SESSION SET SQL_TRACE:TRUE

トレースファイルは、初期化パラメータ「user_dump_dest=ディレクトリ名」で指定したディレクトリに取得されます。

TKPROFユーティリティを使用すると、取得されたファイルを分析する際、問題のあるSQL文を抽出して、分析結果をレポートすることができます。
問題のあるSQL文とは、たとえば、以下のような点に注目してレポートに抽出します。

物理IOが大量に必要なSQL
大量にブロックアクセスを行ったSQL（物理IOなしのアクセスを含む）
CPU時間が長くかかったSQL

TKPROFはOSプロンプトで実行します。
上記のような問題のあるSQLを、物理IO数が多い順やCPU時間の長い順にソートし、上位n個を出力ファイルに列挙するという指定をオプションで指定します。
実行の構文は次のとおりです。

TKPROF取得したトレースファイル名アウトプットファイル名オプション;

STATSPACKレポート

STATSPACKレポートは、指定した時間帯でリソース使用率の高いSQL文を見つけ出すために使用することができます。
レポートには、「物理読み込みブロック数の多い順」「アクセスするブロック数の多い順」「解析に時間のかかった順」にSQL文がリストされているため、どのSQL文がボトルネックになっているか特定することができます。

また、統計情報は、統計情報のスナップショットを随時取得しておき、統計情報を取得したいインターバルを決めます（たとえば、トランザクションピーク時の午前10：00から12：00まで）。
そして、レポートを作成するときに、開始時に取得したスナップショットのIDと、終了時に取得したスナップショットのIDをパラメータで指定し、その間の差分をレポートとして作成します。

また、レポートの詳細さを示すレベルを指定することもできます。
デフォルト値は5です。
レベル7を指定すると、RACのチューニングをする際に必要な統計情報も収集することができます。

STATSPACKレポートの作成方法を以下に示します。
詳細はOracleのマニュアル「パフォーマンスガイドおよびリファレンス」を参照してください。

【STEP1】
SQL*Plusを起動し、Sysdba権限をもつユーザで接続

【STEP2】
ORACLE_HOME/rdbms/admin/spcreate.sqlを実行して環境を作成
（スクリプト実行中に下記内容を対話式に聞かれる）

【STEP3】
スキーマユーザ「perfstatユーザ」の作成
（途中でパスワードを指定する必要あり）

【STEP4】
指定した表領域に必要なオブジ工クトを作成
（オブジ工クトを作成する表領域と一時表領域を途中で指定する必要あり）

環境を構築したら、スナップショットを取得します。
また、時間の統計情報を収集するために、初期化パラメータTIMED_STATISTICSを設定します。

【STEP1】
SQL*PIusを起動し、SySdbaの権限をもつユーザで接続し、時間の統計情報取得の設定を行う
ALTER SYSTEM SET TIMED STATISTICS＝true;
（時間統計情報は負荷が高いため、統計情報を収集しないときはFALSEを指定しておくこと）

【STEP2】
SQL*PIusを起動し、Perfstatユーザで接続
perfstat.snapプロシージャを実行（適切な間隔で収集し続ける）
EXECUTE perfstat.snap;
スナップショットを自動収集するためには、DBMSJOBパッケージを使用しますが、その他の設定も必要です。
詳しくはマニュアルを参照してください。
スナップショットを取得した後、開始スナップショットIDと終了スナップショットIDを指定してレポートを出力します。

【STEP3】
SQL*PIusを起動し、Perfstatユーザで接続。次のSQL文を実行します。
ORACLE_HOME/rdbms/admin/spreport.sql;
このSQLを実行すると、これまでに取得されたスナップショットのリストが表示されます。
レポートを作成する際に必要な開始と終了のスナップショットを指定するプロンプトに答えます。

AUTOTRACEオプションの使用

AUTOTRACEオプションはSQL*Plusから簡易に実行計画を確認できる機能です。
このオプションを指定することによって、実行結果の出力を抑制し、実行計画と統計情報のみを表示することができます。

ただし、詳細な統計情報までは取得できないので、索引などのオブジェクトを追加した後、実行計画が変更されたかを確認するためなどに使うことができます。
また、AUTOTRACEオプションはデフォルトで使用できるようにはなっていないため、使用する場合には以下の準備が必要です。

【STEP1】
sysdba権限をもっているユーザで、以下のSQL文を実行します。
ORACLE_HOME/sqlplus/admin/plustrce.sql

【STEP2】
STEPlのSQL文の実行によって作成されたロールplustraceを、AUTOTRACEオプションを使用するユーザに付与します。
GRANT plustrace TOユーザ名:

【STEP3】
plustraceロールを付与されたユーザで、AUTOTRACEオプションの使用を可能にします。

【STEP4】
SQL*Plusで以下を実行します。
set auto trace{on|on explain| on statistics | traceonly|}

【STEP5】
SQL文を実行します。

まず、どのSQL文がリソース使用上問題があるかを突き止める必要があります。
そのためには、インスタンス全体の統計情報を確認しなければなりません。
その意味で、まずSTATSPACKレポートを取得し、実行回数が多く、リソースを多く使用しているSQL文を抽出します。
そして、問題をもつSQL文にめぼしをつけ、そのSQL文を分析するセッションを決め、EXPLAIN PLAN文を実行するか、そのセッションでSQL文のトレースを取得して分析します。

v$sql_planを参照

Oracle10gから、v$sql_planを問い合わせて、任意の文の実行計画、コスト計算結果を取得することができるようになりました。
実行した文のSQL_ID（データベース内で管理されているSQL文の識別子）を取得するためには、v$sqlを問い合わせます。

上記SQL文で取得したsql_idを使用してv$sql_planを問い合わせるためには、DBMS_XPLAN.DISPLAY_CURSOR()ファンクションを使用します。

非常に見やすく、有効な情報を取得することができますが、v$sql_planへの問い合わせは、それ自体がコストのかかる処理であるため、ピーク時に大量の文を発行することは避けてください。

AUTOTRACEオプションの使用

Oracle10gのDBコンソール機能から取得されたSQL情報の一部を以下に表示します。
ここでは、過去に実行されたSQLのうち、リソース消費率が高かったものをリスト表示し、その実行計画をGUI画面から確認することができます。
DBコンソール機能を使用して、SQLチューニングアドバイザによるSQLチューニング推奨事項を取得することもできます。