SQLトレースとは？
SQLトレースは、OracleデータベースでSQLのパフォーマンスに関する情報を文単位で出力する機能のことです。
SQLトレースでは、次のような情報を確認できます。
- 解析，実行，取り出しの回数
- CPU処理時間や経過時間
- 物理的な読み込みと論理的な読み込みのブロック数
- 処理したレコード数
そのため、データベースのパフォーマンス低下の調査に有効な機能です。
SQLトレースの取得方法
SQLトレースの取得方法には、大きく分けて次の2つがあります。
- セッションのトレースを取る方法
- データベース・インスタンス全体のトレースを取る方法
セッションのトレースを取る方法では、「ALTER SESSION SET sql_trace = true」というSQL文を実行することで、トレースを開始します。
「… = false」と入力する、もしくはセッションを切断することでトレースの中止が可能です。
データベース・インスタンス全体のトレースを取る方法では、init.oraでsql_trace=trueと設定します。
ただし、データベース全体でトレースをとるためパフォーマンスに悪影響を及ぼしてしまうのです。
もし、稼働中のシステムでALTER SESSIONが発行できないとき以外であれば、セッションのトレースを取る方法を使うとよいでしょう。
「セッションのトレースを取る方法」と「データベース・インスタンス全体のトレースを取る方法」のどちらにせよ、事前にinit.oraの設定が必要です。
デフォルトのままだと「timed_statistics」を有効にしない場合、CPU時間や経過時間などが計測できません。
また、SQLトレースの手順は次の通りです。
- 事前準備
- SQLトレースを開始
- SQL文の実行
- SQLトレースを終了
- 出力結果を整形
SQLトレースの取得準備
ここでは、SQLトレースを取得する準備について見ていきます。
- user_dump_dest：トレースファイルの出力先ディレクトリ
- max_dump_file_size：トレースファイルの最大サイズ
- timed_statistics： 「true」が設定してあることを確認(CPU時間や経過時間などの計測に必要)
- セッション単位：alter session set sql_trace = true ;
- システム単位：alter system set sql_trace = true ;
- セッション単位：alter session set sql_trace = false ;
- システム単位：alter system set sql_trace = false ;
SQLトレースの整形方法
SQLトレースは、処理を実行したプロセスの「トレースファイル」に出力されます。
取得するときに「TRACEFILE_IDENTIFIER=’SQLTRACE」の指定を行った場合は、トレースファイルにタグが付いているため、見つけるのが簡単なります。
トレースファイルに出力されたSQLトレースは「TKPROF」で整形します。
TKPROFとは、生のSQLトレースを見やすい形に整形するユーティリティです。
使用方法は次の通りです。
tkprof <SQLトレースファイル名> <成形後のトレースファイル>
SQLトレースの見方は？
ここでは、TKPROFで整形後のSQLトレースの見方を紹介します。
サンプルとして以下の出力例を見てみます。
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno; call count cpu elapsed disk query current rows ---- ------- ------- --------- -------- -------- ------- ------ Parse 1 0.16 0.29 3 13 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.03 0.26 2 2 4 14 Misses in library cache during parse: 1 Parsing user id: (8) SCOTT Rows Execution Plan ------- --------------------------------------------------- 14 MERGE JOIN 4 SORT JOIN 4 TABLE ACCESS (FULL) OF 'DEPT' 14 SORT JOIN 14 TABLE ACCESS (FULL) OF 'EMP'
上記のSQLトレースは、上から順に次のようになっています。
- SQL文
- call：コールの種別（Parse=解析、Execute=実行、Fetch=フェッチ）
- count：実行回数
- cpu：CPU使用時間(秒)
- elapsed：経過時間(秒)（CPU使用時間も含む）
- disk：物理ディスクからバッファキャッシュに読み込んだブロック数
- query：読み取り一貫性モードでバッファから読み込んだブロック数（物理ディスクからバッファキャッシュに読み込んだブロック数の一部またはすべてを含む）
- current： カレントモードでバッファから読み込んだブロック数（物理ディスクからバッファキャッシュに読み込んだブロック数の一部またはすべてを含む）
- rows：行数
- Misses in library cache during parse：パース時にライブラリキャッシュにヒットせずハードパースされた回数
- Parsing user id：パースを実行したユーザID
- Rows：各オペレーションが戻した行数
- Execution Plan：EXPLAIN PLANによって生成された実行計画
【関連記事】
