Oracle の断片化解消についてのメモ

ちょっと入り用で調べたことをまとめるメモページとしてここを拝借

Oracle は性質上

一度テーブルにデータを挿入するとテーブルを保有する表領域(TABLESPACE)と呼ばれる領域のサイズ拡張をします

その拡張された表領域の容量は特定の行動をしてやらないとテーブル内のデータを物理削除しても容量そのものは減ることがありません

別に増えていくだけならいいんじゃないのとなるかと思います

ですが

Oracle の表領域(TABLESPACE)は Oracle インスタンスを構築する際に表領域(TABLESPACE)に割り当てる最大物理容量を決めて作っているはずです

それが俗に言う物理ファイルのデータファイルになります

そちらは最初に決めた容量以上には広がりませんので特定の表領域(TABLESPACE)だけで容量埋め尽くすとパンクします

そうならないためにも作業でたまたま拡張してしまったりした際に元の大きさまで圧縮しようと試みる一つが断片化解消です

ちなみに話逸れる

表領域とスキーマは厳密には別物だけど

正直口頭で話をするときはお互い同じものを指していると思われるくらいには混同しちゃうから細かく突っ込む必要はないでしょう

表領域が実際のテーブルやテーブル内のデータレコードを保有している

スキーマとは所属になっているテーブルを好き放題できるかどうか権限を保持しているもの的な感じだったかな

Oracle ではスキーマ＝ユーザ名として捉えるでいいかと

で話戻ってデータサイズの拡張の続き

一度データを追加したことによりテーブルのデータサイズ拡張をされると例え増やしたレコードを単純削除(DELETE)だけでは表領域上で拡張占有したデータサイズが変わることはありません

この時に専門用語だとハイウォーターマーク(HWM)なるものが出てくるも今は割愛

別にわからないから載せないってわけでもないこともなかったり、、、

これはメモリ自動解放機能のあるプログラミング言語みたいに適宜解放などしてくれずいつの間に表領域を食いつぶしてますとなる原因となりますと

たとえ話として

元々は〇〇テーブルのデータサイズは３MBの１０レコードしかありませんでした

そこに５レコード増やしたことにより２MB追加されたことで〇〇テーブルは５MBの１５レコードを保持していますと

そこへ不要となった合計１MB分のデータ量を持つ３レコードを削除しましたと

そうすると単純引き算により〇〇テーブルはデータサイズ４MBの１２レコードを保持している

・・・・・・とはなりません

これは今時点の OracleDB 仕様としては間違った認識です

※未来の OracleDB の仕様がどう変わるかは担保しませんと自分へ

正解は〇〇テーブルのデータサイズは一度増えた５MBのままレコードのみ１２になったまま保持しているとなるわけですと

これが通称断片化と呼ばれるもの

１５レコードに増えたものが１２レコードに減ったときも頭から揃えてたりしてくれませんので断片化と言われる所以だとかないとか

断片化をについて整理したところで元々の本題

断片化解消にあたっての特殊な工程不要で行える方法は以下の３つと補足の１つ

TABLEの TRUNCATE
Export/Import（Drop前提）
ALTER TABLE MOVE（ALTER INDEX REBUILD）
ALTER TABLE SHRINK SPACE（これが補足の１つ）

それぞれどのようにするかを解説

TABLEのTRUNCATE

これは簡単な話で下記の簡単すぎる恐怖の SQL を実行する

TRUNCATE TABLE <対象のテーブル名>;

たったこれだけであら不思議

テーブルのサイズも減ったし件数も減りましたと

これは DELETE による全件削除と違って対象のテーブルが拡張して表領域から割り当てられていたデータサイズまで TRUNCATE してくれます

Export/Import

実際に鍵となるのはこれと一緒に行うべき Drop にあり

Drop でテーブルを削除すると削除されたテーブルが表領域を拡張して確保していたデータサイズも解放します

なのでデータを確保したまま断片化解消する一つとして Export でデータをバックアップ後に Drop を行い

そこから Import でデータを復活させるとあら不思議もともとあったサイズよりちいさくなっちゃったとなりますと

これは Expdp/Impdp でももちろんかまいません

その場合は Impdp の Exists Action には Replace を設定してテーブルがあったら作り直して入れる方式にすれば解決

大切なのはテーブルを Drop し作成し直すことにより断片化の解消になります

ただしこの時注意しなければいけないのはパフォーマンスを測定するためにデータレコード増減繰り返しまくったデータ

これをなんの気なしに Export 後に Import による入れ直しした場合

せっかく大量にレコード追加して DELETE して断片化させたのに元に戻ってるとなります

ALTER TABLE MOVE

これは上記２つの副次効果的なのとは違い最初から断片化を解消させることを目論んだ対応ですと

行うのは以下の一つのテーブルに一つの SQL でOK

ALTER TABLE <対象のテーブル名> MOVE;

これだけすよと

断片化解消するのが狙いなので SQL を実行する前と後に該当のテーブルサイズか表領域の空き容量を見るなりしとかないと効果の程がわかりません

ちなみに INDEX も保持しているテーブルなら先程の SQL に続けて次の SQL も実行してあげましょう

ALTER INDEX <対象のインデックス名> REBUILD;

こちらもテーブルと一緒で前後でサイズ比較しないと効果不明となります

注意事項として

後段の SHRINK にも出てくる LOB セグメントを有するテーブルは対象外なため

上記に加えて LOB 向け MOVE の SQL にする必要があります

これは後日追記、、、

ALTER TABLE SHRINK SPACE

これは補足とした一つ

補足の理由は単純

これを行うには５つの条件を考慮していないといけない難点もあったりします

１．Oracle のバージョンが 10g 以上である。
２．ローカル管理表領域、自動領域管理セグメントである必要がある。
３．行管理が有効化されている必要がある。
４．Long 列や LOB セグメントを含むテーブルは対象外である。
５．行移行や行連鎖の状況によっては思ったほど縮小されないことがある。
Archive Redo Blog より

それぞれの番号について

１．これはバージョン古いと使えないらしいけど今の世の中どれだけ古いのが残っているのだろうか、、、

２．こちらは以下の通りの確認が取れれば OK

DBA_TABLESPACESのEXTENT_MANAGEMENT が LOCAL、SEGMENT_SPACE_MANAGEMENT が AUTO になっていることを確認
DB & SQL 技術ブログより

３．これは後述の SQL 本体発行前後に挟むように入れればいいらしい

ALTER TABLE <対象のテーブル> ENABLE ROW MOVEMENT;
ALTER TABLE <対象のテーブル> DISABLE ROW MOVEMENT;

４．これは desc なりテーブルの情報から該当の型を保持させるテーブルかどうか確認するしかないでしょう

５．これはもう SQL 発行した前後でのサイズ比較しか確かめようがありません

でこれらの考慮をクリアしたら以下の SQL を発行すれば OK

ALTER TABLE <対象のテーブル> ENABLE ROW MOVEMENT;
ALTER TABLE <対象のテーブル> SHRINK SPACE CASCADE;
ALTER TABLE <対象のテーブル> DISABLE ROW MOVEMENT;

SHIRINK 自体は２行目が本命

SPACE の後ろの CASCADE は紐づく INDEX も同時に対処させたいときには記載するものです

不要ならいらないもの

てことで今は超えないといけないものがあるためにちょっととっつきにくいと思わされるかもなので補足扱い

ただしこの SHRINK 自体は MOVE よりもお手軽に且つUNDO、アーカイブ以外の容量を取らない実行が可能だったりするメリットもあったりもします

詳しい比較情報はこちら（Shift the Oracle）のページか上記の引用元ページに多少あります

後はこういったページを参照用のとして置いとくと

Oracleにおける断片化の種類と解消方法～表領域、セグメント、エクステント、ブロック、テーブル、索引
オラクルエンジニア通信 – 技術資料、マニュアル、セミナーより

解放されない – oracle 表領域縮小 shrink
CODE Q&A 解決方法より

Oracle の断片化解消についてのメモ

いいね:

関連

コメント

共有:

いいね:

関連

コメント