【ORACLE】長時間流れているSQLの処理状況を調査

IT

※経験則に基づくものであり、ORACLEの仕様を把握して書いてるわけではないです

投げたSQL（SELECT文とか）が全然かえってこないという場合

下記２つの見方でそのSQLの進行状況がある程度把握できる

（１）V$SESSION.SEQ#が変化するか

　UPDATEやDELETEなどを実行したのにいつまでたっても終わらない場合、

　先に該当レコードや表が掴まれている（LOCKされている）可能性がある。

　その場合、「待たされてるほう」のセッションは先行セッションのLOCK解放待ちになり、

　その間V$SESSION.SEQ#が同じ値のまま動かなくなる。

　逆に言うと、V$SESSION.SEQ#が見るたび変化してるようなら、

　そのセッションはちゃんと動いている。（少なくともLOCK解放待ちではない）

　この数値の変動具合が大きいほど進み具合が大きいような気がするが、

　詳しい仕様はよくしらない。

　また、特殊なケースではこの数値が変化していても

　実際は全然進んでいない　というようなこともある。

　⇒元表に項目追加したことによる関連マテビューのリフレッシュ等で

　　この現象を目にしたことがある。

　　SEQ#が動いてるので放置してたら

　　6時間以上経っても終わらなかった（普段2時間くらい）ので強制KILLした

　あと、この数値は６５５３５あたりを境に０に戻ってループし始める。

　このあたりは実体験に基づくものであり、正確な仕様はわからない。

（２）V$SESSION_LONGOPS.TIME_REMAININGを見る

　長時間走行しているSQLの「残り時間」がここに秒の単位で表示される。

　1800と出てくれば1800秒＝あと30分くらいかかりそう、

　というORACLEの内部的な推定値をこの項目にもっている。

　V$SESSION_LONGOPSは、V$SESSIONとSID・SERIAL#で結合すればよいが

　すべてのセッションに対してこのV$SESSION_LONGOPSが存在するわけではないので、

　外部結合にする必要がある。

　ちなみに、この数値は稀にあてにならないことがある

　⇒たとえば「あと２時間（7200秒）」とか出ていたので

　　ぼーっと待ってたら10分くらいで完了してたことがある

　ORACLEがブロックサイズとかから計算で求めてるだけらしいので

　これも、あくまで目安の数値。

　それと、UNIX系OSだけなのかもしれないが、

　何かの拍子にV$SESSION_LONGOPSにアクセスする行為

　それ自体が異常に重くなることがある。

　夜間処理等の忙しい時間帯にはあまり見ないほうがいいかもしれない。

　（そういうときほどトラブるから見たいんだけどね）