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

Oracle SQLチューニング講座(4):チューニングが必要なSQLを洗い出す (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)かかっていると推測されます。

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。