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

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

動的パフォーマンスビューを使用した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

リスト2　合計実行時間の長いSQLの出力例（クリックすると別ウィンドウで表示します）

　出力例を見ると、ELAPSED_TIME列の値が「186.913533秒」であることを確認できます。このSQLはEXECUTIONSが「3」となっているため、1回の実行当たり「約62秒」（≒186.91353/3）かかっていると推測されます。