Q 質問 Oracleを利用しています。SQL文が原因でパフォーマンスが悪くなっているようです。「トレースを取って調べてみろ」と言われましたが，どうすれば良いのか分かりません

A 回答 ALTER SESSIONで取得した統計情報から，キャッシュのヒット率や実行計画などを調べます

OracleのSQLトレース機能を利用すると，データベースで実行されたそれぞれのSQL文について，（1）解析，実行，取り出しの回数，（2）CPU処理時間や経過時間，（3）物理的な読み込みと論理的な読み込みのブロック数，（4）処理したレコード数などが調べられます。

　トレースを取得する方法は，主に2通りあります。一つは現行セッションのトレースを取る方法，もう一つはデータベース・インスタンス全体のトレースを取る方法です。

表1●トレースをとるために設定が必要なinit.oraのパラメータ
　前者は，“ALTER SESSION SET sql_trace = true”というSQL文を実行すればトレースを開始できます。“… = false”と入力するか，セッションを切断するとトレースを中止します。

　後者は，init.oraでsql_trace=trueと設定するのですが，データベース全体でトレースをとるため，パフォーマンスに悪影響を及ぼします。稼働中のシステムなどでALTER SESSIONが発行できない場合以外は，前者を使うのが良いでしょう。

　また，いずれの場合も事前にinit.oraの設定が必要です（表1[拡大表示]）。デフォルト設定のままでも構いませんが，timed_statisticsを有効（true）にしないとCPU時間や経過時間などが計測できません。ただ，有効にするとデータベースの負荷も増えるので，SQLトレースのときだけ有効にします。

　トレースを実行すると，統計情報はinit.oraのuser_dump_destで指定したディレクトリにトレース・ファイル（.trc）として蓄積されます。トレース・ファイルはそのままでも見られますが，読みづらいのでTKPROFユーティリティを使って見やすい形式に整形します。TKPROFはコマンド・ラインで“tkprof トレース・ファイル名 出力ファイル名 ［SORT=パラメータ］［PRINT=数値］［EXPLAIN=ユーザー名/パスワード］”といった形式で実行します。

　主に利用するオプションはSORTとEXPLAINです。SORTは経過時間など特定の項目に関して，統計情報を降順（悪い順）にソートした結果を出力します。EXPLAINはSQL文の実行計画を出力するオプションです。

図1●TKPROFで整形した出力ファイルの例（一部を抜粋）
　TKPROFを出力した結果は，図1[拡大表示]のようになります。実行したSQL文に関して，解析（Parse），実行（Execute），取り出し（Fetch）の各フェーズの統計情報，実行計画などを確認できます。ユーザー・アプリケーションにより，結果の見方や注意すべき点は異なりますが，最低でも以下の点はチェックしておきましょう。

　まず，表形式の統計情報を見ると，Fetchするために読み込んだブロック数が分かります。（query［Fetch］+ current［Fetch］）/rows［Fetch］の値を計算すれば，SELECT文で取り出した結果の1行あたりの読み込みブロック数が分かります。1行の取り出しに対して数百ブロックも読み込まれている場合はインデックスが効率的に使用されていない可能性があります。

　キャッシュのヒット率も分かります。query［Execute + Fetch］とcurrent［Execute + Fetch］の合計値は，メモリーとディスクを含めた総読み取りブロック数を意味しています。この値に対してディスク・アクセスのブロック数（disk［Execute + Fetch］）が多い場合は，キャッシュのヒット率が低いことになります。（1 -（disk［Execute + Fetch］/（query［Execute + Fetch］ + current［Execute + Fetch］）））×100でヒット率を計算して，結果が90％未満の場合はDB_BLOCK_BUFFERSの追加などを検討する必要があります。キャッシュのヒット率は，出力ファイルの末尾に表示されるトレース・ファイル全体の統計情報も参考になります。

　SQL文の実行計画も確認する必要があります。例えば，図1では「TABLE ACCESS （FULL）OF 'EMP'」となっていることから，EMP表を全件検索していることが分かります。前述の（query［Fetch］ + current［Fetch］）/rows［Fetch］ の値と共に確認し，必要であれば，統計情報の更新やインデックスの再作成を検討します。

　実際のチューニング作業では，TKPROFのSORTオプションを使ってCPU時間（execpu，fchcpu）や経過時間（exeela，fchela）の悪いものを出力してSQL文を修正する，という作業の繰り返しになります。ただ，実行したSQL文がコミットされていないトランザクションのデータにアクセスした場合は（読み取り一貫性の処理のため）通常よりも多くのブロックにアクセスされる，など統計情報を読む際のトラップもあるので注意が必要です。

　このほか，BSTAT/ESTATユーティリティやOracle Performance Pack（別売り）もチューニングに役立ちます。SQLトレースを含めたこれらの詳細はマニュアルや日本オラクルの技術資料（http://www.oracle.co.jp/webdev/techdoc/apdev/index.html）などが参考になります。　　　（本誌）