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

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

トレースファイルの注目ポイント

　トレースファイルには多くの情報が出力されています。ここでは、チューニングを行う際に注目すべきポイントを、TKPROFユーティリティにより整形後のファイルを例に説明していきます。

図1　TKPROFにより整形後のトレースファイル

（1）	SQL	実行したSQL文
（2）	CPU時間 （1/100秒単位）	Parse、Execute、Fetchの各CPU時間と合計CPU時間を表示。1/100秒以下の場合、0秒として計算される
（3）	経過時間 （1/100秒単位）	Parse、Execute、Fetchの各経過時間と合計経過時間を表示。1/100秒以下の場合、0秒として計算される
（4）	ディスクアクセスブロック数	ディスクI/Oが発生し、読み取られたブロック数
（5）	バッファアクセスブロック数	メモリ上でアクセスされたブロック数
（6）	行数	このSQLを実行した結果、処理された行数
（7）	ライブラリキャッシュでのミス	値が0の場合、共有プール上の解析結果が存在したため、解析処理が排除されたことを表す
（8）	オプティマイザモード	SQLを実行したときのオプティマイザモードを表す
（9）	解析したユーザー	SQL文を解析したユーザーIDを表す
（10）	SQLが実行されたときの実行計画	SQLが実行されたときの実行計画を表す （詳細は後述）
（11）	TKPROFが実行されたときの実行計画	TKPROFが実行されたときの実行計画を表す （詳細は後述）
表4　TKPROF後のトレースファイルの主な出力項目

SQL実行時の実行計画の見方

　図1の（10）は、SQLが実行されたときの実行計画を表しています。2行目に「TABLE ACCESS FULL」と出力されているため全表スキャンが行われたことが確認できます。「OBJ#」のカッコ内の数字はオブジェクト番号「OBJECT_ID」を表しており、DBA_OBJECTSディクショナリ・ビューを参照することで、対象のオブジェクトを特定できます。また、その後ろに表示されている「cr」「r」「w」「time」のパラメータはそれぞれ表5のような内容を示しています。

cr	バッファ上から読み込まれた合計ブロック数
r	ディスクから読み込まれた合計ブロック数
w	ディスクに書き込まれた合計ブロック数
time	合計経過時間（1/1000000秒、マイクロ秒）
表5　実行計画中のパラメータの意味

　これらの項目に出力される値は、親ステップ（SORT処理）の値と子ステップ（TABLE ACCESS処理）の値との合計値になります。そのため、各ステップでの処理時間やアクセスされたブロック数などの情報は、ステップごとの差分から読み取る必要があります。

　図1の（10）、「time」の値を例に挙げて説明すると、最初のステップであるTABLE ACCESS FULLは、「60530996」であり、この値が全表スキャンにかかった時間になります。次のステップであるSORT GROUP BYは、「62654805」となっていますが、この値には前ステップのTABLE ACCESS FULLの値も含まれています。そのため、純粋なSORT GROUP BYの経過時間は、「62654805 - 60530996 = 2123809」、すなわち約2.1秒であったことが分かります。

TKPROF実行時の実行計画の見方

　図1の（11）は、TKPROFユーティリティを実行した時点での実行計画、つまり、現時点でこのSQLを実行した際に使用される実行計画となります。SQLトレースを取得した時点から、表、索引の統計情報が更新されている場合や、索引の作成／削除などが行われていると、SQLトレース取得時点の実行計画と異なってくる場合があります。

SQLの分析

　図1の（11）、実行計画の結果は、よりインデントが深い（右の方に出力されている）処理が先に実行され、結果が上のレベルに渡されます。同一のレベルの処理が存在する場合は、より上にある処理が先に実行されます。

　そのため、この実行計画の結果からは、LINEITEM表への全表スキャンが発生してから、GROUP BY処理が行われていることが確認できます。また、（3）SQLの経過時間が「62.81秒」であり、（10）の詳細で確認した全表スキャンのみの時間は「約60.5秒（time＝60530996）」であることが分かります。

　処理時間のほとんどを全表スキャンが占めていることから、この点を改善することが実行時間の短縮につながることが分かります。また、（4）と（5）から、ほぼ全ブロックのアクセスがディスクI/Oを伴っていることも確認できます。（1）のSQLを見るとWHERE条件が含まれていることから、条件列のカーディナリティを調査し、索引作成が有効であるか調査を進めることになります。