テーブルの中身がディスク上でどう並んでいるかを知らずにインデックス設計をしていた時期があり、後になってその怖さに気づきました。物理構造を押さえておくと、実行計画の読み方も変わってきます。

物理階層の全体像

データの物理構造図

図1: データベース物理構造（Database→Tablespace→Segment→Extent→Block）

RDBMSはディスクを以下のような階層で管理しています。この階層を理解することで、なぜディスクI/Oがボトルネックになるのか・どうすれば改善できるかの仕組みが見えてきます。

  • Block（ブロック）: I/Oの最小単位。4K/8K/16Kなど。1ブロックに複数行が入る。
  • Extent（エクステント）: 連続するブロックのまとまり。セグメントが大きくなる度に確保。
  • Segment（セグメント）: テーブル / インデックス / LOBごとの論理的なまとまり。
  • Tablespace（表領域）: 1つ以上のデータファイルから成る論理的な格納先。

ブロックサイズはデータベース作成時に決定し、後から変更できません。OLTPシステム（小さい行を大量に読み書き）では 8KB が標準的です。DWH・分析系（大量データをスキャン）では 32KB を選ぶこともあります。小さなブロックサイズは小さいデータの読み書きに効率的で、大きなブロックサイズは連続データの読み込みに効率的です。

ブロック → エクステント → セグメント → 表領域

用途別に表領域を分けるのが一般的です。表領域を分けることで、I/Oの負荷分散・バックアップ単位の制御・格納先ディスクの分離が可能になります。

表領域 用途 注意点
USERS（業務）業務データ・テーブルを格納I/Oが最も集中する。高速ストレージに配置が望ましい
INDEXインデックス専用テーブルとインデックスを別ディスクに分けると並列I/O効果あり
TEMP一時表領域（ソートなど）PGAで処理しきれない場合の退避先。大きいとI/O増大
UNDOロールバック・一貫性読み取り用長いトランザクションほど大量消費。UNDO_RETENTION設定が重要
SYSTEM辞書情報（システム管理用）業務データとは分離する。通常はデフォルトのまま
LOB大きなオブジェクト用通常のデータとは別に管理することで断片化を抑制

データファイル / REDOログ / 制御ファイル

データファイル

実データ・インデックスの実体。表領域 = 1つ以上のデータファイルの集合であり、更新は基本「バッファキャッシュ → DBWR → ファイル」の流れで行われます。大規模テーブルは複数データファイルに分散配置することでI/Oスループットを上げられます。

REDOログ

変更履歴を時系列で記録するファイルです。コミット時に LGWR（Log Writer）が必ずディスクに書き出します。このためコミット速度はREDOログの書き込み速度に直結します。障害時はREDOを再適用してリカバリを行います。最低でも3グループ以上・各グループを多重化（多重化数=2以上）するのがベストプラクティスです。

制御ファイル

DB全体の構成情報・状態（データファイルやREDOログのパス、SCN、バックアップ情報）を管理します。破損するとデータベースがマウント不可になるため、必ず多重化します。通常は異なるディスクに3コピー配置するのが推奨です。

アーカイブログ

満杯になったREDOログの退避コピーです。ARCHIVELOGモード時のみ生成されます。PITR（Point-In-Time Recovery：指定時点復旧）に必須のファイルで、本番環境では原則ARCHIVELOGモードで運用します。アーカイブログが蓄積しすぎてディスクが溢れると、データベースが停止するため、定期的なバックアップ＆削除の仕組みが必要です。

UNDO（ロールバック領域）

更新前のイメージを保管します。ROLLBACK時のデータ復元と、長いトランザクション中に他セッションが読み取る際の一貫性読み取りに使われます。トランザクションが長いほど大量のUNDOを消費します。UNDO_RETENTION パラメータで保持時間を設定しますが、長すぎるとUNDO表領域が枯渇するため適切なチューニングが必要です。

一時（TEMP）

PGAで処理しきれないソート・ハッシュ結合の中間データの退避先です。TEMP表領域への書き込みが多いと（= Disk Sort が多発している状態）、PGAのサイズ拡大や問題SQLのチューニングを検討します。

バッファキャッシュとI/Oの流れ

データの読み書きは、ディスクを直接操作するのではなく必ずバッファキャッシュを経由します。この仕組みがRDBMSのI/O効率の核心です。

  • SELECT は必要なブロックをファイルから読み、バッファキャッシュに乗せる（キャッシュヒットしなければ物理I/O）。
  • UPDATE はバッファキャッシュ上のブロックを書き換える + REDOログバッファに変更履歴を残す。
  • 実ファイルへの書き出しは DBWR が後でまとめて行う（= ユーザは待たない）。
  • コミット時には REDO が必ずディスクに書かれる ⇒ そこで初めて「永続化された」と言える。
バッファキャッシュ上の状態:
  • Dirty: 変更あり、未ディスク書き出し（DBWR待ち）
  • Clean: ディスクと同一（ヒット率に寄与）

バッファキャッシュが小さすぎると、同じブロックを何度もディスクから読み直す「Physical Reads」が多発しパフォーマンスが低下します。AWRレポートのBuffer Cache Hit Ratioが95%を下回る場合はキャッシュサイズの拡大を検討します。現在の物理構造の状況は次のクエリで確認できます。

-- 表領域ごとのサイズと空き容量を確認
SELECT tablespace_name,
       ROUND(SUM(bytes) / 1024 / 1024, 1) AS total_mb,
       ROUND(SUM(DECODE(autoextensible,'YES',maxbytes,bytes)) / 1024 / 1024, 1) AS max_mb
FROM   dba_data_files
GROUP  BY tablespace_name
ORDER  BY total_mb DESC;

物理構造設計でよくある誤りと判断基準

誤り・判断ミス何が起きるか正しい設計
全テーブルをUSERS表領域に格納する 業務データ・インデックス・LOBが同一ディスクに集中しI/O競合が発生する 用途別に表領域を分け、可能な範囲でディスクを物理分離する
REDOログをデータファイルと同一ディスクに置く COMMIT時のLGWRとDBWRがディスクI/Oで競合し、コミット遅延が発生する REDOログは専用の高速SSD・シーケンシャル書き込み専用ディスクに配置する
ブロックサイズをOLTPシステムで32KBに設定する 小さい行を1件読むだけでも32KBをキャッシュに乗せるためBuffer Cacheを圧迫する OLTPは8KB、DWH・大量スキャンは16〜32KBとワークロードに合わせて選択する
AUTOEXTEND=YESで上限なし設定のまま運用する ディスクが満杯になるまで拡張が続き、気づいた時にはサービス停止になる MAXSIZE を設定し、表領域使用率を監視ジョブで定期チェックする