Zenn
📑

ORACLE MASTER Silver DBA 取得に必要な知識のまとめ～データ格納のための記憶域編～

2024/06/13に公開
Oracle
tech

はじめに

本記事はORACLE MASTER Silver DBA取得に必要な知識の個人的まとめになります。

ORACLE MASTERとは

Oracle Databaseの管理スキルを証明するOracle社の資格です。

Bronze, Silver, Gold, Platinumの4ランクあります。

行の格納方法とブロックの空き領域管理

ある表に格納された行のデータは表に割り当てられたデータブロック内に格納されます。

ブロックは管理用の領域であるヘッダー領域とデータ格納用の領域に分かれています。ヘッダー領域の中にはブロックヘッダー、表ディレクトリ、行ディレクトリ路言う領域があり、タイムスタンプやブロック内の行データの位置情報などの管理情報が格納されています。ヘッダー領域のサイズは可変です。

行のINSERT処理を実行すると、「ブロックのデータ格納用の領域における末端側」から順に行データが格納されます。通常、1つのブロックには複数の行が格納されます。

INSERT処理を実行してブロックに格納された行データに対して、UPDATEやDELETEなどの変更処理を実行できます。なお、以下の操作を実行すると、行データと行データの間に空き領域が生まれます。

  • 既存の行データを小さいサイズに変更するUPDATE
  • DELETEによる行の削除

表の行データは表のセグメントを呼応生するいずれかのブロックに格納されます。このため、新しい行データを表に追加する場合を考えると、「行データを格納するのに十分な空き領域があるブロックはどれか？」がポイントになります。また、行データを更新してサイズが増える場合を考えると、「行データの増加に備えて、どれだけブロック内の空き領域を残しておくか？」がポイントになります。

セグメント領域管理方式

新しい行データを表に追加する場合、行データを格納するために十分な空き領域があるブロックを高速に探し出せる必要があります。このための仕組みがセグメント領域管理方式です。

セグメント領域管理方式には以下の2種類が存在します。

セグメント領域管理方式 説明
自動セグメント領域管理方式 ・空き領域があるブロックをツリー形式の管理構造で管理
・表レベルの設定項目がPCTFREEのみ
手動セグメント領域管理方式（フリーリスト管理方式） ・空き領域があるブロックをリスト形式の管理構造で管理
・表レベルの設定項目がPCTFREE、PCTUSED、FREELISTS、FREELIST GROUPSの複数存在

自動セグメント領域管理方式

すべてのブロックについて空き状態を4つのレベルに分類し、ツリー形式の管理構造で管理します。そのセグメントのブロックのいくつかを管理用ブロックとして使用し、それらに管理構造の情報を格納します。

手動セグメント領域管理方式に比べ、複数の処理を並行して実行した場合の性能に優れます。また、多くの処理が自動化されているため、構成は容易です。

手動セグメント領域管理方式

空き領域があるブロックをリスト形式の管理構造で管理します。このため、フリーリスト管理方式とも呼ばれます。

表に対してPCTFREE、PCTUSED、FREELISTS、FREELIST GROUPSなどのパラメータを設定し、ブロックの空き領域の有無の判断およびリスト形式の管理構造の工数などを調整する必要があり、一般に構成が困難です。

セグメント領域管理方式の構成手順

セグメント領域管理方式を構成するには以下の2つの作業を行います。

  1. 使用するセグメント領域管理方式を指定して表領域を作成
  2. ブロック空き領域管理関連の物理属性パラメータを指定して表を作成

使用するセグメント領域管理方式は表領域単位で設定し、表領域作成時に SEGMENT SPACE MANAGEMENT で指定します。自動セグメント領域管理方式の場合 AUTO、手動セグメント領域管理方式の場合 MANUAL を指定します。表領域作成後にセグメント領域管理方式を変更することはできません。なお、 CREATE TABLESPACE にセグメント領域管理方式を指定しない場合は自動セグメント領域管理方式が使用されます。

PCTFREEパラメータと行データの追加可否

1つのブロックには複数の行データを追加できますが、ブロックに目いっぱい行データを詰め込みません。PCTFREEパラメータに設定した割合だけ空き領域を残すように動作します。これは行が更新された結果、行データのサイズが増える場合に備えるためです。

再度新規行が追加可能になる条件

行の削除や、行のデータサイズを縮小するような更新により、ブロックの空き領域が増えた場合は、表データを追加できるようになります。

再度、新規行が追加できるようになる具体的な条件は、表を格納する表領域のセグメント領域管理方式により異なります。

セグメント領域管理方式 新規行を追加できる条件
自動セグメント領域管理方式 Oracle Databaseが鈍アルゴリズムに従い、自動的に判断。PCTUSEDは使用されない
手動セグメント領域管理方式 ブロックの使用率がPCTUSEDを下回った場合

行移行と行連鎖

通常、1つの行のデータはある特定のブロックに格納されます。しかし、例外的な状況では1つの行のデータが複数のブロックにまたがって格納されることがあります。

状況 説明
行連鎖 1ブロックに収まりきらないサイズの行データが複数のブロックに分割して格納された状況
行移行 拡張された行データが別のブロックに格納された状況

行連鎖とは、1つの行のデータサイズがブロックのデータ格納用領域よりも大きい場合に、行データを複数の行断片に分割し、それぞれの行断片を別々のブロックに格納する形態です。

行連鎖につながる行のデータを読み出すためには、その行の行断片が格納されているすべてのブロックを読み出す必要があるため、ストレージI/O回数が増えます。ただし、行連鎖の発生している行が1%以下である場合は影響は軽微です。

行連鎖の原因はブロックサイズに比べて1つの行のデータサイズが大きいことですので、発生を抑止するには以下の対処が考えられます。

  • ブロックサイズを拡張
  • 行のデータサイズを小さくする

行移行とは、行のサイズを拡張するようなUPDATEを実行したとき、拡張したデータがブロックの空き領域に収まりきらなかった場合の行データの格納の形態です。

その行のデータは別のブロックにコピーされ、元のブロックには行のポインタのみが格納されます。

行移行につながる行のデータを読み出すためには、行のポインタが格納されているブロック、行のデータが格納されているブロックの2つを読み出す必要があるため、ストレージI/O回数が増えます。ただし、行移行の発生している行が1%以下である場合は影響は軽微です。

行移行の原因は行データサイズの増加がブロックの増加がブロックの空き領域よりも大きいことですので、発生を抑止するには以下の対処が考えられます。

  • PCTFREEを大きく設定し、ブロックの空き領域をあらかじめ大きくしておく
  • 可能であれば、行でデータサイズが増加しないような工夫を行う
    • 例えば、ある列の値が更新前：NULL、更新後：サイズ大のデータであれば、更新後の列値はNULL以外のサイズ大のデータとしておく

セグメントの管理

表や索引などのデータを保持するオブジェクトにはセグメントと呼ばれる記憶域が1対1で対応します。

なお、すべてのオブジェクトにセグメントが対応するわけではありません。例えば、ビューはデータを保持しないオブジェクトであるため、ビューに対応するセグメントはありません。

オブジェクト 説明 セグメントを持つか
表 行データを格納するオブジェクト 持つ
索引 表へのアクセスを高速化するためのオブジェクト 持つ
パーティション表 大量のデータを扱いやすくするために表を複数に分割したオブジェクト。分割により、1つのオブジェクトに対して複数のセグメントが対応する 持つ
クラスタ 一緒に参照される複数の表をグループ化したオブジェクト 持つ
UNDOセグメント 過去データをUNDO表領域に格納するセグメント（厳密にはオブジェクトではない） 持つ
一時セグメント 一時的なデータを一時表領域に保管するセグメント（厳密にはオブジェクトではない） 持つ
ビュー データを持たない仮想的な表 持たない
シノニム オブジェクトの別名 持たない
シーケンス（順序） 連番を振り出すオブジェクト 持たない
ストアドプログラム プログラムに名前を付けてデータベースに保存し、繰り返し実行できるようにしたもの（パッケージ、プロシージャ、ファンクション、トリガーなど） 持たない

セグメントの格納先として表領域を指定されます。

遅延セグメント作成

DEFERRED_SEGMENT_CREATION初期化パラメータをTRUEに設定すると、セグメントを持つオブジェクトを作成しても、オブジェクトにデータが格納されるまでセグメントは作成されません。これを遅延さぐメントといいます。

遅延セグメント作成機能を使用すると以下の利点を得られます。

  • データが格納されない可能性がある表が大量に存在する場合に領域消費を大幅に節約できる
  • 大量の表の作成が必要なアプリケーションの導入において、導入時間を大幅に短縮できる

以下のいずれかの方法で遅延セグメント作成機能を使用できます。

  • DEFERRED_SEGMENT_CREATIONをTRUEに設定してからセグメントを作成する
  • CREATE TABLE に SEGMENT CREATION DEFERRED を指定する

遅延セグメント作成機能の留意点は以下の通りです。

  • デフォルトでDEFERRED_SEGMENT_CREATIONはTRUEです
  • DEFERRED_SEGMENT_CREATIONは動的初期化パラメータであり、かつ、セッションレベルで設定可能です
  • 遅延セグメント作成機能が有効か無効かに応じて、表領域のクォータが割り当てられていないときの動作が異なります
    • 遅延セグメント作成機能が有効の場合、表の作成は可能ですが、表に行をINSERTすると、クォータが割り当てられていないことに起因するエラーが発生します
    • 遅延セグメント作成機能が無効の場合、表の作成時にエラーが発生します
  • ローカル管理方式の表領域で使用可能です、ディクショナリ管理方式の表領域では使用できません
  • SYSユーザーおよびSYSTEMユーザーが所有するセグメント、SYSTEM表領域に格納されるセグメントは遅延セグメント作成機能が無効です

セグメントの拡張

大量のデータを追加する処理などを実行して、セグメントの空き領域が不足すると、セグメントに自動的にエクステントが追加され、セグメントのサイズを拡張します。

セグメントが格納されている表領域に空き領域がある場合、その空き領域からエクステントを割り当てることができます。

しかし、表領域に空き領域がない場合は表領域を構成するデータファイルのサイズを拡張し、表領域に空き領域を確保し、そこからエクステントを割り当てる必要があります。

データファイルの自動拡張設定がOFFになっているなどの理由で、セグメントの拡張に失敗した場合はORA-01653、ORA-01654などのセグメント拡張エラーが発生し、大量のデータ追加などのセグメント拡張を必要とする処理が強制終了されます。

再開可能領域割り当て

セグメント拡張エラーが発生すると、処理が強制終了されてデータベースは処理を実行する前の状態に戻ります。この場合、データファイルを拡張アウルなどして、セグメント拡張エラーの原因を取り除き、処理を最初から再度実行する必要があります。時間がかかる処理の場合、これはとても非効率です。再開可能領域割り当て機能を使うと、この問題を解消できます。

再開可能領域割り当て機能は、セグメント拡張を正常に実行できない状況が発生した場合、エラーを発生させてセグメント拡張を必要とする処理を強制終了するのではなく、処理を一時停止し、RESUMABLE_TIMEOUT初期化パラメータに設定した秒数だけエラー発生及び処理の強制終了を待つ機能です。

処理の一時停止中にデータベース管理者がデータファイルを拡張するなどして、セグメント拡張失敗の原因が解消すると、一時停止した処理は途中から再開されます。

再開可能領域割り当て機能を使用しない場合に比べると、処理を最初から再実行する必要がないため効率的です。

なお、RESUMABLE_TIMEOUT秒だけ一時停止しても原因が解消されないと、再開可能領域割り当て機能を使用しない場合と同様にセグメント拡張エラーを発生し、処理が強制的に終了されます。

再開可能文

再開可能領域割り当て機能を使用できる操作及びSQL文を再開可能文と呼びます。再開機能文には以下の者があります。

  • 一時表領域を使うSELECT文
  • DML
  • ロード系処理：Data Pump Importによるデータのインポート、SQL *Loaderを用いたデータロード
  • DDL： CREATE TABLE AS SELECT など

再開可能領域割り当てが動作する状況

  • 表領域の空き領域不足により追加エクステントの割り当てに失敗した場合
  • ユーザーのクォータを超過した場合
  • セグメントの最大エクステント数に達した場合（ディクショナリ管理表領域のみ）

再開可能領域割り当てを使用する手順

  1. RESUMABLE_TIMEOUT初期化パラメータに再開可能文を一時停止する時間（再開可能領域割り当てのタイムアウト値）を設定
    • ALTER SESSION ENABLE RESUMABLE の TIMEOUT で一時停止時間を設定する場合はこの設定は不要
  2. ALTER SESSION ENABLE RESUMABLE を実行して、現行のセッションで再開可能領域割り当てを有効化
    • ALTER SESSION ENABLE RESUMABLE の実行、RESUMABLE_TIMEOUT初期化パラメータの設定には、RESUMABLEシステム権限が必要
  3. 再開可能文を実行

再開可能文が一時停止したときに実行される処理

  • エラーがアラートログに記録される
  • ユーザーがALTER SUSPENDイベントに対してトリガーを登録していた場合はそのトリガーが実行される

セグメントの縮小

セグメントに格納したデータが削除された場合はセグメントを縮小できます。

HWM

各セグメントには、そのセグメントのどこまでデータが格納されたことがあるかを示すHWM(High Water Mark)と呼ばれるマークがあります。

HWMによって、HWMよりも後ろのブロックにはデータが存在しないこと、すべてのデータはセグメントの先頭ブロックからHWMの間に格納されていることが保証されます。
このため、表フルスキャンのように全行分のデータを読み出す必要がある場合でもデータベースはセグメントの先頭ブロックからHWMまでブロックをアクセスすればよく、すべてのブロックを読み出す場合と比べて、処理を効率化できます。

ただし、データを大量に削除すると、HWMはデータの格納状態を正しく反映した位置になりません。データ追加時にHWMは末尾に自動的に移動しますが、データ削除時に戦闘側に自動的に移動しないためです。
また、セグメントの格納効率も悪くなります。

表セグメントのオンライン縮小

ALTER TABLE SHRINK SPACE を実行すると、表セグメントを縮小して格納効率を高め、HWMを正しい位置に変更できます。

縮小処理中、通常のDMLやSELECTを同時に実行できます。また、 CASCADE を指定すると表に設定した索引も併せて縮小できます。

ALTER TABLE SHRINK SPACE のオプションは以下の通りです。

オプション指定 説明
なし セグメントの縮小後、HWMを引き下げてHWM以降の領域（エクステント）を開放する
COMPACT あり セグメント内のデータの再編成のみを行う（HWMの引き下げ、領域の開放は行わない）
CASCADE あり 表に作成されている索引に対しても縮小処理を行う

オンラインセグメント縮小を実行するには、以下の条件を満たす必要があります。

  • 対象の表について、行移動が有効化されている(ENABLE ROW MOVEMENT)
    • 行移動は表に格納された行データの移動を許す設定で、デフォルトで無効化されています
  • 対象の表が、自動セグメント領域管理方式の表領域に格納されている

また、 ALTER INDEX SHRINK SPACE で索引セグメントを縮小できます。

TRUNCATE TABLEとエクステントの開放

TRUNCATE TABLE を実行すると、表のすべての行を削除できます。

この時、表の作成後に追加で割り当てられたエクステントを開放する動作を変更できます。

オプション 解放するエクステント
DROP STORAGE(デフォルト) 表の作成後に追加で割り当てられたエクステント
DROP ALL STORAGE すべてのエクステント
REUSE STORAGE エクステントを開放しない

特殊なセグメント

データの永続的な保管を目的とするセグメントは、永続表領域に格納されます。

永続セグメントはユーザーがデータを保持するオブジェクトを作成すると併せて作成されます。表や索引に対応するセグメントは永続セグメントです。

永続セグメント以外のセグメントとしてUNDOセグメントと一時セグメントがあります。これらのセグメントはデータベースによって管理され、処理状況に応じて自動的に作成および削除されます。

永続セグメントとは扱いが大きく異なります。

分類 説明 格納される表領域
永続セグメント データを保持するオブジェクトを作成するのと併せて作成される 永続表領域
UNDOセグメント 変更処理を実行したとき、過去データを保管するために使用される UNDO表領域
一時セグメント 大量のデータをソートする場合など、メモリー内でデータ処理が実行できない場合に一時的にデータを保管する

上記の表の例外は以下の通りです。

  • 一時UNDO機能を使用すると、UNDOセグメントが一時表領域に作成されます
  • 表や索引などの永続セグメントの作成実行中、一時セグメントが永続表領域に作成されます

表の圧縮

Oracle Databaseでは表を圧縮し、使用するストレージ領域を削減すること、使用するデータベースバッファキャッシュのメモリーサイズを削減することが可能です。

圧縮方法は以下の通りです。

圧縮方法 圧縮可能な操作 表レベルの圧縮設定のための句 向いている用途
基本表圧縮 ・ダイレクトパスロード操作
・ALTER TABLE ... MOVE
・表のオンライン再定義		 ・ROW STORE COMPRESS BASIC
・ROW STORE COMPRESS
・ROW COMPRESS 		データウェアハウス、意思決定支援システムなどのデータが一括で投入されるタイプのシステムでの使用
高度な行圧縮 ・すべての操作 ・ROW STORE COMPRESS ADVANCED オンライントランザクション処理システムを含めた、あらゆるシステムの使用（有償オプションのAdvanced Compression Optionが必要）

ALTER INDEX UNUSABLE

一時的に使用しない索引をUNUSABLE(使用不可)にすると、索引セグメントを削除し、領域を解放できます。

なお、索引セグメントは削除されますが、データディクショナリに索引の定義は残ります。

ALTER INDEX 索引名 UNUSABLE;

索引の定義は残っているため、索引の再構築を行うと再び索引を使用できます。

ALTER INDEX 索引名 REBUILD;

おわり

データ格納のための記憶域編はここまで。

次はUNDOの管理について説明していきます。

Discussion