Oracle（10g）・動的再編成作業　自分用（我流）メモ

自分のとこでは、データベースの空き領域確保を目的としてテーブルの動的再編成を行うことがよくあるので、自分用メモとして残しておく。

断片化しているか否か？　再編成によって“浮く”領域量はどれくらいか？

下記のようなコマンドで得られるテーブル毎のサイズは、「そのテーブルに登録されている実データサイズ」ではなく、上述の「そのテーブルという入れ物のサイズ」となる。つまり、これを見るだけでは、“入れ物に対してデータがギッチリ詰まっている”か、それとも“スッカスカ”か、が判断できない。

select tablespace_name, segment_name, segment_type, sum(bytes) bytes, count(*) cnt
from user_extents
where tablespace_name = 'TABLESPACE_NAME' and
segment_type = 'TABLE'
group by tablespace_name, segment_name, segment_type
order by segment_name

では、「そのテーブルに登録されている実データサイズ」を計るにはどうすれば良いか、だが、色々と探してみたがドンピシャの方法というのが見つからない。そのため、我流（といっても、それぞれのスクリプトは色んなところからの拾いものではあるが）ではあるが、以下のような方法を取っている。

下記のプロシージャを実行すると、

    -  変数宣言
VARIABLE unformatted_blocks NUMBER
VARIABLE unformatted_bytes  NUMBER
VARIABLE fs1_blocks         NUMBER
VARIABLE fs1_bytes          NUMBER
VARIABLE fs2_blocks         NUMBER
VARIABLE fs2_bytes          NUMBER
VARIABLE fs3_blocks         NUMBER
VARIABLE fs3_bytes          NUMBER
VARIABLE fs4_blocks         NUMBER
VARIABLE fs4_bytes          NUMBER
VARIABLE full_blocks        NUMBER
VARIABLE full_bytes         NUMBER
VARIABLE partition_name     VARCHAR2
VARIABLE segment_name       CHAR(7)

    -  実行
exec DBMS_SPACE.SPACE_USAGE('SCHEMA_NAME', 'TABLE_NAME', 'TABLE', -
       :unformatted_blocks, :unformatted_bytes, -
       :fs1_blocks, :fs1_bytes, :fs2_blocks, :fs2_bytes, -
       :fs3_blocks, :fs3_bytes, :fs4_blocks, :fs4_bytes, -
       :full_blocks, :full_bytes, -
       :partition_name -
)

PRINT unformatted_blocks
PRINT unformatted_bytes
PRINT fs1_blocks
PRINT fs1_bytes
PRINT fs2_blocks
PRINT fs2_bytes
PRINT fs3_blocks
PRINT fs3_bytes
PRINT fs4_blocks
PRINT fs4_bytes
PRINT full_blocks
PRINT full_bytes

下記のような結果を得ることができる。

FS1_BLOCKS
    - --------
0


 FS1_BYTES
    - --------
0


FS2_BLOCKS
    - --------
16


 FS2_BYTES
    - --------
131072


FS3_BLOCKS
    - --------
650


 FS3_BYTES
    - --------
5324800


FS4_BLOCKS
    - --------
288025


 FS4_BYTES
    - --------
2359500800


FULL_BLOCKS
    - ---------
251413


FULL_BYTES
    - --------
2059575296

ここで、fs[1-4]で表される分類は、下記のような意味がある。

fs1 : 空き領域が 0- 25%のブロック
fs2 : 空き領域が25- 50%のブロック
fs3 : 空き領域が50- 75%のブロック
fs4 : 空き領域が75-100%のブロック

つまり上述のプロシージャは、「テーブルを構成するブロックをその密度によって、4種類に分類・集計してくれる」プロシージャ。これを使えば、おおよその「そのテーブルに登録されている実データサイズ」を計ることができないか、と考えた。色んな考え方はあると思うが、僕が計算するときには最も単純な考え方によるもので、「各分類の平均使用領域量に基づいて、実データサイズを計る」というもの。つまり、

FS1_BYTES * 0.875 ≒ FS1ブロックに分類されるデータの実サイズ (A)
FS2_BYTES * 0.625 ≒ FS2ブロックに分類されるデータの実サイズ (B)
FS3_BYTES * 0.375 ≒ FS3ブロックに分類されるデータの実サイズ (C)
FS4_BYTES * 0.125 ≒ FS4ブロックに分類されるデータの実サイズ (D)

(A) + (B) + (C) + (D) + FULL_BYTES ≒ そのテーブルに登録されている実データサイズ

という感じになる。こうして算出されたサイズと、「そのテーブルという入れ物のサイズ」とを比較したときの、その差が「再編成実施によって開放される領域量」（実際には追加割り当て量を定義するので、その分は差し引かれるが）となる。たとえばデータベースの空き領域確保が第一命題のときには、この差が大きいものから優先的に再編成をやっていくことになるかと思う。

動的再編成の注意点

Oracleデータベースは、更新処理を含めて、通常通り使用できる状態を維持しながら、再編成ができる（動的再編成・オンライン再編成）ようになっている（どのバージョンからかは失念）。ただ、対象のテーブルに複合インデックス、又はファンクションインデックスが付されている場合には、動的再編成を行うことはできない。その場合の再編成時の手順は次のようになる。

インデックスの削除
テーブル再編成実施
インデックスの追加

それと、このテーブルの動的再編成は、DBMSの内部処理としては、再編成用のテーブルをもう1つ作成して再編成が完了したタイミングで切り替える、というようなことをしている（っぽい）。このため、対象テーブルと同容量以上の空き領域が、一時的に必要になる。

加えて、テーブルの再編成によってデータの物理的な格納場所が変わるため、インデックスが無効になることがある。したがって、テーブル再編成後は速やかにインデックスの再編成(REBUILD)を実施しておいたほうが良さそう。

動的再編成の所要時間は、対象テーブルに登録されている実データ量に依存する。あまりにも実データ量が多い場合はテーブルのDROP/CREATEで作り直すという方法を取ったほうが早く終わる場合もあるんじゃないかなと。

データのエクスポート
テーブルのDROP&CREATE
インデックスの作成
データのインポート

動的再編成の手順

インデックスの削除（複合インデックス、又はファンクションインデックスが付されている場合）
テーブルの再配置可能化
- テーブルの物理的な再配置を可能にする。

ALTER TABLE table_name ENABLE ROW MOVEMENT;

テーブルのSHRINK
- データを再配置し，未使用領域を解放する。

ALTER TABLE table_name SHRINK SPACE;

テーブルへの領域割り当て
- SHRINKで必要最小限まで縮小されたテーブルに対して，EXTENT領域を割り当てる。

ALTER TABLE table_name ALLOCATE EXTENT (SIZE sizeK);

-テーブルの再配置可能化解除 - テーブルの物理的な再配置を禁止する。

ALTER TABLE table_name DISABLE ROW MOVEMENT;

インデックスの再編成
- インデックスを再編成する。

ALTER INDEX index_name REBUILD;

（必要な場合）事前に削除したインデックスがあれば，再作成する。
統計情報の取り直し

begin 
dbms_stats.gather_table_stats(ownname=> 'OWNNAME',tabname=> 'TABLE_NAME' ,estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>DBMS_STATS.AUTO_CASCADE,degree=> null,no_invalidate=> FALSE,granularity=> 'AUTO',method_opt=> 'FOR ALL COLUMNS SIZE AUTO'); 
end;

テーブルの状態確認
- テーブルのステータスが"VALID"であること。

SELECT TABLE_NAME, STATUS FROM ALL_TABLES WHERE STATUS <> 'VALID' AND OWNER = 'OWNNAME';

インデックスのステータスが"VALID"であること。

SELECT INDEX_NAME, TABLE_NAME, STATUS FROM ALL_INDEXES WHERE STATUS <> 'VALID' AND OWNER = 'OWNNAME';

統計情報が失効していないこと。

SELECT TABLE_NAME, STALE_STATS FROM ALL_TAB_STATISTICS WHERE TABLE_NAME IN ('TABLE_NAME') ORDER BY TABLE_NAME;

今後も継続してOracleを使えるような仕事をすることになるかはわからないけど、自分用メモとして。。

えいのうにっき

a-knowの日記です

Oracle（10g）・動的再編成作業　自分用（我流）メモ

断片化しているか否か？　再編成によって“浮く”領域量はどれくらいか？

動的再編成の注意点

動的再編成の手順

断片化しているか否か？ 再編成によって“浮く”領域量はどれくらいか？

動的再編成の注意点

動的再編成の手順

断片化しているか否か？　再編成によって“浮く”領域量はどれくらいか？