How to find slow SQL in Oracle

Oracle: 時間のかかるSQLを調べる方法

何かOracleの挙動がおかしいが、どのSQLに問題があるのか分からない。
そんなとき、SQL*Plusを使って調査する方法の一例。

※この記事は Oracle 10g について記載しています。9i 以前では一部コマンドが正しく動作しませんのでご注意ください。

ライブラリ・キャッシュ内に残っているSQLは、
動的パフォーマンスビュー「v$sql」を参照することで直近の状態を把捉できる。

1. 事象の発生した時間が特定できる場合

　最後にアクティブになった時刻「last_active_time」を「to_char」で文字列に変換し、
　それを比較することでレコードを抽出する。

　①件数のカウント

　　例1) 最後にアクティブになった時刻が「2011/5/24 19:31:00～19:31:59」のSQLをカウント

   1: select count(*)

   2:   from v$sql

   3:  where to_char(last_active_time, 'yyyymmddhh24mi') = '201105241931';

　　例2) 最後にアクティブになった時刻が「2011/5/24 19:31:20～19:31:29」のSQLをカウント
　　(like で文字列を比較することで、10秒の範囲を抽出できる)

   1: select count(*)

   2:   from v$sql

   3:  where to_char(last_active_time, 'yyyymmddhh24miss') like '2011052419312%';

 

　②SQL情報の取り出し

　　例3) 最後にアクティブになった時刻が「2011/5/24 19:31:00～19:31:59」のSQLを調べる

   1: select to_char(last_active_time, 'yyyy/mm/dd hh24:mi:ss'),

   2:        sql_id,

   3:        hash_value,

   4:        sql_text,

   5:        round(cpu_time/1000000,2),

   6:        round(elapsed_time/1000000,2),

   7:        executions,

   8:        buffer_gets,

   9:        disk_reads

  10:   from v$sql

  11:  where to_char(last_active_time, 'yyyymmddhh24mi') = '201105241931';

　　出力結果は、順に
　　　(1) 最後にアクティブになった時刻
　　　(2) SQL ID
　　　(3) ハッシュ値  ※SQL IDとともに実行計画などの更なる調査に利用
　　　(4) SQL文       ※長いSQLの場合は全文が表示されないことがある => その場合は 「sql_fulltext」を参照
　　　(5) 累計のCPU時間(秒)    ※単位を「マイクロ秒」から「秒」に変換するために10の6乗で割り算を行い、
　　　(6) 累計の経過時間(秒)     さらに小数点以下の丸めを行っている
　　　(7) 実行回数
　　　(8) 累計のバッファ取得回数    ※1回に取得するサイズは、初期パラメータのブロックサイズと同じ
　　　(9) 累計のディスク読み込み回数
　　となる。

 

2. 一回あたりの経過時間が長いSQLを抽出する

　「elapsed_time / executions / 1000000」で1回あたりの経過時間(秒)を算出することができる。
　ゼロ除算を防ぐため、「executions > 0」という条件は必須。

　①件数のカウント

　　例4) 1回あたり60秒以上かかったSQLをカウント

   1: select count(*)

   2:   from v$sql

   3:  where executions > 0

   4:    and elapsed_time/executions/1000000 >= 60;

　②SQL情報の取り出し

　　例5) 1回あたり60秒以上かかったSQLを調べる

   1: select to_char(last_active_time, 'yyyy/mm/dd hh24:mi:ss'),

   2:        sql_id,

   3:        hash_value,

   4:        sql_text,

   5:        round(cpu_time/1000000,2),

   6:        round(elapsed_time/1000000,2),

   7:        executions,

   8:        buffer_gets,

   9:        disk_reads

  10:   from v$sql

  11:  where executions > 0

  12:    and elapsed_time/executions/1000000 >= 60;

参考：
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19228-04/dynviews_2.htm