ハイウォーターマーク（HWM）の確認方法

ORACLEの運用において、ハイウォーターマーク（HWM）を把握する必要があります。
UPDATEやDELETEの繰り返しで、断片化が発生し、検索時間が無駄にかかってしまうことがあるためです。

本記事では、HWMの把握方法を紹介します。

HWMの把握方法
1. テーブルのHWMの把握方法
2. インデックスのHWMの把握方法
注意点
まとめ

HWMの把握方法

ハイウォーターマーク（HWM）を把握するためには、静的データディクショナリビューを検索します。

テーブルのHWMを確認するためには、USER_TABLES、ALL_TABLES、DBA_TABLESを検索します。
インデックスのHWMを確認するためには、USER_INDEXES、ALL_INDEXES、DBA_INDEXESを検索します。

また、パーティション分割されているテーブルのパーティションごとのHWMを確認するためには、USER_TAB_PARTITIONS、ALL_TAB_PARTITIONS、DBA_TAB_PARTITIONSを検索します。
同様に、パーティション分割されているインデックスのパーティションごとのHWMを確認するためには、USER_IND_PARTITIONS、ALL_IND_PARTITIONS、DBA_IND_PARTITIONSを検索します。

USER_*****は、接続しているユーザーに属するテーブル・インデックスの情報を確認できます。
ALL_*****は、接続しているユーザーがアクセスできるテーブル・インデックスの情報を確認できます。
DBA_*****は、全テーブル・インデックスの情報を確認できますが、DBAロールが付与されているユーザーでないと検索できません。

また、注意点として事前に統計情報が取得されている必要があります。

テーブルのHWMの把握方法

以下、テーブルのHWMの把握するための、具体的な検索SQLとサンプルの出力結果です。

-- テーブルのHWMの確認
SELECT TABLE_NAME      -- テーブル名
      ,BLOCKS          -- データが格納されている領域（ORACLEブロック単位）
FROM   DBA_TABLES
;

TABLE_NAME        BLOCKS
--------------- --------
TEST_TABLE1        16667
TEST_TABLE2        16667
TEST_TABLE3        16643

-- パーティションテーブルのHWMの確認
SELECT TABLE_NAME      -- テーブル名
      ,PARTITION_NAME  -- パーティション名
      ,BLOCKS          -- データが格納されている領域（ORACLEブロック単位）
FROM   DBA_TAB_PARTITIONS
;

TABLE_NAME      PARTITION_NAME      BLOCKS
--------------- ----------------- --------
TEST_TABLE3     TEST_TABLE3_01        8314
TEST_TABLE3     TEST_TABLE3_02        8329

TABLE_NAMEには、テーブル名が入ります。
PARTITION_NAMEには、パーティション名が入ります。
BLOCKSには、HWMを示すデータが格納されている領域（ORACLEブロック単位）が入ります。

インデックスのHWMの把握方法

以下、インデックスのHWMの把握するための、具体的な検索SQLとサンプルの出力結果です。

-- インデックスのHWMの確認
SELECT INDEX_NAME      -- インデックス名
      ,LEAF_BLOCKS     -- リーフブロック領域（ORACLEブロック単位）
FROM   DBA_INDEXES
;

INDEX_NAME       LEAF_BLOCKS
--------------- ------------
PK_TEST_TABLE1          3760
PK_TEST_TABLE2          3760
PK_TEST_TABLE3          3559

-- パーティションインデックスのHWMの確認
SELECT INDEX_NAME      -- インデックス名
      ,PARTITION_NAME  -- パーティション名
      ,LEAF_BLOCKS     -- リーフブロック領域（ORACLEブロック単位）
FROM   DBA_IND_PARTITIONS
;

INDEX_NAME      PARTITION_NAME      LEAF_BLOCKS
--------------- ------------------ ------------
PK_TEST_TABLE3  PK_TEST_TABLE3_01          1778
PK_TEST_TABLE3  PK_TEST_TABLE3_02          1781

INDEX_NAMEには、インデックス名が入ります。
PARTITION_NAMEには、パーティション名が入ります。
LEAF_BLOCKSには、リーフブロック領域（ORACLEブロック単位）が入ります。
インデックスは、リーフブロックのみの確認となります。
他にルートブロックやブランチブロックもインデックス領域の構成要素ですが、リーフブロックが占める割合が大きいので、目安としてはリーフブロックの確認のみで問題ないケースが多いです。

注意点

注意点としては、上で少し触れましたが、事前に該当テーブル・インデックスの統計情報を取得が必要です。

以下、SQLPLUSから統計情報を取得するコマンドのサンプルです。

-- 統計情報の取得
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'ユーザー名', TABNAME=>'テーブル名', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', CASCADE=>TRUE);