チューニングメモ20071231 - 闘うITエンジニアの覚え書き

遅いSQLを特定する †

　http://www.atmarkit.co.jp/fdb/rensai/orasql04/orasql04_1.html

• チューニング対象SQL
  • 1実行当たりの実行時間が長いSQL
  • ディスク読み取りブロック数が多いSQL
  • バッファの読み取り数が極端に多いSQL
  • 実行回数が極端に多いSQL

• 対象SQLの洗い出し
  • 動的パフォーマンスビューを使用したSQLの洗い出し
    　[デメリット]
    　　・1回当たりの実行時間などは平均値しか取得できない
    　　・メモリ上にキャッシュされているSQLの情報しか確認できない

-- 合計実行時間の長いSQL
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回の実行当たりの秒数 = ELAPSED_TIME / EXECUTIONS となる

-- バッファ読み込みブロック数が多いSQL
-- ※大量のバッファ読み込みを行っているSQLは、適切でない索引や、
-- 　連結索引の一部を使用している可能性がある。

先のSQLのORDER BY に buffer_gets を指定する

-- ディスク読み込みブロック数が多いSQL
-- ※ディスク読み込みが多発している場合、効率の悪い索引を使用している、
-- 　もしくは全表スキャンを行っている可能性が考えられます。
-- 　ディスクI/Oは、メモリI/Oと比べて大幅に処理速度が劣るため、
-- 　チューニングによりディスクI/Oを減らすことができないか検討します。

先のSQLのORDER BY に disk_reads を指定する

-- 実行回数の多いSQL

先のSQLのORDER BY に executions を指定する

-- SQL全文の取得方法
-- 初めに、V$SQLからADDRESS列、HASH_VALUE列の値を確認し、下記のSQLを実行

set pages 100 feed off timing off echo off lines 140
SELECT sql_text
  FROM v$sqltext
 WHERE hash_value=V$SQLから取得したHASH_VALUE値
   AND address='V$SQLから取得したADDRESS値'
 ORDER BY piece;