Oracle SQLチューニング講座（4）

チューニングが必要なSQLを洗い出す　Page 2

株式会社アゲハ
亀田 明裕
2004/9/22

■動的パフォーマンスビューを使用した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）かかっていると推測されます。（次ページへ続く）

2/3

　Index
連載 Oracle SQLチューニング講座（4）
チューニングが必要なSQLを洗い出す
  Page 1
・チューニング対象SQLの洗い出し
・対象SQLの洗い出し方法
Page 2
・動的パフォーマンスビューを使用したSQLの洗い出し
　−合計実行時間の長いSQL
  Page 3
　−バッファ読み込みブロック数が多いSQL
　−ディスク読み込みブロック数が多いSQL
　−実行回数の多いSQL
・SQL全文の取得方法


連載　Oracle SQLチューニング講座

TechTargetジャパン

Database Expert フォーラム 新着記事

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）

RSSフィード

＠IT 新着記事

キャリアアップ

- PR -
＠IT Sepcial
→ ＠IT Special ヘ

イベントカレンダー

PickUpイベント

- PR -
もっと見る
- PR -

お勧め求人情報

ホワイトペーパー（TechTargetジャパン）

＠IT Sepcial
→ ＠IT Special ヘ
ソリューションFLASH