【Oracle】遅いSQLを特定するSQL - サービス業で働く社内SEのブログ

ブログの移設ついでに過去のブログ内容を投稿。

DBAであればOEMでSQL監視から特定してみたり
AWRレポートを発行したり等でどのSQLがボトルネックになっているかを調査する事が出来ますが
開発者サイドに立った場合になかなかこういったところまで踏み込む事は難しいと思います。
そもそもちゃんとした会社であれば実装部隊とチューニング部隊とが分かれている(と聞かされている)ため
携わる機会すら無いかもしれません。

そういった場合を考慮しSQL1発で取得出来るクエリを作成しました。

SELECT
    DISTINCT S.SID
  , S.STATUS
  , S.PROGRAM
  , q.MODULE
  , q.SQL_ID
  , dbms_lob.substr(q.SQL_FULLTEXT,3000,1 ) SQL
  , s.LAST_CALL_ET 実行時間
  , q.PARSING_SCHEMA_NAME
  , q.FIRST_LOAD_TIME
  , q.LAST_LOAD_TIME
  , s.OSUSER
  , S.MACHINE
FROM
    v$session s
  , v$sql q
WHERE
    s.SQL_ID = q.SQL_ID
--↓ここの数字を変えます。このサンプルでは10秒以上実行され続けているSQLを特定
AND s.LAST_CALL_ET > 10
--↑ここの数字を変えます。このサンプルでは10秒以上実行され続けているSQLを特定
AND q.PARSING_SCHEMA_NAME <> 'SYSMAN'
--↓使用する開発環境ソフトウェアは除外すると見やすいかも
AND NOT q.MODULE IN ('cse.exe','osqledit.exe','ob10.exe')
--↑使用する開発環境ソフトウェアは除外すると見やすいかも
AND S.STATUS = 'ACTIVE'
ORDER BY
    1

V$SESSIONとV$SQLへの参照が必要ですので、
これらは必要なロールを付与する等で対応して下さい。

上記のサンプルでは10秒以上実行され続けているSQLを特定します。
私は開発環境ソフトウェアに関して
・CSE
・ObjectBrowser
・OSqlEdit
を使い分けて使用していますので
これらから発行されたSQLは対象として引っかからないように除外してあります。

このSQLは現在実行されているものにスポットが当たりますので
例えば監視アプリやバッチ等で何分かおきに発行するように組み込む事により
実行に時間がかかっているSQLの特定が可能となります。
これで後は該当SQLの実行計画を見るなりして
速度改善・パフォーマンスチューニングに繋げれます。