チューニングが必要なSQLを洗い出す：Oracle SQLチューニング講座（4）（2/3 ページ）

» 2004年09月22日 00時00分公開

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

動的パフォーマンスビューを使用したSQLの洗い出し

　SQLの洗い出しのために使用する動的パフォーマンスビューは、主にV$SQL、V$SQL_TEXT、V$SQL_PLANの3つで、これらは共有SQL領域に保持されているSQLの情報を表示します。この共有SQL領域は、SQLを再利用するための情報がキャッシュされる領域となります。実行されたSQLの情報は必ずこの領域にキャッシュされますが、空き領域が不足した場合、新しいSQLのために、実行頻度が低いSQLの情報が追い出されてしまいます。そのため、この方法は、これまで実行されたすべてのSQLの情報を取得できるわけではない点に注意してください。

　必要な情報は、直接SELECT文で確認することもできますし、また、一部の情報は、Oracleが標準で提供しているSTATSPACKユーティリティを使用することでも取得可能です。STATSPACK自体の説明については、別の機会に譲ります。

　表3は、SQL調査に使用するV$表の列名、意味となります。ここで紹介したV$SQL_PLANは実行計画を確認するビューとなりますので、次回説明します。


ビュー名	主な格納情報	主な列名	列値の意味
V$SQL	・SQLの先頭1000bytes ・SQLの累積リソース使用状況	SQL_TEXT	SQLの先頭1000bytes
		SORTS	SORTの回数
		FETCHES	FETCHの回数
		EXECUTIONS	実行回数
		USERS_EXECUTING	現在実行中のユーザー数
		PARSE_CALLS	解析コールの回数
		DISK_READS	ディスク読み込み数
		BUFFER_GETS	バッファ読み込み数
		ROWS_PROCESSED	SQLが戻す行数
		CPU_TIME	処理に使用したCPU時間（マイクロ秒）
		ELAPSED_TIME	処理に使用した経過時間（マイクロ秒）
		ADDRESS	ほかのViewとの結合に使用
		HASH_VALUE	ほかのViewとの結合に使用
V$SQL_TEXT	・SQLの全文	SQL_TEXT	64bytes単位で分割されたSQL
		PIECE	分割されたSQLの断片番号
		ADDRESS	ほかのViewとの結合に使用
		HASH_VALUE	ほかのViewとの結合に使用
V$SQL_PLAN	・SQLの実行計画	OBJECT_NAME	実行計画中の表、索引名
		OPERATION	実行計画オペレーション
		OPTIONS	実行計画オペレーションのオプション
		OPTIMIZER	オプティマイザのモード
		DEPTH	実行計画のTreeの深さ
		ADDRESS	ほかのViewとの結合に使用
		HASH_VALUE	ほかのViewとの結合に使用
表3　SQLの調査に使用する主なV$表

合計実行時間の長いSQL

　V$SQLを参照して、対象となるSQLを洗い出すには、リスト1に示したSQLを実行します。ORDER BY句の条件を変更することで、目的に合わせて、洗い出し条件を変更できます。合計実行時間が長いSQLを洗い出す場合には、ORDER BY句の条件に“elapsed_time”を指定します。なお、例ではトップ10を表示していますが、最終行のrownumの指定を変更することで、出力する件数を変更することが可能です。

SET LINES 140

      COL sql_text            FORM A140

      COL buffer_per_run      FORM 999999999999

      COL disk_per_run        FORM 999999999999

      COL cpu_time            FORM 999999999999

      COL elapsed_time        FORM 999999999999

      SELECT * FROM

      (SELECT

        sql_text,address,hash_value,parse_calls,executions,

        buffer_gets,disk_reads,

        buffer_gets/executions buffer_per_run,

        disk_reads/executions disk_per_run,cpu_time,

        elapsed_time

      FROM v$sql

      WHERE executions>0

      ORDER BY elapsed_time desc)   -- ←この条件を変更する

      WHERE rownum <= 10;           -- ←表示件数はこの値を変更する

リスト1　V$SQLを参照するSQL

SQL_TEXT

      -----------------------------------------------------------------

      -----------------------------------------------------------------

      ----------

      ADDRESS  HASH_VALUE PARSE_CALLS EXECUTIONS BUFFER_GETS DISK_READS 

      BUFFER_PER_RUN  DISK_PER_RUN      CPU_TIME  ELAPSED_TIME

      -------- ---------- ----------- ---------- ----------- ---------- 

      -------------- ------------- ------------- -------------

      SELECT count(*) FROM lineitem WHERE l_orderkey=:b1

      563F6734  349970465           1          3      325716     325606

         
      108572        108535      44190000     186913533