Oracle shrinkする上での注意点

Oracleを使用していると、断片化がつらい。
頻繁に更新しているインデックスの断片化が特につらい。
そして表領域を圧迫していく。。

そういう時にEnterpriseEditionであればindex rebuild onlineなんてできるけど、
StandardEditionだとできないのでshrinkで対応。

以下のように記述する。

ALTER INDEX indexname SHRINK SPACE;

このshrinkはSEでもオンラインで可能なので、いいんですが注意が必要。

1. ディスク領域

アーカイブログが大量に出力されるのでディスク領域に注意が必要。
単純なディスクサイズの問題もあるし、もし高速リカバリエリアにアーカイブログを保存している場合は使用率の確認が必要。
高速リカバリエリアのサイズを確認しながら、またはdfコマンドで確認しながらshrinkする。

• 確認SQL

>SELECT nvl(SUM(PERCENT_SPACE_USED),0)   FROM v$flash_recovery_area_usage;

NVL(SUM(PERCENT_SPACE_USED),0)
------------------------------
                         32.86

2. UNDO領域の使用率

shrink中はUNDO領域の使用も大幅に増える。
UNDO領域を食い潰すと更新できずにセッションが詰まる、自動拡張にしていても拡張のタイミングで更新が待たされセッションが詰まる。
よって、UNDO領域の使用率がいっぱいにならないように確認しながら、UNDO領域のパージを待ったりして進める必要あり。

• 確認SQL

select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') "TIME",
       round("USED_SIZE(MB)", 3) "USED(MB)",
       round("TBS_SIZE(MB)", 3)  "TBS(MB)",
       round("USED_SIZE(MB)"/"TBS_SIZE(MB)" * 100, 3) "USAGE(%)"
  from (select sum(BYTES)/1024/1024 "USED_SIZE(MB)"
          from DBA_UNDO_EXTENTS
         where STATUS in ('ACTIVE', 'UNEXPIRED')
           and TABLESPACE_NAME = 'UNDOTBS1') ,
       (select sum(BYTES)/1024/1024 "TBS_SIZE(MB)"
          from DBA_DATA_FILES
         where TABLESPACE_NAME = 'UNDOTBS1') ;

SQLの参考

第28回 UNDO表領域の管理～保存期間の自動チューニング～

3. 解放時のロック

SHRINK SPACEを実施してると断片化を解消している処理の間は行ロックなので、ほぼオンライン。
しかし、その空いたエクステントを解放するときはテーブルロックをとるので解放する領域が大きいとセッションが詰まってこれまた大変なことに。。。

SHRINK SPACE COMPACTにすることで、エクステントを解放はせずに断片化だけ解消するのでセグメントのサイズは変わらないが、
空いた領域を再利用してくれるので増加は抑えられる。
または、SHRINK SPACE COMPACTをしておいて、負荷の少ない時間帯に解放してあげるのが良い。

以上のことを踏まえて、うちではこの辺を自動化して運用してます。