Oracle9i 物理設計

第6部 断片化の解決
日本オラクル クロスインダストリー本部 OracleDirect SCグループ
中家 裕之

約半年に渡って解説してきた「Oracle9i物理設計」もとうとう最終回になりました。物理設計のうち皆さんが興味を持ちそうなところを中心に解説してきたつもりでしたがいかがでしたでしょうか？今後もiSeminarやOTNを通じてOracleDirect のメンバーで技術情報を発信してまいりますのでご期待ください。

◆ 第1章 断片化解決の概要

はじめに
第5部の解説を受けて、本項では断片化の解決方法について解説いたします。断片化の解消の作業は出来る限り発生しない方がDBAの方々にとっても幸せだと思いますので、該当の断片化が発生しにくくなる事前の対策についても出来る限り解説します。


断片化解決の概要
断片化の解決方法は、断片化現象によりある程度は異なりますが、共通の手法で解決できるものもあります。まずは断片化現象ごとの解決方法を一覧にまとめてみました。次章以降で個別に解説したいと思います。


表1：Oracleの物理構造と断片化、解決方法
階層 断片化現象 解決方法(再編成方法) 解説章
データファイル（表領域） ファイルレベルの断片化 OSコマンドによるデフラグ 第2章
データファイルレベルの未使用領域の発生 表領域レベルの再編成
第2章
データファイルの縮小 第2章
セグメント 位置の高いハイウォーターマーク セグメントレベルの再編成 第3章
セグメントレベルの未使用領域の発生 エクステントの切り捨て 第3章
セグメントレベルの再編成 第3章
階層の深いインデックス セグメントレベルの再編成 第3章
エクステント 不連続のエクステント セグメントレベルの再編成 第3章
表領域レベルの再編成 第2章
ブロック 行移行 セグメントレベルの再編成 第3章
行移行しているレコードのみの再編成 第4章
行連鎖 ブロックサイズ変更して再編成 第4章
ブロック内の未使用領域の発生 セグメントレベルの再編成 第3章


◆ 第2章 データファイル（表領域）レベルの断片化対策

OSコマンドによるデフラグ
UNIXやLinuxなどではファイルの断片化はあまり気にする必要はないと言われていますし、Windows系OSでもひどくないレベルの断片化はパフォーマンスへの影響はほとんどありません。ただ、システム構築後一度もデフラグを実施したことがないのであれば最低限断片化具合の調査くらいはした方がいいかもしれません。Windows系OS上でOracleを構築する場合はデフラグツールでファイルレベルの断片化を解消します。例えばWindows2000系のOSの場合だと、「スタートボタン」→「プログラム」→「アクセサリ」→「システムツール」→「ディスクデフラグ」で起動できます。なお、デフラグツール等で最適化を実施する場合はインスタンスは停止してください。


表領域レベルの再編成
Oracle9iからある表領域に格納されているテーブルをまるごとExport/Importすることができるようになりました。この機能を使えば、ある表領域全体をまとめて再編成することが可能です。以下表領域レベルの再編成の手順になります。

（1）Exportを実施
Export時にTABLESPACEオプションに表領域名を指定すると、指定した表領域に存在するオブジェクトがExportされます。EXP_FULL_DATABASE ロールを所有しているユーザーで実施して下さい。
c:\> exp system/manager tablespace=test01 file=c:\temp\test.dmp log=c:\temp\testexp.log

（2）オブジェクトの削除＆再作成
表領域のパラメータを変更したい場合は表領域を削除→再作成します。表領域のパラメータをしない場合でも表領域を削除→再作成した方が楽です。TABLESPACEオプションを指定したExportで作成したダンプファイルにはCREATE TABLESPACE文は含まれませんので、表領域は先に作成しておく必要があります。もしExport時と異なるパラメータでテーブルやインデックスを作成したい場合は、空のテーブルやインデックスを新しいパラメータの値で事前に作成してください。 
SQL> drop tablespace test01 including contents and datafiles;
SQL> create tablespace test01 datafile ....;

（3）Importを実施
Import時はTABLESPACEオプションは不要です。もし（2）で空のセグメントを作成している場合はIGNOREオプションを「Y」で指定してください。IMP_FULL_DATABASE ロールを所有しているユーザーで実施して下さい。
c:\> imp system/manager tablespace=test file=c:\temp\test.dmp log=c:\temp\testimp.log

実作業例についてはこちらを参照してください。


データファイルの縮小
初期に確保したデータファイルの容量に比して実際にデータが入ってこないと、未使用領域として場合によってはディスク容量を圧迫します。データが入ってくる見込みがない場合などは未使用領域を切り捨てることも可能です。データファイルを縮小する場合は、以下のSQLで行います。
SQL> alter database datafile 'c:\temp\test.dbf' resize 100m;
データファイル名及び縮小後のサイズを指定します。ただし、切り捨てることが出来るのはHWM（ハイウォーターマーク）以降の領域のみです。HWMについては第5部 第2章をご参照ください。仮にHWM以前に断片化された未使用領域があっても縮小の対象にはなりません。縮小というよりはお尻の未使用部分の切捨てという処理になります。ちなみに縮小だけでなく拡張も同様の構文で可能です。

ファイルレベルの断片化の防止策
ファイルレベルの断片化を防ぐためには以下のような方策を採ると有効です

（1）データベース作成前にデフラグを実行する
データベース作成前にデフラグな必要なくらい断片化しているようでは、その後に作成するファイルも断片化する可能性が高いです。表領域を配置する予定のディスク（ドライブ、パーティション）については、新規のものでない限りは事前にデフラグを実行しましょう。

（2）極力専用の物理ディスク、ないしパーティションにデータファイルを配置する
データベースを構成しないファイル群と同じドライブ（パーティション）にデータファイルを配置すると断片化しやすいです。小規模システムでディスクが一つといった場合でも、新たにパーティションを作成して、そこにはデータベースと無関係なファイルを置かないようにして下さい。

（3）事前の見積をきちんと行う
他のレベルの断片化の予防策でもありますが、事前にセグメントやデータファイルの見積を行って必要分を確保しておくと、いざ予想が外れて断片化が発生しても見積もらずに適当に確保した場合より断片化の影響を抑えることが出来ます。

（4）データファイルの自動拡張に頼らない
データファイルが自動拡張すると、そのドライブ(パーティション)に該当のファイルしかない場合以外は断片化が発生し得ます。極力事前見積りして、必要十分なファイル容量を事前に確保してください。第2部 第2章の最後の項も併せてご参照ください。


◆ 第3章 セグメントレベルの断片化対策

セグメントレベルの再編成：概要
個別のセグメントの再編成方法はいくつか存在します。次項以降で以下の方法について解説します。
  • テーブル、インデックスのExport/Import
  • テーブルの移動
  • インデックスの再編成

テーブル、インデックスのExport/Import
テーブルと、そのテーブルに付随するインデックスをまとめて再編成する場合に有効です。複数のテーブルやインデックスをまとめて実施することも可能です。しかし再編成中に該当のテーブルにアクセスすることは出来ません。以下に手順を示します。第2章の表領域レベルの再編成と手順はほぼ同じなので、詳細な実行ログは省きます。

（1）Exportを実施する
再編成を目的にExportを実施する際の注意点としては、
  • 手順2でテーブルを再作成する場合は、付属するインデックスや制約やトリガーなどもExportしておきましょう。別途作成SQLがあればそれを再実行してもいいのですが（その方が再編成が早く終わるケースもあります）、まとめてExport/Importした方が楽です。
  • ローカル管理表領域の場合、COMPRESSオプションは意味がありませんので、指定は不要です。 
例）
c:\> exp scott/tiger tables=(emp, dept) file=c:\temp\test.dmp log=c:\temp\testexp.log

（2）オブジェクトの削除＆再作成
Exportしたセグメントを削除します。セグメントのパラメータを変更したい場合はセグメントを一旦削除して空で再作成します。

（3）Importを実施
もし（2）で空のセグメントを作成している場合はIGNOREオプションを「Y」で指定してください。
c:\> imp scott/tiger full=y file=c:\temp\test.dmp log=c:\temp\testimp.log

テーブルの移動
Oracle8iからテーブルの存在する表領域を以下の構文で変更することができるようになりました。結果として、新しい表領域にテーブルが移動します。この際に再編成も実施されます。もちろんSTORAGE句をつけてテーブルの大きさを調整することも可能です。
SQL> alter table table1 move tablespace tbs1;
このSQLでtablespace句に現在テーブルが存在する表領域と同じ表領域を指定するか、あるいはtablespace句自体を省く（alter table table1 move）と、同じ表領域に移動します。異なる表領域に移動する場合は当然ですが、同じ表領域に移動する場合でもワークとして再編成後のテーブルの大きさと同じだけの空き領域が移動先表領域に必要になります。


インデックスの再編成
インデックスについてはOracle7.3より再編成専用のSQL構文が用意されています。 
SQL> alter index index1 rebuild;
こちらの構文を利用すると、インデックスをDROPしてからCREATEするよりも高速に再編成が出来ます。TABLESPACE句をつけて格納表領域を変更したり、STORAGE句をつけてインデックスの大きさを調整したりすることも出来ます。 
SQL> alter index index1 rebuild tablespace tbs2 storage(initial 50m);

エクステントの切り捨て
初期に確保したセグメント容量に比して実際にデータが入ってこないと、未使用領域として残りつづけて場合によってはディスク容量を圧迫します。データが入ってくる見込みがない場合などは未使用エクステントを切り捨てることも可能です。エクステントを切り捨てる場合は以下のSQLで行います。 
SQL> alter table table1 deallocate unused;
インデックスの場合は「alter table」が「alter index」になるだけで同様の構文です。このSQLを実行すると該当のセグメントのHWM以降の未使用のエクステント全てが切り捨てられます。未使用のエクステントを少し残したい場合は 
SQL> alter table table1 deallocate unused keep 120m;
とkeep句にサイズを指定すると、この例だと120MBになるようにHWM以降の未使用エクステントを切り捨てます。ただし、ローカル管理表領域の場合エクステントのサイズが何らかの形で表領域レベルで決まっています。例えば上記SQLをUNIFORMサイズ18MBの表領域のセグメントに対して実行した場合、120MBではなく126MBの大きさになります。


セグメントレベルの断片化の防止策
セグメントレベルの断片化を防ぐためには以下のような方策を採ると有効です。ただ、索引の場合はREAD ONLYであるとか、値がシーケンシャルに増えていくINSERTしかないといったような場合を除き、いずれはセグメントレベルの断片化が発生します。テーブルも、削除が多いテーブルはどうしてもセグメントレベルの断片化が発生しやすくなります。

（1）事前の見積をきちんと行う
他のレベルの断片化の予防策でもありますが、事前にセグメント大きさのの見積を行って必要分を確保しておくと、いざ予想が外れて断片化が発生しても見積もらずに適当に確保した場合より断片化の影響を抑えることが出来ます。

（2）PCTFREE/PCTUSEDの調整
(1)と同様PCTFREE/PCTUSEDをきちんと行うと再編成の機会が減ります。詳細については第3部、第4部をご参照ください。

（3）セグメントのパーティション化
Enterprise Edition及びPartitioning Optionをご利用の場合に限られますが、テーブルやインデックスをパーティション化すると断片化の発生を防ぐないし軽減することができます。例えば3ヶ月保存で3ヶ月以上経過したデータを削除する仕様になっている場合、一月ごとにパーティション化して、月次処理で一番古いパーティションを削除するようにすると、月次処理によってHWMが無駄に高くなることがなくなります。もし単にDELETE文で削除した場合はHWM以前の未使用領域が大量に発生し、すぐにその未使用領域が再利用されなければ第5部 第3章で解説したような影響が出ます。また、再編成を実施する際にもパーティション単位に再編成を実施できますので、再編成対象の総セグメント容量を減らし、短時間で再編成が実施できるという効果が期待できます。


◆ 第4章 ブロックレベルの断片化対策

行移行の再編成
行移行の解消はセグメントレベルないし表領域レベルの再編成を実施すれば解消できます。しかし、ほんの一部のレコードのみが行移行している状態であれば、以下の手順で行移行しているレコードのみを狙い撃ちした再編成が可能です。

（1）CHAINED_ROWSテーブルを作成する
ORACLE_HOME配下のrdbms\admin\utlchain.sqlスクリプトファイルを再編成対象テーブルを所有しているユーザーで実行して、CHAINED_ROWSテーブルを作成します。一度作成すればOKです。

（2）ANALYZEを実施する
list chained rowsオプションをつけてanalyzeを実施します。
SQL> analyze table test list chained rows;
上記SQLを実行すると、CHAINED_ROWSテーブルのHEAD_ROWID列に行移行が発生しているレコードのROWIDが格納されます。

（3）ワークテーブルに行移行しているレコードを退避させる
再編成対象テーブルと同じ列構成のワークテーブルを作成し、ここにCHAINED_ROWSテーブルに格納されているROWIDのレコードをコピーします。
SQL> create table work_test as select * from test
  2  where exists (select 'X' from chained_rows
  3  where head_rowid = test.rowid);

（4）行移行しているレコードを削除
SQL> delete from test where exists (select 'X' from chained_rows
  2  where head_rowid = test.rowid);

（5）ワークテーブルに退避させたレコードを戻す
INSERTされたレコードでは行移行は発生しません。
SQL> insert into test select * from work_test;

（6）ワークテーブルを削除
SQL> drop table work_test;

実作業例についてはこちらを参照してください。


行連鎖の再編成
行連鎖の解消方法は、該当のテーブルの存在する表領域レベルの再編成を実施します。表領域レベルの再編成については第2章を参照してください。この際、表領域のブロックサイズを平均ないし最大レコードサイズより大きくしてください。ただし、BLOBに音声データを格納している場合などOracleのブロックサイズの最大値より大きなデータを格納している場合は行連鎖の発生は避けられません。この場合は行連鎖の発生回数を減らすことを目的に、お使いのOracleで指定できる一番大きなブロックサイズにして再編成を実施してください。


行移行の防止策
行移行の防止策は、PCTFREEの適切な調整です。詳細については第3部をご参照ください。固定長のデータ型を使えば行移行は発生しないだろうということで全ての列をCHAR型かつNOT NULLで作成しているシステムをたまに見ますが、ここまでやってしまうと行移行が発生しなくなるメリット以上にデータのI/O量が増えてしまうデメリットの方が遥かに大きいので、そのような設計はやらないようにしましょう。


行連鎖の防止策
行連鎖の防止策は、テーブルの見積をきちんとやって最大レコード長や平均レコード長を算出し、それに見合うブロックサイズを選択することです。Oracle9i以降は表領域ごとにブロックサイズを選べますので、レコードサイズが大き目のテーブルを同じ表領域にまとめるのも手です。


◆ 第5章 まとめ

本稿では断片化の解消方法について解説してきました。途中何度か同じ事を書きましたが、断片化を防ぐ最良の方法は、事前に断片化しにくい物理設計を行うことです。半年に渡って本連載で解説してきたことを今後のデータベースの物理設計に総合的に取り入れると、断片化の減少のみならず、データベースのメンテナンス作業全般を減らすことが可能になり、より安定した運用ができるようになります。まずは本連載内容に従った物理設計を基礎として、あとは各システムの特性に応じて本連載の解説内容をカスタマイズしていってください。

 第5部 インデックスの設計