HWエンキューでロックされているセグメントを特定する

Oracle

はじめに

Oracleまわりのトラブルの調査のため、HWエンキューでロックされたセグメントを特定する方法を調べてみました。

環境

HWエンキューってなんぞ？

Oracleのエンキューとは

エンキューは、データベース・リソースへのアクセスをシリアライズする共有メモリーの構造（ロック）です。
(Oracleエンキュー名)

です。
HWエンキューは、セグメントのHWM(High Water Mark; 最高水位標)を動かすときに行われるロックです。保持されているロック情報を示すV$LOCKビュー上では、TYPE='HW'のレコードとして表されます。

HWエンキューでロックしているセグメントを特定する

1. ロックされたHWエンキューが待っているデータのファイルとブロックを特定

まず、V$LOCKからTYPEが'HW'の行を探し、そのデータブロックアドレスのファイル番号とブロック番号を取得します。

HWエンキューの場合、V$LOCKのID1, ID2は下記のようになります。

  • ID1: HWMが変更されるセグメントが格納されている表領域の番号
  • ID2: セグメントヘッダの相対データブロックアドレス

(パフォーマンス・ビューを使用したインスタンスのチューニング - HWエンキュー)


また、ストアドファンクションDBMS_UTILITY.DATA_BLOCK_ADDRESS_FILEとDBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCKにデータブロックアドレスを渡して実行すると、そのデータブロックアドレスのファイル番号とブロック番号がわかります。

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE
引数 dba にデータ・ブロック・アドレスを渡して実行すると、データ・ブロック・アドレスのファイル番号部分を返す
(DATA_BLOCK_ADDRESS_FILEファンクション - DBMS_UTILITY)

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (
   dba NUMBER)
  RETURN NUMBER;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK
引数 dba にデータ・ブロック・アドレスを渡して実行すると、データ・ブロック・アドレスのブロック番号部分を返す
(DATA_BLOCK_ADDRESS_BLOCKファンクション - DBMS_UTILITY)

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (
   dba NUMBER)
  RETURN NUMBER;


そこで、V$LOCKのID2をDATA_BLOCK_ADDRESS_FILEとDATA_BLOCK_ADDRESS_BLOCKに渡せばよさそうです。
SQLはこんな感じになります。

SELECT
     DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(ID2) "FILE_ID",
     DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(ID2) "BLOCK_ID"
FROM
     V$LOCK
WHERE
     TYPE = 'HW'

結果はこんな感じ。ファイル番号とブロック番号がわかりました。

FILE_ID BLOCK_ID
20 29


2. ロックしているセグメントを特定
1.でわかったファイル番号とブロック番号から、HWエンキューでロックしているセグメントを特定します。
こんな感じのSQLを実行します。

SELECT
     OWNER,
     SEGMENT_NAME,
     SEGMENT_TYPE
FROM
     DBA_EXTENTS
WHERE
     FILE_ID = 20
AND 29 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

WHEREの条件の値（上記の20と29）には、1.で調べたものを入れてください。
結果はこんな感じ。

OWNER SEGMENT_NAME SEGMENT_TYPE
ORACLE SYS_LOB000001234C01234$$ LOBSEGMENT

どうやら SYS_LOB000001234C01234$$ というセグメントがロックされているらしい、ということがわかりました。
この情報をもとに原因を特定して、対策を行いましょう。

まとめ

HWエンキューでロックされているセグメントを特定する方法を調べてみました。
これでHWエンキューでずーっとロックされてエラいことになっても大丈夫ですね！

参考