Oracle の領域管理 - OTN

THE
Database
FOR Internet Computing
Oracle の領域管理
∼トラブル防止のテクニック∼
日本オラクル株式会社
目次
はじめに................................................................................................................................................ 3
1.表と索引の管理................................................................................................................................. 4
1.1.TABLESPACE の設定..................................................................................................................... 4
1.2.PCTFREE の設定 .......................................................................................................................... 5
1.3.STORAGE 句の設定....................................................................................................................... 5
1.4.DB_BLOCK_SIZE ついて ............................................................................................................... 7
1.5.自動作成される索引に関する注意............................................................................................ 8
1.6.パラメータの設定例.................................................................................................................. 9
表と索引の管理のまとめ................................................................................................................ 12
2.表領域の管理................................................................................................................................... 13
2.1.表領域の拡張法について......................................................................................................... 13
2.2.TABLESPACE COALESCE について ..................................................................................................... 16
2.3.DEALLOCATE UNUSED SPACE について............................................................................................... 17
2.4.MINIMUM EXTENT パラメータについて...................................................................................... 18
2.5.ローカル管理表領域について.................................................................................................. 20
表領域の管理のまとめ.................................................................................................................... 22
3.ロールバック・セグメントの管理.................................................................................................. 23
3.1.サイズを考える ....................................................................................................................... 23
3.2.個数を考える........................................................................................................................... 24
3.3.鉄則.......................................................................................................................................... 25
3.4.設定例１ .................................................................................................................................. 25
3.5.設定例２(OPTIMAL を使う) ...................................................................................................... 26
3.6.初期データベースをカスタマイズする................................................................................... 28
3.7.UNLIMITED EXTENTS について................................................................................................... 31
3.8.動的縮小方法（SHRINK）......................................................................................................... 31
3.9.V$ROLLSTAT を使用したサイズの見積もり方法....................................................................... 31
ロールバック･セグメントの管理のまとめ..................................................................................... 33
4.テンポラリー・セグメントの管理.................................................................................................. 34
4.1.いつ使用されるのか................................................................................................................ 34
4.2.使用する一時表領域の指定..................................................................................................... 34
4.3.専用一時表領域 ....................................................................................................................... 35
4.4.デフォルト記憶領域パラメータ.............................................................................................. 36
4.5.テンポラリファイルを使った専用一時表領域........................................................................ 37
4.6.初期データベースをカスタマイズする................................................................................... 39
4.7.使用の確認方法 ....................................................................................................................... 40
テンポラリー・セグメントの管理のまとめ................................................................................... 43
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
1
5.REDO ログ・ファイルの管理............................................................................................................ 44
5.1.サイズを考える ....................................................................................................................... 44
5.2.配置を考える........................................................................................................................... 45
5.3.初期データベースをカスタマイズする................................................................................... 46
5.4.サイズの見積もり方法............................................................................................................. 48
REDO ログ・ファイルの管理のまとめ............................................................................................. 49
おわりに.............................................................................................................................................. 50
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
2
はじめに
データベースのトラブルはどうして起こるのでしょうか。その原因はさまざまです。
例えば、領域に関するトラブルがあります。「領域を確保できない」というエラーはその代表的なも
のでしょう。しかし、この原因は単に「データ量の見積もりの甘さ」による「表領域のサイズ不足」で片
付けられないことが多いのです。実際のところ、これらのトラブルの多くは Oracle の領域管理の仕組み
を知っていれば未然に防げたはずです。また、データベースのパフォーマンス悪化が領域の設定に起因し
ていることもあります。
本書の目的は、領域の設定・管理に起因するトラブルを回避するポイントを解説することです。トラ
ブルが発生したあとで、どのように対処するのかではなく、トラブルをできるだけ未然に防ぐ方法を解説
します。
まずデータベースの領域について簡単にまとめてみましょう。
Oracle の世界（論理的）
表
でのセグメント
Oracle の世界（論理的）
索引
ロールバッ
ク・セグメント
テンポラリ・セ
グメント
表領域（TABLESPACE）群
ァイル群
でのファイル
OS 上（物理的）のファイ
REDO ログ・フ
データファイル群
ル
REDO ログ・フ
その他のフ
ァイル群
ァイル群
本書では上記図中の太枠で囲んだ領域の管理について、順を追って説明していきます。各領域に関して
起こり得るトラブルの原因とその対処法についても解説します。
「第３章ロールバックセグメントの管理」以降の章では、解説したポイントをもとに、初期データベー
スを実際にカスタマイズする方法についても説明します。
本書はデータベースの領域管理に関して「理論」より「実践」、「パフォーマンス」より「安定性（ト
ラブルレス）」、また「詳しい構造･仕組み」より「明解さ」に重点をおいて解説してあります。本書で
詳しく触れない点についてお知りになりたい方は、製品マニュアルや本文中で紹介している技術資料を参
考にすることをお勧めします。
なお本書は、第一版のわかりづらい表現や誤りを修正し、Oracle8i R8.1.5 の新機能であるローカル管理表
領域やテンポラリファイルを使った一時表領域などの記述を追加したものです。
本文中では Oracle8 Enterprise Edition R8.0.5 for Windows NT および Oracle8 Enterprise Edition R8.0.5 for Solaris を使用
しています。Oracle8 をベースとしていますが、ほとんどの内容は Oracle7 や Oracle8i でも有効です。
ø Oracle、Oracle7、Oracle8 はオラクル社の登録商標です。
ø その他のすべての企業名と商品名は各社の登録商標または商標、製品名です。
ø なお、本文中では 、 は明記していません。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
3
1. 表と索引の管理
CREATE ￨ TABLE ￨ オブジェクト名
￨ INDEX ￨
TABLESPACE テーブルスペース名.........................1.1. TABLESPACE の設定
PCTFREE xx PCTUSED xx.................................1.2. PCTFREE の設定
INITRANS xx
STORAGE(..............................................1.3. STORAGE 句の設定
INITIAL xx NEXT xx
MINEXTENTS xx MAXEXTENTS xx
PCTINCREASE xx )
この章では、上記の例文中の項目について説明していきます。さらに、構文中には出てきませんが、
表と索引の領域管理に関係の深い、次の 2 つの項目についても説明します。
DB_BLOCK_SIZE の設定
自動作成される索引に関する注意
1.1. TABLESPACE の設定
Oracle では、TABLESPACE 句によって、オブジェクトの格納先を明示的に指定することができます。
TABLESPACE 句を記述しないと、オブジェクトはユーザーのデフォルト表領域に格納されます。格納する
表領域を指定することによって、ディスク I/O のバランスを図ったり、オンラインバックアップの単位を
分割したりできます。
なおパーティション表を除き、
オブジェクト作成後に ALTER 文で格納先の表領域を変更することはでき
ません。オブジェクトを再作成するか、表であれば CREATE TABLE AS SELECT を使います。
オブジェクトごとに表領域を分けるおもな理由は次の２つです。
・ディスク I/O のバランスを図る。
・オンラインバックアップの単位を区別する。
上記の理由から、表領域の分割基準を次のように決定します。
１．表と索引の表領域を分ける。
２．１つの DB を複数のアプリケーションで使用する場合には、アプリケーションごとに表領域を分け
る。
ここまでが基本です。それ以外にも性質の異なる表ごとに表領域を分割することもあります。たとえ
ばデータ量が安定しているマスター系の表と、データが累積されていく伝票などの表です。
4 種類のセグメント（表、索引、ロールバック、テンポラリ）全般に対しても表領域の分割を考慮しな
ければいけません。各セグメントの章の中でも触れていますので、確認してください。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
4
1.2. PCTFREE の設定
PCTFREE は 1 ブロック内にあらかじめ確保される空領域の割合（％）です。この確保した領域は、行サ
イズが大きくなるような更新処理のために使用されます。PCTFREE のデフォルト値は 10 ですが、処理の
種類によっては変更が必要です。
頻繁に更新されるセグメントでは行移行を避けるため、PCTFREE は大きめに設定する。
Oracle では、行サイズが大きくなるような更新を行った場合、ブロック内に空きがないと、別のブ
ロックへその行データを移動し、元のブロックにはそのアドレスを残します。これを行移行と呼び、更
新や検索の性能劣化の原因となります。PCTFREE であらかじめブロック内に空き領域を確保しておくこ
とにより、行移行を回避することができます。VARCHAR2 や NUMBER などの可変長データ型を使っている
ときや、CHAR のような固定長データ型であっても、NULL 値の列にデータをセットするときには、行サ
イズが大きくなる可能性があります。
更新の全くない検索専用表のPCTFREE は小さく設定する。
PCTFREE で確保した領域は更新のときにしか使われないことに注意してください。検索専用の表や行
サイズが大きくならない表では PCTFREE で領域を確保するメリットはありません。そのようは表や索引
では PCTFREE を小さくします。データの格納効率がよくなり、読みとりブロック数を減少できるので、
検索パフォーマンスが向上します。
索引の PCTFREE は小さくてよい場合が多い。
主キーに付けられた索引は VARCHAR2 や NUMBER などの可変長データ型で定義していても、実際には固
定長である場合が多いので PCTFREE は小さくてよいでしょう。
PCTFREE + PCTUSED ＜ 100 に設定する。
PCTFREE に対して、PCTUSED はブロック内で「使う」ことのできる割合の最小値（％）です。ブロッ
クが PCTFREE で指定した限界値まで満杯になると、そのブロックにおける割合が PCTUSED を下回るまで、
Oracle はそのブロックに新しい行を挿入しません。PCTUSED は、PCTFREE との和が 100 を下回るように
設定してください。
1.3. STORAGE 句の設定
Oracle ではオブジェクト作成時に STORAGE 句で以下の記憶領域パラメータを指定することができます。
・INITIAL 第一番目のエクステントの大きさ
・NEXT 次に割り当てられるエクステントの大きさ
・MINEXTENTS
オブジェクト作成時のエクステント数
・MAXEXTENTS
最大エクステント数
・PCTINCREASE エクステント・サイズの増加率(%)
これらの設定次第では運用途中に重大な障害が発生する可能性があります。
これらのパラメータの詳細な意味は本書では解説しません。ここではこれらのパラメータを設定する
指針を解説します。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
5
これらの STORAGE 句のパラメータの値は次の優先順位で利用されます。
1.オブジェクト作成時に指定した値
2.表領域の DEFAULT STORAGE 句の値
3.システムのデフォルト値
すべてのデータを INITIAL に納めるべきか？
これはよく議論のされるところです。しかしさまざまな考慮点があり、一概にこれがよいということ
はできません。考慮点を紹介します。
・インデックススキャンではブロック単位で I/O が行われるため、
エクステントのフラグメンテーショ
ンはそれほどパフォーマンスには影響しません。
・フルテーブルスキャンでは DB_FILE_MULTIBLOCK_READ_COUNT×DB_BLOCK_SIZE の単位で I/O が行われ
ます。そして Oracle は一回の I/O で複数のエクステントを処理できません。したがって、このサイ
ズ以下のエクステントが多いと必要以上に I/O 回数が増加する可能性があります。
・ハードウェアレベルで見た場合、連続した領域にデータが格納されていたほうが、ハードディスク
のシーク時間を減らすことができます。
・データの増加量に対して INITIAL と NEXT が小さいと、頻繁にエクステントの確保が必要になり、
INSERT 時のパフォーマンスが低下する可能性があります。
・INITIAL で確保した領域は DEALLOCATE UNUSED SPACE コマンドを使用しても後で解放することができ
ません。したがって大規模な表で見積を誤ると、非常に多くの領域が無駄になる可能性があります。
・１つのエクステントは複数のデータファイルをまたがることができません。
・複数のディスク装置が付いたシステムでは、１つの表のエクステントをそれぞれのディスクに分散
することによりパフォーマンスが向上します。
以上のことから、数メガバイト程度の小さい表は１つのエクステントにまとめたほうが良く、大規模
な表では小さすぎるエクステントは良くないものの、エクステント数にそれほど敏感になる必要はない
といえます。
エクステント最大数について
Oracle7 R7.2 まではデータベースのブロックサイズによってエクステント数の最大値が制限されて
いました。したがって行数の多い表に小さな INITIAL、NEXT を指定した場合、最大エクステント数に達
してしまい、途中でエラーが発生します。次の表はデータベースのブロックサイズと最大エクステント
数の関係を表したものです。
ブロックサイズ
最大エクステント数
2K
121
4K
249
8K
505
MAXEXTENTS を指定しないときには、この値がデフォルトになります。
Oracle7 R7.3 からは UNLIMITED と指定することにより、最大エクステント数は 2,147,483,645 とな
りました。事実上、エクステントの割り当て制限がなくなったといえます。しかし、次々にエクステン
トを割り当てるのは、パフォーマンスへの悪影響を与えるので望ましくありません。データ量を計算し
ている場合には、適切な INITIAL、NEXT、MINEXTENTS を設定して、不必要なエクステントの数の増加は
抑えたほうが良いでしょう。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
6
PCTINCREASE は、当初予想し得ないほどデータが増加するときに有効なパラメータです。しかし
MAXEXTENTS が UNLIMITED に指定できる現在では、PCTFREE を大きく設定してデータ増加に備える必要は
ないでしょう。表領域のフラグメンテーション（断片化）を避けるためにも通常はゼロに設定してくだ
さい。ただし、表領域の PCTINCREASE は 1 に設定することをお勧めします。PCTINCREASE を 0 より大き
い値にすると、定期的に SMON プロセスが DROP、TRUNCATE で開放された隣接する細切れの空エクステン
トを自動的に合体するからです。
1.4. DB_BLOCK_SIZE ついて
DB ブロックサイズとは、Oracle の管理する最小記憶領域単位です。DB ブロックサイズの設定次第では、
領域の利用効率が悪化したり、行連鎖によってパフォーマンスが低下したりする可能性があります。また
DB ブロックサイズはデータベース作成時に決定し、途中での変更はできません。運用開始後に変更する
ためには、データベースを再作成する必要があります。
したがって DB 設計段階で慎重に検討し、早期に決定する必要があります。DB ブロックサイズ決定のポ
イントは次の２点です。
・処理の種類(OLTP or DSS)
・表の行サイズ
処理の種類(OLTP or DSS)について
Oracle ではブロックが最小の I/O 単位です。したがって小さいデータの読み書きが主体の OLTP シス
テムでは、ブロックはあまり大きくない方がよく、逆に大量のデータを読む意志決定支援やデータウェ
アハウスのシステムでは、大きいブロックの方が望ましいといえます。次の表は一般的なガイドライン
です。
システムの種類
DB_LOCK_SIZE
オンライン・トランザクション処理（OLTP）
2∼4KB
意思決定支援（DSS）
4∼16KB
表の行サイズについて
表の行サイズも、領域の利用効率やパフォーマンスの面で重要です。たとえばブロックサイズが 2K
バイトのデータベースに平均行サイズが 700 バイトの表を格納すると、１ブロックあたり２行分のデー
タしか格納できません。単純計算で 2048-(700*2)=648 バイトは無駄な領域になってしまいます。
また、非常に長い行データを扱うときは注意が必要です。行サイズがブロックサイズをを超えてしま
うようなとき、Oracle はその行データを複数の断片に分割して、別々のブロックに格納します。これ
は行連鎖(注意参照)とよばれ、行移行と同様に、更新や検索の性能劣化の原因となります。したがって
行サイズが大きいシステムでは、大きいブロックのほうが望ましいと言えます。
この２つのポイントを念頭に置いて、DB ブロックサイズを決定してください。
(注意) 連鎖行の調査方法
移行された行と連鎖行をすべて表示する方法を説明します。
1. 連鎖行の調査をしたいユーザーで接続し、連鎖行情報を格納するために UTLCHAIN.SQL スクリプトを使
用して CHAINED_ROWS という表を作成します。ここでは SCOTT で接続します。
SQL> CONNECT SCOTT/TIGER
接続されました。
SQL> @C:¥ORANT¥RDBMS80¥ADMIN¥UTLCHAIN
表が作成されました。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
7
2. 調査する表に以下のように LIST CHAINED ROWS オプションを付けて、
ANALYZE コマンドを発行します。
連鎖行情報が CHAINED_ROWS 表に格納されます。ここではユーザーSCOTT の CHAIN_TEST 表を調査しま
す。
SQL> ANALYZE TABLE CHAIN_TEST LIST CHAINED ROWS;
表が分析されました。
3. 次の問い合わせ SQL 文を発行し連鎖行を表示します。
SQL> SELECT * FROM CHAINED_ROWS
2
WHERE TABLE_NAME = 'CHAIN_TEST';
OWNER_NAME
TABLE_NAME
CLUSTER_NAME
------------------------------ ------------------------------ -----------------------------PARTITION_NAME
HEAD_ROWID
ANALYZE_
------------------------------ ------------------ -------SCOTT
CHAIN_TEST
AAAAn+AACAAABmkAAA 99-02-01
SCOTT
CHAIN_TEST
AAAAn+AACAAABmmAAA 99-02-01
ここでは移行された行または連鎖行が２行存在していることがわかります。
1.5. 自動作成される索引に関する注意
表に主キー制約やユニーク制約をつけると、Oracle によって暗黙的に索引が作成されます。では、次
のような SQL を実行すると、暗黙的に作成される索引はどのようになるでしょうか。ユーザーのデフォル
ト表領域は USER_DATA とします。
1.表作成時に主キー制約をつけるとき
CREATE TABLE TAB1
(COL1 CHAR(10) PRIMARY KEY,
COL2 VARCHAR2(500))
TABLESPACE TS1
STORAGE(INITIAL 1M NEXT 100K PCTINCREASE 0);
2.後で主キーを制約をつけるとき
CREATE TABLE TAB1
(COL1 CHAR(10), COL2 VARCHAR2(500))
TABLESPACE TS1
STORAGE(INITIAL 1M NEXT 100K PCTINCREASE 0);
ALTER TABLE TAB1
ADD CONSTRAINT PK_TAB1 PRIMARY KEY(COL1);
1 の場合、表に指定したパラメータが使用されます。
作成された表領域
：TS1
STORAGE パラメータ
：INITIAL 1M NEXT 100K PCTINCREASE 0
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
8
2 の場合、ユーザーのデフォルト表領域に作成され、STORAGE 句はその表領域の DEFAULT STORAGE の値
が使用されます。
作成された表領域
：USER_DATA
STORAGE パラメータ
：INITIAL 10K NEXT 10K PCTINCREASE 50
例えば、索引サイズが 400KB になるとします。1 の場合、必要以上に領域（1MB）を確保してしまいま
す。2 の場合は、INITIAL で確保する領域（10KB）が小さすぎます。これを避けるには、制約を定義する
CONSTRANT 句の中で USING INDEX 句を使ってその索引を格納する表領域と STORAGE パラメータを指定しま
す。1 の場合、以下のように変更します。
CREATE TABLE TAB1
(COL1 CHAR(10) PRIMARY KEY
USING INDEX
PCTFREE 20
TABLESPACE INDEX_DATA
STORAGE (INITIAL 400K NEXT 40K PCTINCREASE 0),
COL2 VARCHAR2(500)
)
TABLESPACE TS1
STORAGE(INITIAL 1M NEXT 100K PCTINCREASE 0);
2 の場合、以下のようにして変更します。
CREATE TABLE TAB1
(COL1 CHAR(10), COL2 VARCHAR2(500))
TABLESPACE TS1
STORAGE(INITIAL 1M NEXT 100K PCTINCREASE 0);
ALTER TABLE TAB1
ADD CONSTRAINT PK_TAB1 PRIMARY KEY(COL1)
USING INDEX
PCTFREE 20
TABLESPACE INDEX_DATA
STORAGE (INITIAL 400K NEXT 40K PCTINCREASE 0);
1、2 の場合ともに、作成された索引は、次の設定になります。
作成された表領域
：INDEX_DATA
STORAGE パラメータ
：INITIAL 400K NEXT 40K PCTINCREASE 0
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
9
1.6. パラメータの設定例
ここでは性質の違う 4 つの表に対するパラメータの設定例を挙げます。ただし、ここで紹介する例は、
絶対的なものではありません。システムの要件やバックアップの形態によっては、さまざまなバリエーシ
ョンが考えられます。この例を参考に、各システムに合わせて変更を加えてください。
例 1. 検索の中心となるマスター表
表の初期データ量
：1MB
毎年のデータ増加量
：ほとんど増減無し
この場合、パラメータの設定は、次のようにします。
INITIAL
：1M
初期データが収まる値にする。
NEXT
：100K
ほとんど増減ないので小さくて構わない。
MAXEXTENTS
：デフォルト値
特に設定する必要なし。
PCTFREE
：5
マスター・データの更新はほとんどないので。
例 2. 伝票のような挿入と検索用の表
表の初期データ量
：0MB
月毎のデータ増加量
：10MB
データ保存期間
：3 年
この場合、パラメータの設定は、次のようにします。
INITIAL
：80M
半年分のデータ＋αが収まる値にする。
NEXT
：10M
月毎の増加量。
MAXEXTENTS
：UNLIMITED
予想できないデータ増加に備えて余裕をもたせる。
PCTFREE
：5
更新はほとんどないので。
例 3. 顧客表のような挿入と更新が繰り返される表
表の初期データ量
：100MB
月毎のデータ増加量
：10MB
データ・メンテナンス間隔
：1 年
この場合、パラメータの設定は、次のようにします。
INITIAL
：250M
次のデータ･メンテナンスまでのデータ＋αが収まる値にする。
NEXT
：25M
INITIAL の 10%くらい。
MAXEXTENTS
：UNLIMITED
予想できないデータ増加に備えて余裕をもたせる。
PCTFREE
：20
更新による可変長データの拡大と、NULL からデータ有に変化する列が
予想されるため大きめに設定する。
例 4. データウェアハウスに用いられるような巨大読取専用表
表の初期データ量
：1GB
毎年のデータ増加量
：ほとんどなし
この場合、パラメータの設定は、次のようにします。
INITIAL
：1G
初期データが収まるようにする。
NEXT
：1M
INITIAL に全てのデータが収まるはずなので小さくてよい。
MAXEXTENTS
：UNLIMITED
特に設定する必要なし。
PCTFREE
：0
更新は全くないので、読み込むブロック数を最小にする。
一般に、大きな表を扱うときは、正確にデータ量を見積もり、パラメーターの設定に反映させることが
大切ですが、データの少ない小さな表に対しては、デフォルトのままの設定でも問題はないでしょう。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
10
参考までに Oracle Enterprise Manager（OEM）による PCTREE と PCTUSED の設定は以下のようになりま
す。
更新アクティビティ
挿入アクティビティ
多い（増加行サイズの更新）
PCTFREE：20
多い
PCTFREE：10
少ない
PCTFREE：5
多い（削除を含む）
PCTUSED：60
多い
PCTUSED：40
少ない
PCTUSED：60
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
11
表と索引の管理のまとめ
TABLESPACE の設定
• オブジェクトの格納先を明示的に指定する
− ディスク I/O のバランスをとる
− オンライン・バックアップの単位を区別する
• セグメントの種類（表、索引、ロールバック、テンポラリー）で表領域を分ける
• アプリケーション毎に表領域を分ける
PCTFREE の設定
• 更新処理では行移行を避けるため、PCTFREE は大きめに設定する
• 更新の全くない検索専用表の PCTFREE は小さく設定する
• 索引の PCTFREE は小さくてよい場合が多い
• PCTFREE + PCTUSED ＜ 100 に設定する
STORAGE 句の設定
• 優先順位：①オブジェクト作成時の指定
②表領域の DEFAULT STORAGE 句
③システムのデフォルト
• 動的拡張を回避するには初期データを INITIAL に収める
• Oracle7 R7.3 からは MAXEXTENTS に UNLIMITED を指定できる
• PCTINCREASE はゼロにする。ただし表領域は 1 にする
DB_BLOACK_SIZE の設定
• データベース作成時にだけ指定できる
• OLTP は小さめ（2∼4KB）、DSS は大きめ（4∼16KB）に設定する
• 行サイズが大きい時はブロックサイズも大きく設定する
自動作成される索引に関する注意
• USING INDEX 句を用い、表とは別の索引用の表領域（TABLESPACE）を指定する
• 同様に STORAGE 句、PCTFREE 等も索引用に指定する
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
12
2. 表領域の管理
表領域全般の管理として注意することは、
ディスク I/O のバランスとバックアップの単位であることは
既に前章でも説明しました。ここでは表領域の拡張法について説明します。また、リリース 7.3 から追加
された表領域の管理の便利な機能である、空き領域を結合する Tablespace Coalesce とエクステントを縮
小・開放する Deallocate Unused Space についても説明します。空き領域の断片化を制御するために
Oracle8 から指定可能になった MINIMUM EXTENT パラメータについても解説します。
Tablespace Coalesce と Deallocate Unused Space、MINIMUM EXTENT パラメータについては、応用的な
内容で、DB 管理上級者向けの内容となります。しかし、表領域の拡張法については、便利で実践的な内
容ですので、初心者の方も是非参考にしてください。
2.1. 表領域の拡張法について
表領域に関するトラブルの多くは領域不足によるものです。領域を追加するには、手動で行う方法と、
自動で行う方法があります。手動で追加する方法は 2 通りあります。1 つは新しいデータファイルを追加
する方法です。もう 1 つは、既存のデータファイルのサイズを大きくする方法です。
ただし、あまりに大きなデータファイルを作成するには問題があります。本節中「データファイル・
サイズに関する注意」を参考にして、データファイルのサイズを検討してください。
手動でデータファイルを追加する方法
これはデータファイルを追加する方法です。次の構文で指定します。
ALTER TABLESPACE テーブルスペース名
ADD DATAFILE ’データファイル’ SIZE サイズ
（手動１）表領域 USER_DATA に 10MB のデータファイルを追加します。
SQL> ALTER TABLESPACE USER_DATA
2
ADD DATAFILE 'C:¥ORANT¥DATABASE¥USER2ORCL.ORA' SIZE 10M;
表領域が変更されました。
小さなデータファイルを複数追加することはお勧めできません。一つのエクステントはデータファイ
ルをまたいで割り当てることはできないので、エクステント・サイズが大きい場合、一つ一つのデータフ
ァイルの使用効率が低くなり、領域が無駄に使用される可能性があります。
手動でデータファイル・サイズを変更する方法
R7.2 以降では、次のようにすれば、データファイルを新しく追加するのではなく、既にあるデータ
ファイルのサイズを拡大または縮小することが可能です。次の構文で指定します。
ALTER DATABASE DATAFILE ’データファイル’
RESIZE サイズ
（手動 2）データファイル USR1ORCL.ORA のサイズを 10MB に変更（拡大）します。
SQL> ALTER DATABASE
2
DATAFILE 'C:¥ORANT¥DATABASE¥USR1ORCL.ORA'
3
RESIZE 10M;
データベースが変更されました。
(注意)ファイルサイズを縮小する場合、必ず指定したサイズにまでなるとは限りません。既に格納され
ているオブジェクトのサイズや格納位置が影響するからです。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
13
データファイル・サイズの自動拡張を作成時に設定する方法
R7.2 からは、データファイルの自動拡張を設定できるようになり、データファイルの初期設定サイ
ズを超える領域の割り当てが可能となりました。以下の構文で表領域を作成します。
CREATE TABLESPACE テーブルスペース名
DATAFILE ‘データファイル’
AUTOEXTEND ON NEXT サイズ MAXSIZE サイズ
DEFAULT STORAGE 句
（自動）10MB の表領域 DMS_DATA を作成します。さらに領域が必要な場合、10MB ずつ、最大 100MB まで自
動拡張させるには、次のように設定します。
SQL> CREATE TABLESPACE DMS_DATA
2
DATAFILE 'C:¥ORANT¥DATABASE¥DMS1ORCL.ORA' SIZE 10M
3
AUTOEXTEND ON NEXT 10M MAXSIZE 100M
4
DEFAULT STRAGE(INITIAL 1M NEXT 1M MAXEXTENTS 121 PCTINCREASE 1);
表領域が作成されました。
既存のデータファイルの自動拡張を設定する方法１
既存のデータファイルに自動拡張を指定することもできます。以下の構文で指定します。
ALTER DATABASE
DATAFILE ‘データファイル名’
AUTOEXTEND ON NEXT サイズ MAXSIZE サイズ
（自動）既存のデータファイル、C:¥ORANT¥DATABASE¥DMS1ORCL.ORA を 10MB ずつ、無制限まで自動拡張さ
せるには、次のように設定します。
SQL> ALTER DATABASE
2
DATAFILE 'C:¥ORANT¥DATABASE¥DMS1ORCL.ORA'
3
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
表領域が変更されました。
既存のデータファイルの自動拡張を設定する方法２
既存の表領域にデータファイルを追加するときに、自動拡張を指定することをもできます。以下の構
文で指定します。
ALTER TABLESPACE テーブルスペース名
ADD DATAFILE データファイル
AUTOEXTEND ON NEXT サイズ MAXSIZE サイズ
（自動）既存の表領域 DMS_DATA にデータファイルを追加します。追加するデータファイルを 10MB ずつ、
無制限まで自動拡張させるには、次のように設定します。
SQL> ALTER TABLESPACE DMS_DATA
2
ADD DATAFILE 'C:¥ORANT¥DATABASE¥DMS2ORCL.ORA' SIZE 10M
3
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
表領域が変更されました。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
14
データファイルの自動拡張設定を確認する方法
R7.3 以前： FILEXT$表を参照することで、自動拡張設定されているデータファイルのファイル ID を確
認できます。MAXEXTEND 列は拡張できる最大ブロック数、INC 列は自動拡張時の増分バイ
ト数を表します。その ID をもとに DBA_DATA_FILES を検索すれば、そのデータファイルの
情報を取得できます。
SQL> SELECT * FROM SYS.FILEXT$;
FILE# MAXEXTEND
INC
--------- --------- --------15
10240
2560
SQL> SELECT * FROM DBA_DATA_FILES WHERE FILE_ID = 15;
FILE_NAME
--------------------------------------------------------------------------FILE_ID TABLESPACE_NAME
BYTES
BLOCKS STATUS
--------- ------------------------------ --------- --------- --------D:¥ORANT¥DATABASE¥USR1ORCL.ORA
15 USER_DATA
10485760
5120 AVAILABLE
R8.0 以降： DBA_DATA_FILES 表を参照すれば、自動拡張設定を確認できます。自動拡張が設定されてい
れば、AUTOEXTENSIBLE 列が YES となっています。MAXBYTES は最大サイズのバイト数、
MAXBLOCKS は最大サイズのブロック数、INCREMENT_BY は増分サイズのバイト数を表します。
SQL> SELECT FILE_NAME, TABLESPACE_NAME,
2
AUTOEXTENSIBLE, MAXBYTES, MAXBLOCKS, INCREMENT_BY
3
FROM DBA_DATA_FILES WHERE AUTOEXTENSIBLE = 'YES';
FILE_NAME
--------------------------------------------------------------------TABLESPACE_NAME
AUT
MAXBYTES MAXBLOCKS INCREMENT_BY
------------------------------ --- --------- --------- -----------C:¥ORANT¥DATABASE¥USR1ORCL.ORA
USER_DATA
YES 157286400
76800
2560
１行が選択されました。
R7.2 以降を使用している場合、AUTOEXTEND 機能を活用することをお勧めします。
データファイル・サイズに関する注意
データファイルのサイズは 2GB より小さくすることをお勧めします。もし 2GB 以上の表領域を作成す
るのであれば、2GB より小さい複数のデータファイルから構成するようにすべきです。理由は以下の通
りです。
• 初めに不要な領域を確保しないので、バックアップ時間を短縮できる
• あまりに大きいデータファイルはパフォーマンスに悪影響を与える可能性がある
• I/O 負荷を複数のディスクに分散できる
• I/O のボトルネックが気になるようなケースでも、データファイルの物理ディスク間移動が容易
にできる
データファイルが多いとメモリを多少消費しますが、I/O バランスと管理上のメリットの方が大きい
でしょう。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
15
自動拡張のクセに注意する
自動拡張は便利な機能ですが、注意すべき点もあります。次のようなシナリオを考えてください。表
領域 USERS は、次の 2 つのデータファイルから構成されています。
l
C:¥Orant¥Database¥usr01.ora
l
D:¥Orant¥Database¥usr02.ora
そして、それぞれのデータファイルには自動拡張の設定がしてあります。
このときに表領域の自動拡張が起きると、Oracle は自動拡張の設定がある 1 つのデータファイル
（usr01.ora）の拡張を試みます。ところがこのときにディスクドライブ自体の空き容量がないと、
Oracle は次のデータファイルの拡張を試みるのではなく、単に「ディスクの空き容量不足」のエラー
で終了してしまいます。たとえもう一つのデータファイルを拡張するのに十分なディスク領域があって
も、それを使用することはありません。
このように、１つの表領域が複数のディスクドライブに分散しているときは、MAXSIZE UNLIMITED は
万能ではないことに注意してください。MAXSIZE をディスクの空き容量にあわせて指定することも一考
でしょう。
2.2. Tablespace Coalesce について
．．．．
エクステントは連続するデータベース・ブロックの集まりです。すなわち、エクステントを割り当て
るのに必要な空き領域は同じ表領域内で連続していなくてはいけません。これが原因で発生するトラブル
があります。表領域には、まだ空き領域が残っているのに、新しいエクステントを追加できない場合があ
るのです。例えば、図 2.1.の状態②です。空き領域は合計で 30MB あるにもかかわらず、次の表 A エクス
テント（20MB）を割り当てることができずにエラーになってしまうのです。
データ・ファイル
データ・ファイル
表Aエクステント1（
20MB）
表Bエクステント1（15MB）
表Bエクステント2（15MB）
表Aエクステント2（
20MB）
データ・ファイル
表Aエクステント1（20MB）
表Bを削除
エクステントの残骸（
15MB）
エクステントの残骸（
15MB）
表Aエクステント1（
20MB）
COALESCE
表Aエクステント2（20MB）
状態①
結合された空領域（
30MB）
表Aエクステント2（
20MB）
状態②
状態③
図 2.1. 空き領域の結合処理
状態①：
表 A と表 B のエクステントが図のように存在しています。
状態②：
表 B が削除され、合計 30MB の領域が開放されたにもかかわらず、15MB ずつ細切れになっ
ているため、表 A の次のエクステント（20MB）を確保することができません。
状態③：
エクステントの残骸が結合され、30MB の空き領域が作成されました。この状態なら表 A
の次の 20MB のエクステントを確保できます。
この問題を解決するするために空き領域の結合処理を行います。Tablespace Coalesce は、指定した表
領域内で DROP や TRUNCATE して開放されたのに、細切れになって残っている連続した空き領域（エクステ
ントの残骸）を一つの空き領域に変換するコマンドです。従来は SMON プロセスが定期的に空き領域の結
合を行っていましたが、この機能により空き領域の結合処理を随時実行できるようになりました。例えば、
図 2.1.状態③では空き領域の結合処理により 30MB の領域が使用可能となっています。これで、次の表 A
エクステント（20MB）を割り当てることが可能となりました。
USER_DATA 表領域に空き領域の結合処理を行うとき、以下のコマンドを実行します。
SQL> ALTER TABLESPACE USER_DATA COALESCE;
表領域が変更されました。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
16
2.3. Deallocate Unused Space について
予想したデータ量をもとに巨大なエクステントを確保したのに、実際のデータ量は少なかった、とい
う場合があります。ところが、一度確保したエクステントは DROP または TRUNCATE コマンドによってしか
開放することはできません。確保された巨大なエクステントはそのほとんどにデータを格納することなく、
他の誰も使えない領域を無駄に占有することになるのです。例えば、図 2.2.の状態①です。50MB のエク
ステントを確保したにもかかわらず、それ以上増えることもないデータは 10MB 程しか格納されていませ
ん。40MB 分の領域が無駄に使用されているのです。
データ・
ファイル
データ・
ファイル
高水位標
高水位標
エクステント（
50MB）
未使用領域（
40MB）
10MB）
エクステント（
開放された領域（
40MB）
DEALLOCATE
空き領域（
10MB）
空き領域（
10MB）
状態①
状態②
図 2.2. 未使用領域の切り分け
状態①：
エクステント（50MB）、連続空き領域（10MB）が存在しています。この状態では 10MB 以
下のエクステントしか割り当てることはできません。
状態②：
エクステント内の高水位標以上の領域が開放され、40MB、10MB の 2 つの空き領域が存在
しています。この状態なら 40MB 以下のエクステントを割り当てることが可能です。
DEALLOCATE UNUSED SPACE（未使用領域の切り分け）は、エクステント内の未使用領域を開放し表領域
中の別のオブジェクトが使用できるようにする機能です。この機能により、エクステントを高水位標まで
随時、縮小・開放することができます。またオプションの KEEP 句を使用して、高水位標以上の任意の大
きさにエクステンントを縮小することも可能です。ただし、INITIAL×MINEXTENTS で確保された領域は、
このコマンドでも開放することはできないことに注意してください。図 2.2 の状態②では無駄に使用され
ていた領域が開放され、40MB までのエクステントを割り当てることができるようになっています。
EMP 表の未使用領域の開放処理を行う時、以下のコマンドを実行します。
SQL> ALTER TABLE EMP DEALLOCATE UNUSED
KEEP 100K;
表が変更されました。（高水位標以上 100KB の空き領域を残しています）
SQL> ALTER TABLE EMP DEALLOCATE UNUSED;
表が変更されました。（高水位標まですべての空き領域を開放しています）
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
17
2.4. MINIMUM EXTENT パラメータについて
Oracle8 から表領域の空き領域の断片化を制御するためのパラメータとして MINIMUM EXTENT が追加さ
れました。概要は以下の通りです。
• CREATE/ALTER TABLESPACE コマンドで指定
• バイト数を指定するが、Oracle がブロックサイズの倍数に切り上げる
• 表領域内のすべてのエクステント（使用済みおよび空き領域を含めて）は、このパラメータで指定
したサイズの倍数になる
• DBA_TABLESPACES および USER_TABLESPACES ビューの MIN_EXTLEN 列で指定したサイズを参照する
ことができる
表領域には空きがあるのに新しいエクステントを割り当てられない、という断片化現象を第 2.2 節で説
明しました。断片化現象は DROP や TRUNCATE によって開放されても割り当てることができないほど小さ
な空エクステントが残ってしまうことに起因しています。
MINIMUM EXTENT パラメータは、
このような利用できないほど小さいエクステントが残らないようにす
るために設定する「エクステント・サイズの最小値」パラメータです。このパラメータにより、表領域内
のすべてのエクステント（使用済みおよび空き領域を含めて）は指定したサイズの倍数になります。
MINIMUM EXTENT パラメータを設定することで表領域の断片化の削減を期待できます（図 2.3 参照）。
MINIMUM EXTENT 1M 指定
MINIMUM EXTENT指定無し
0.8MB
1.5MB
0.8MB
2MB
2MB
1.6MB
1.4MB
2MB
1MB
1MB
1MB
1MB
1MB
2MB
1MB
1.3MB
1MB
1MB
2MB
1MB
状態①
状態②
図 2.3. MINIMUM EXTENT パラメータ指定
状態①：
MINIMUM EXTENT は指定されていないある表領域の状態です。様々なサイズのエクステン
トが割り当てられ、断片化が発生しています。
状態②：
MINIMUM EXTENT 1M と指定されている表領域です。すべてのエクステントのサイズは 1MB
の倍数になっており、断片化は発生しにくくなっています。
表に指定されたエクステント・サイズと、例えば MINIMUM EXTENT 1M と指定された表領域に実際に割
り当てられるエクステント・サイズの関係を以下に示します。
表に指定したエクステント・サイズ
実際に割り当てられるエクステント・サイズ
500KB
1MB
1.5MB
2MB
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
18
実際に表領域を作成し、割り当てられるエクステントのサイズを確認してみましょう
MINIMUM EXTENT パラメータを表領域に指定します。ここでは TBS01 表領域作成時に MINIMUM
EXTENT を 1MB と指定します。
SQL> CONNECT SYSTEM/MANAGER
接続されました。
SQL> CREATE TABLESPACE TBS01
2
DATAFILE 'C:¥ORANT¥DATABASE¥TBS01.ORA' SIZE 50M
3
AUTOEXTEND ON NEXT 10M MAXSIZE 200M
4
MINIMUM EXTENT 1M
5
DEFAULT STORAGE ( INITIAL 1M NEXT 1M PCTINCREASE 0);
表領域が作成されました。
DBA_TABLESPACES 表を参照して MINIMUM EXTENT に指定されたサイズを確認できます。ここでは
TBS01 表領域の MINIMUM EXTENT サイズを確認します。
SQL> SELECT TABLESPACE_NAME, MIN_EXTLEN
2
FROM DBA_TABLESPACES
3
WHERE TABLESPACE_NAME = 'TBS01';
TABLESPACE_NAME
MIN_EXTLEN
------------------------------ ---------TBS01
1048576
TBS01 表領域には 1048576 バイト、すなわち 1MB の MINIMUM EXTENT が指定されていることがわかり
ます。
TBS01 表領域にユーザーSCOTT でエクステント・サイズが 500KB になるような EXT500K 表を作成しま
す。
SQL> CONNECT SCOTT/TIGER
接続されました。
SQL> CREATE TABLE EXT500K (COL1 CHAR(10))
2
TABLESPACE TBS01
3
STORAGE ( INITIAL 500K NEXT 500K PCTINCREASE 0);
表が作成されました。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
19
DBA_EXTENTS 表を参照し、EXT500K 表に実際に割り当てられたエクステント・サイズを確認します。
SQL> CONNECT SYSTEM/MANAGER
接続されました。
SQL> SELECT OWNER, SEGMENT_NAME, TABLESPACE_NAME,
2
EXTENT_ID, BYTES, BLOCKS
3
FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'EXT500K';
OWNER
-----------------------------SEGMENT_NAME
--------------------------------------------------------------------------------TABLESPACE_NAME
EXTENT_ID
BYTES
BLOCKS
------------------------------ --------- --------- --------SCOTT
EXT500K
TBS01
0
1048576
512
EXT500K 表の第一エクステント
（EXTENT_ID = 0）
が TBS01 表領域に割り当てられ、
そのサイズは 1048576
バイト（512 ブロック）、すなわち 1MB となっています。表に指定した 500KB ではなく、表領域に指定
した MINIMUM EXTENT のサイズでエクステントが作成されていることが確認できます。
2.5. ローカル管理表領域について
Oracle 8i R8.1.5 から新しい種類の表領域としてローカル管理表領域が加わりました。
ローカル管理表領域では、
従来のようにデータディクショナリでエクステントを管理するのではなく、
データファイルの一部にビットマップ形式の管理エリアを確保し、エクステントをビットとして管理し
ます。この新しいアーキテクチャによって、エクステント拡張のための再帰オペレーションや、空きエ
クステント結合などのオーバヘッドを抑制することができます。
ローカル管理表領域のおもな特徴は次の２点です。
オーバーヘッドの削減
従来の管理方法では、
空きエクステントを使うためにはデータディクショナリを更新する必要があり
ました。またデータディクショナリを更新するために、ロールバック情報を生成しました。しかし、新
しいローカル管理表領域では、各エクステントの使用状態の情報をデータファイル内に保持しているの
で、データディクショナリを更新することはありません。またロールバック情報も生成されません。
エクステントのフラグメンテーションの解消
隣接する 1MB と 2MB の連続した空きエクステントがあるとします。このとき従来のデータディクシ
ョナリ管理の表領域では、それぞれのエクステントが連結されておらず、これを連続した 3MB の空き
エクステントを使用することができませんでした。しかしローカル管理の表領域では、連続した空き領
域と認識できるため、このようなことは発生しません
今後両者を明確に区別したいときは、従来の表領域のことをディクショナリ管理の表領域と記述しま
す。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
20
ローカル管理表領域を作成する
ローカル管理表領域を作成するために、CREATE TABLESPACE 文に EXTENT MANAGEMENT LOCAL オプ
ションが追加されました。
従来のディクショナリ管理の表領域では、エクステントサイズはオブジェクトごとに可変でしたが、
ローカル管理表領域では表領域単位で均一になります。エクステントサイズは表領域作成時に、
UNIFORM SIZE または AUTOALLOCATE オプションを指定します。
UNIFORM SIZE オ プ シ ョ ン を 指 定 す る と 、 明 示 的 に エ ク ス テ ン ト サ イ ズ を 指 定 で き ま す 。
AUTOALLOCATE を指定した場合には、Oracle は自動的に最適なエクステントサイズを設定します。本書
の一時表領域の章で説明する、「エクステントがローカル管理される一時表領域（テンポラリファイル
を使った一時表領域）」の場合は、UNIFORM SIZE オプションだけが指定可能です。AUTOALLOCATE オ
プションは、永続表領域のデフォルトで、UNIFORM SIZE を指定したときのデフォルトは 1MB です。
AUTOALLOCATE の最小エクステント・サイズは 64KB です。
実際に AUTOALLOCATE オプションや UNIFORM SIZE オプションを使ってローカル表領域を作成してみ
ましょう。構文は次のようになっています。
・エクステントサイズを自動計算する場合
CREATE TABLESPACE 表領域名
サイズ[K|Ｍ]
DATAFILE ‘ファイルのディレクトリ’SIZE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
・エクステントサイズを指定する場合
CREATE TABLESPACE 表領域名
DATAFILE ‘ファイルのディレクトリ’SIZE
サイズ[K|M] EXTENT MANAGEMENT LOCAL UNIFORM SIZE サイズ［K|M］
SQL> CREATE TABLESPACE LOCAL_AUTO
2 DATAFILE 'E:¥ORACLE¥ORADATA¥ORCL¥LOCAL_A01.DBF'
3
SIZE 5M EXTENT MANAGEMENT LOCAL AUTOALLOCATE
表領域が作成されました。
SQL> CREATE TABLESPACE LOCAL_UNI
2
DATAFILE 'E:¥ORACLE¥ORADATA¥ORCL¥LOCAL_U01.DBF'
3
SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 500K;
表領域が作成されました。
SQL>SELECT TABLESPACE_NAME, CONTENTS, LOGGING,
2
EXTENT_MANAGEMENT FROM DBA_TABLESPACES
3
WHERE TABLESPACE_NAME LIKE 'LOCAL_%';
TABLESPACE_NAME
CONTENTS
LOGGING
EXTENT_MAN
------------------------------ --------- --------- ---------LOCAL_UNI
PERMANENT
LOGGING
LOCAL
LOCAL_AUTO
PERMANENT
LOGGING
LOCAL
以上でローカル管理表領域が作成できました。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
21
表領域の管理のまとめ
表領域の拡張
データファイルの追加・拡張方法：
• 手動追加法
：ALTER TABLESPACE ADD DATAFILE
• 手動拡張法（R7.2 以降） ：ALTER DATABASE DATAFILE RESIZE
• 自動拡張法（R7.2 以降） ：ALTER DATABASE DATAFILE AUTOEXTEND ON
ø 2GB 以上のデータファイルは作成しない
TABLESPACE COALESCE
• エクステントの残骸を一つの空き領域に変換するコマンド
DEALLOCATE UNUSED SPACE
• エクステント内の未使用領域を開放し、他オブジェクトが使用可能にするコマンド
MINIMUM EXTENT
• 表領域の空き領域の断片化を制御するためのパラメータ
• 表領域内のすべてのエクステント（使用済みおよび空き領域を含めて）はこのパラメータで指定し
たサイズの倍数になる
ローカル管理の表領域
• Oracle8i R8.1.5 から新しく導入された表領域。
• 表領域内に作成したビットマップでエクステントを管理するため、効率的な領域管理ができる。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
22
3. ロールバック・セグメントの管理
50×121＝6050 キロバイト。これは初期データベース(Windows NT 版)のロールバックセグメントの最大
サイズです。ロールバックセグメントにはトランザクションのロールバック情報が格納されます。つまり
初期データベースをそのまま使い続けた場合、これ以上のロールバック情報が生成されるトランザクショ
ンはエラーになります。
少量のデータでテストしているときには、この問題に気付かないかもしれません。実運用に入って、
はじめて発覚することがよくあります。
この章では、これら問題を防止するためのロールバックセグメントの設定方法を解説します。
3.1.サイズを考える
ロールバックセグメントが最大エクステント数に達してしまったとき、次のエラーが発生します。
ORA-01562: ロールバック・セグメント番号: 3 を拡張できません。
ORA-01628: 最大エクステント: 121 に達しました(ロールバック・セグメント: RB2)。
ロールバックセグメントの最大エクステント数は、表や索引と同じくＤＢブロックサイズとプラット
フォームに依存します。次の表はブロックサイズごとの最大エクステント数です。
ブロックサイズ
最大エクステント数
2K
121
4K
255
8K
504
本来、運用前に十分なテストを行い、どのくらいのロールバック情報が生成されるのか調査するべき
です。十分な見積作業をしたうえで適切な INITIAL や NEXT を設定すれば、このような問題は発生しませ
ん。見積方法については第 3.9.節で説明します。
ロールバックセグメントの大きさや個数は、アプリケーションの種類によって決定します。
OLTP 系
比較的小さいトランザクションが多く発生する場合は、INITIAL が 100K∼200K 程度のロールバックセ
グメントを多数用意します。サイズが小さければ、キャッシュされる可能性が高まります。
ただし同一の表に対し大規模な問い合わせと OLTP が行われる場合には注意が必要です。Oracle は読み
取り一貫性を施行するためにロールバックセグメントを使用します。したがってロールバックセグメント
が小さすぎると、読み取り一貫性を実現するために必要なロールバックエントリが上書きされてしまい
「ORA-01555：スナップショットが古すぎます」が発生する可能性があります。
バッチ系
大量データの更新処理では、大きなロールバックセグメントを用意します。領域不足によるエラーを
回避するとともに、あらかじめ十分な領域を確保することによってパフォーマンスを向上させます。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
23
組み合わせ処理
小さな OLTP と大規模バッチ処理があるときには、どのようにすればよいのでしょうか。一つの方法は、
バッチ処理のときだけ特定のロールバックセグメントを割り当てる方法です。通常 Oracle は、活動中の
トランザクションが最も少ないロールバックセグメントから順に使用します。しかし SET TRANSACTION コ
マンドを使うことによって、明示的にロールバックセグメントを指定することができます。
もう一つの方法は、すべてのロールバックセグメントをバッチ処理に耐えうる大きさにする方法です。
ソースコードを変更できないパッケージアプリケーションや、スナップショットのリフレッシュなど、SET
TRANSACTION コマンドが使えないときに有効な方法です。
特定のロールバックセグメントを割り当てる方法
ビッグ・トランザクションだけを特定のロールバックセグメントに割り当てる方法を説明します。
１．まず事前に専用のロールバックセグメントを作成します。
２．ほかのトランザクションに使われないように、そのロールバックセグメントをオフラインの状態
にしておきます。
３．大きなトランザクションを実行する前にロールバックセグメントをオンラインにし、SET
TRANSACTION コマンドでロールバックセグメントを指定します。
４．トランザクションが終了したら、このロールバックセグメントをオフラインにします。これでほ
かのトランザクションがこのロールバックセグメントを使用することを防ぎます。
ALTER ROLLBACK SEGMENT rbs_large ONLINE;
SET TRANSACTION USE ROLLBACK SEGMENT rbs_large;
∼ここでバッチ処理∼
ALTER ROLLBACK SEGMENT rbs_large OFFLINE;
3.2.個数を考える
OLTP 処理では、トランザクション 4 つあたりロールバック･セグメント 1 つが推奨されています。次の
表を参考にしてロールバック・セグメントの数を決定してください。
同時実行トランザクション数（N）
ロールバック･セグメント数の推奨値
N ＜ 16
4
16 ≦ N ＜ 32
8
32 ≦ N
N / 4 （50 以下）
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
24
3.3.鉄則
これはロールバックセグメントに関する基本ルールです。
・１つのトランザクションは、ロールバックセグメントをまたがって使うことはできない。
・ロールバックセグメントの PCTINCREASE はつねにゼロで、変更することはできない。
・ロールバックセグメントの MINEXTENTS の最小値は２。
・ロールバックセグメントは I/O が多く、領域の確保/解放が起こりやすいので専用の表領域を確保する。
・フラグメンテーションを防止するため INITIAL と NEXT のサイズは同じにする。
・エクステントの個数を１０∼３０の範囲に抑えたほうがパフォーマンス上好ましい。
3.4.設定例１
本来ロールバックセグメントのサイズは、生成されるロールバックエントリのサイズを調査してから
決定すべきです。しかし、とりあえずエラーなしで動くことを優先することもあります。たとえば調査が
できない場合や、開発作業中でデータ量の変動が大きい場合は、とりあえずエラーなしで動くことを優先
するでしょう。またパッケージアプリケーションのように、使用するユーザーによって著しくデータ量が
異なる場合もあります。
ここでは処理別の典型的な設定例を紹介します。なお DB ブロックサイズが 2K バイトで、ロールバック
セグメントの数が４つの場合を想定しています。
OLTP の場合
OLTP ではエクステントサイズが小さい方が好ましいので INITIAL、NEXT ともに 200K バイトにします。
200K バイトにしておけば 24MB(＝200K×121)までのロールバックエントリに耐えられます。パフォーマン
スを重視するため MINEXTENTS は 20 にします。
ロールバックセグメント用の表領域として、それぞれのロールバックセグメントが最大サイズまで拡
大しても大丈夫なように領域を確保します。
INITIAL
:200K
OLTP は小さめに設定
NEXT
:200K
INITIAL と同じサイズ
MINEXTENTS
:20
パフォーマンス重視の値
表領域サイズ
:24MB×4=96≒100MB
一つのロールバックセグメントの最大サイズ
×ロールバックセグメントの個数
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
25
バッチ処理
バッチ処理ではエクステントサイズが大きい方が好ましいので INITIAL、NEXT ともに 2M バイトにしま
す。2M バイトにしておけば 242M バイト(＝2M×121)までのロールバックエントリに耐えられます。パフ
ォーマンスを重視するため MINEXTENTS は 20 にします。
ロールバックセグメント用の表領域として、それぞれのロールバックセグメントが最大サイズまで拡
大しても大丈夫なように領域を確保します。
INITIAL
:2M
バッチ処理は大きめに設定
NEXT
:2M
INITIAL と同じサイズ
MINEXTENTS
:20
パフォーマンス重視の値
表領域サイズ
:242MB×4=968≒1GB
1 ロールバックセグメントの最大サイズ
×ロールバックセグメントの個数
組み合わせ処理(SET TRANSACTION 指定可能)
SET TRANSACTION を使ってバッチ処理用のロールバックセグメントを指定できる場合を考えます。OLTP
用とバッチ処理用のロールバックセグメントをそれぞれ４つと１つ作成します。ロールバックセグメント
用の表領域として、それぞれのロールバックセグメントが最大サイズまで拡大しても大丈夫なように領域
を確保します。
OLTP 用：
INITIAL
:200K
OLTP 用は小さめに設定
NEXT
:200K
INITIAL と同じ
MINEXTENTS
:20
パフォーマンス重視
INITIAL
:2M
バッチ処理用は大きく設定
NEXT
:2M
INITIAL と同じ
MINEXTENTS
:20
パフォーマンス重視
バッチ処理用：
表領域サイズ
24MB×4＋242MB=338≒350MB :OLTP 用最大サイズ×OLTP 用個数＋バッチ処理用最大サイズ
3.5.設定例２(OPTIMAL を使う)
問題になるのは SET TRANSACTION を使ってロールバックセグメントを指定できないときです。そのとき
にはすべてのロールバックセグメントをビッグトランザクションに耐えうるサイズにしておかなければ
なりません。たとえば最大 500M バイトのロールバックエントリが発生するとします。そのときはロール
バックセグメント用の表領域として 2000M バイト(＝500MB×4)確保しなければなりません。同時トランザ
クション数が多いシステムでは、これ以上のロールバックセグメントが必要になります。
たまにしか発生しないバッチ処理のために、たくさんの領域を確保するのはディスクの無駄です。こ
のためにあるのが OPTIMAL です。STORAGE 句の一部として OPTIMAL を指定することによって、ロールバッ
クセグメントを任意のサイズまで縮小することができます。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
26
OPTIMAL は便利な機能ですが、勘違いしている人もいるので次のことに注意してください。
・ロールバックセグメントが縮小されるのは、コミットしたタイミングではなく、ロールバック・エン
トリがエクステントの境界をまたがるときです。
・OPTIMAL のサイズまで必ずしも縮小するわけではありません。解放しようとするエクステントに活動中
のトランザクションがあった場合には、その時点で解放を中止します。
組み合わせ処理(SET TRANSACTION 使用不可)
ほとんどのトランザクションは 1M バイト以内。月に１度発生するバッチ処理時のトランザクションエ
ントリが 500M バイトの場合を考えます。
１．エクステントサイズを求めます。最大ロールバックエントリ量を最大エクステント数で割ったも
のがエクステントサイズになります。余裕を持って 5M バイトにします。5×121＝605M バイトまで
耐えられます。
500MB÷121＝4.1≒5MB
２．MINEXTENTS はとりあえず 10 にします。したがって OPTIMAL は 50M バイトです。空きディスク容量
が少ないのであれば、MINEXTENTS は 2 でも良いでしょう。もちろん OPTIMAL もそれにあわせて減
らします。
３．ロールバックセグメント用の表領域のサイズを求めます。エクステントを OPTIMAL まで縮小できな
いことも考えられるので、表領域として 800M バイト確保します。
(5MB×10)×3＋5MB×121＝755MB
よって次のように設定します。
INITIAL
:5M
ステップ１で求めたエクステントサイズ
NEXT
:5M
INITIAL と同じ値
MINEXTENTS
:10
ディスク空き容量が少なければ、もっと小さな値でもよい
OPTIMAL
:50M
INITIAL×MINEXTENTS
表領域のサイズ
:800M
ステップ３でもとめた、必要サイズ
OLTP(ロールバックエントリ見積済み)
先ほどは OLTP やバッチ処理など見積なしに設定する例を紹介しました。ここでは見積りを行い、
OPTIMAL を併用する場合を考えます。
見積りをした結果、ほとんどのトランザクションエントリのサイズは２ MB 以下に収まることが分かり
ました。突発的に発生するビックトランザクションは、ほとんど無いようですが、100%無いとは言い切れ
ません。
１．MINEXTENTS を 20 にしたときのエクステントサイズを求めます。
2048KB÷20＝102.4≒100KB
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
27
２．ロールバックセグメント用の表領域のサイズを求めます。最悪のケースも考えて、１つのロール
バックセグメントが最大エクステント数まで拡張しても平気なようにします。
(100KB×20)×3＋100KB×121＝18100KB≒18MB
よって次のように設定します。
INITIAL
:100K
ステップ１の計算結果より
NEXT
:100K
INITIAL と同じ値
MINEXTENTS
:20
パフォーマンス重視
OPTIMAL
:2000K
エクステントサイズ×MINEXTENTS
表領域のサイズ
:20M
２．の結果より少し大きめ
3.6.初期データベースをカスタマイズする
この節では、ここまで説明してきたことをもとに初期データベースをカスタマイズします。初期デー
タベースには RB1∼RB16 までの１６個のロールバックセグメントがあります(SYSTEM は特別なので除外)。
このロールバックセグメントは INITIAL、NEXT ともに 50K バイトなので、少し大きいトランザクションを
実行するとエラーになります。
そこで次のようなパラメータを持つロールバックセグメントを実際に作成する方法を解説します。こ
のロールバックセグメントであれば最大 121M バイトまでのロールバックエントリに耐えられます。
INITIAL
:1M
NEXT
:1M
MINEXTENTS
:10
MAXEXTENTS
:121
OPTIMAL
:10M
表領域のサイズ
:10MB×3＋1M×121＝151MB
１．SQL*Plus もしくはサーバーマネージャを起動し、データベース管理者ユーザーで接続します。
２．表領域 ROLLBACK_DATA のサイズを 150M バイトに拡張します。
ALTER DATABASE DATAFILE 'C:¥ORANT¥DATABASE¥RBS1ORCL.ORA'
RESIZE 150M;
３．表領域が不足しても大丈夫なように自動拡張の設定を行います。
ALTER DATABASE DATAFILE 'C:¥ORANT¥DATABASE¥RBS1ORCL.ORA'
AUTOEXTEND ON NEXT 10M MAXSIZE 200M;
４．新しいロールバックセグメント R01∼R04 を作成します。
CREATE PUBLIC ROLLBACK SEGMENT R01
TABLESPACE ROLLBACK_DATA
STORAGE(INITIAL 1M NEXT 1M MINEXTENTS 10 OPTIMAL 10M);
CREATE PUBLIC ROLLBACK SEGMENT R02
TABLESPACE ROLLBACK_DATA
STORAGE(INITIAL 1M NEXT 1M MINEXTENTS 10 OPTIMAL 10M);
CREATE PUBLIC ROLLBACK SEGMENT R03
TABLESPACE ROLLBACK_DATA
STORAGE(INITIAL 1M NEXT 1M MINEXTENTS 10 OPTIMAL 10M);
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
28
CREATE PUBLIC ROLLBACK SEGMENT R04
TABLESPACE ROLLBACK_DATA
STORAGE(INITIAL 1M NEXT 1M MINEXTENTS 10 OPTIMAL 10M);
５．作成した直後はオフラインになっているので、すべてオンラインにします。
ALTER ROLLBACK SEGMENT R01 ONLINE;
ALTER ROLLBACK SEGMENT R02 ONLINE;
ALTER ROLLBACK SEGMENT R03 ONLINE;
ALTER ROLLBACK SEGMENT R04 ONLINE;
６．ロールバックセグメントの現在のステータスを確認します。
SQL> SELECT segment_name,status from dba_rollback_segs;
SEGMENT_NAME
STATUS
------------------------------ ---------------SYSTEM
ONLINE
RB_TEMP
OFFLINE
RB1
ONLINE
RB2
ONLINE
RB3
ONLINE
RB4
ONLINE
RB5
ONLINE
RB6
ONLINE
RB7
ONLINE
RB8
OFFLINE
RB9
OFFLINE
RB10
OFFLINE
RB11
OFFLINE
RB12
OFFLINE
RB13
OFFLINE
RB14
OFFLINE
RB15
OFFLINE
RB16
OFFLINE
R01
ONLINE
R02
ONLINE
R03
ONLINE
R04
ONLINE
７．オンラインのロールバックセグメントは削除できないので、オフラインにします。
ALTER ROLLBACK SEGMENT RB1 OFFLINE;
ALTER ROLLBACK SEGMENT RB2 OFFLINE;
ALTER ROLLBACK SEGMENT RB3 OFFLINE;
ALTER ROLLBACK SEGMENT RB4 OFFLINE;
ALTER ROLLBACK SEGMENT RB5 OFFLINE;
ALTER ROLLBACK SEGMENT RB6 OFFLINE;
ALTER ROLLBACK SEGMENT RB7 OFFLINE;
ALTER ROLLBACK SEGMENT RB8 OFFLINE;
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
29
８．はじめから作成してあるロールバックセグメントを削除します。
DROP PUBLIC ROLLBACK SEGMENT RB1;
DROP PUBLIC ROLLBACK SEGMENT RB2;
DROP PUBLIC ROLLBACK SEGMENT RB3;
DROP PUBLIC ROLLBACK SEGMENT RB4;
DROP PUBLIC ROLLBACK SEGMENT RB5;
DROP PUBLIC ROLLBACK SEGMENT RB6;
DROP PUBLIC ROLLBACK SEGMENT RB7;
DROP PUBLIC ROLLBACK SEGMENT RB8;
DROP PUBLIC ROLLBACK SEGMENT RB9;
DROP PUBLIC ROLLBACK SEGMENT RB10;
DROP PUBLIC ROLLBACK SEGMENT RB11;
DROP PUBLIC ROLLBACK SEGMENT RB12;
DROP PUBLIC ROLLBACK SEGMENT RB13;
DROP PUBLIC ROLLBACK SEGMENT RB14;
DROP PUBLIC ROLLBACK SEGMENT RB15;
DROP PUBLIC ROLLBACK SEGMENT RB16;
９．再びロールバックセグメントのステータスを確認します。このように表示されれば終了です。
SQL> SELECT SEGMENT_NAME,STATUS FROM DBA_ROLLBACK_SEGS;
SEGMENT_NAME
STATUS
------------------------------ ---------------SYSTEM
ONLINE
RB_TEMP
OFFLINE
R01
ONLINE
R02
ONLINE
R03
ONLINE
R04
ONLINE
１０．新しく作成したロールバックセグメントはパブリックなので、データベース起動時に自動的に
オンラインになります（下記注意参照）。しかし、ここではオンラインにするロールバックセグ
メントを明示的に指定します。INIT.ORA ファイルに次の１行を追加します。
ROLLBACK_SEGMENTS=(R01,R02,R03,R04)
（注意）Oracle は必要なロールバックセグメント数として、次の初期化パラメータから求まる値と等
しい数のロールバックセグメントをオンラインにしようとします。ROLLBACK_SEGMENTS で指定
したロールバックセグメント数が充分であれば、それ以上のアクションはありません。しかし、
さらにロールバックセグメントが必要な場合は、次の式で求まる数に不足する分のパブリッ
ク・ロールバックセグメントをオンラインにしようとします。
Oracle が起動時に取得しようとするロールバックセグメント数：
CEIL(TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT)
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
30
3.7.UNLIMITED EXTENTS について
Oracle7 R7.3 から MAXEXTENTS に UNLIMITED を指定できるようになりました。表や索引だけでなくロー
ルバックセグメントに指定することもできます。ロールバックセグメントの MAXEXTENTS に UNLIMITED を
指定するためには、COMPATIBLE パラメータを 7.3 以上にします。そして Oracle7 R7.3.3∼Oracle7 R7.3.4
の場合は、INIT.ORA パラメータの UNLIMITED_ROLLBACK_SEGMENTS の値を TRUE に設定する必要があります。
Oracle8 からは、このパラメータが廃止されたので設定する必要はありません。
しかしロールバックセグメントの MAXEXTENTS に UNLIMITED を指定することはお勧めできません。次の
ような理由から、著しくパフォーマンスが低下する可能性があるからです。
・INITIAL と NEXT が小さいと、極めて大量のエクステントが発生する。
・OPTIMAL が設定してあると、大量の領域解放が発生する。
ロールバックセグメントの MAXEXTENTS を UMLIMITED に指定することは、正式サポートの対象外になり
ます。つまり設定した方の責任の元で行っていただくことになります。
3.8.動的縮小方法（SHRINK）
Oracle7 R7.3 以上を使用している場合は、ALTER ROLLBACK SEGMENT rbs SHRINK TO size コマンドを使用
し動的にロールバック・セグメントの空エクステントを開放することができます。縮小するサイズを指定
して OPTIMAL サイズより小さな（大きな）値まで縮小させることもできます。
SQL> ALTER ROLLBACK SEGMENT RB1 SHRINK;
ロールバック・セグメントが変更されました。（OPTIMAL サイズまで縮小）
SQL> ALTER ROLLBACK SEGMENT RB2 SHRINK TO 100K;
ロールバック・セグメントが変更されました。（100KB まで縮小）
SHRINK 句に関する詳細はマニュアル『Oracle8 Server SQL リファレンス：Vol.1』4-66 ページの「ALTER
ROLLBACK SEGMENT コマンド」を参考にしてください。
3.9.V$ROLLSTAT を使用したサイズの見積もり方法
動的パフォーマンス表 V$ROLLSTAT より得た値から本番用のロールバック・セグメントに必要なサイズ
を推測することができます。以下にその手順を示します。
1. あらかじめ、小さめのロールバック・セグメントを作成しておきます（OLTP 処理の場合、STORAGE
句で INITIAL 100K、NEXT 100K 程度にして、同時実行トランザクション数にあった個数を用意します）。
2. Oracle を起動します。
3. テスト運用を実施します。
このテスト運用が本番での処理に近いほど見積もりの精度が高まります。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
31
4. テスト運用終了後、以下のコマンドを実行して、テスト運用中に必要とされたロールバック・セグ
メントの最大サイズ（byte）を求めます。
SQL> SELECT MAX(HWMSIZE) FROM V$ROLLSTAT;
MAX(HWMSIZE)
-----------2097152
5. 次の式を用いて MINEXTENTS が 10∼30 になるように INITIAL の値を設定します。
MAX( HWMSIZE ) = INITIAL * MINEXTENTS
10 ≦ MINEXTENTS ≦ 30
ここでは MINEXTENTS を 21 として STEP4 で得た MAX( HWMSIZE )の値より INITIAL を求めてみます。
MAX( HWMSIZE ) = INITIAL * MINEXTENTS
2097152 = INITIAL * 21
INITIAL = 2097152 / 21 = 99864.4 (byte) < 100 (KB)
この結果から INITIAL = NEXT = 100KB、MINEXTENTS = 21 と決定できます。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
32
ロールバック･セグメントの管理のまとめ
トランザクションの種類とサイズ
• OLTP 系：INITIAL 100K 程度を多数用意
• バッチ系：大きなロールバック・セグメントを数個用意
• 組み合わせ処理：
− すべてのロールバック・セグメントをバッチ処理に耐える大きさに設定
− 長時間実行処理だけ SET TRANSACTION USE ROLLBACK SEGMENT で
大きなロールバックセグメントを指定
同時実行トランザクション数とロールバック・セグメント数
• 4 つのトランザクションあたり 1 つのロールバック・セグメントを用意
鉄則
・１つのトランザクションは、ロールバックセグメントをまたがって使うことはできない。
・ロールバックセグメントの PCTINCREASE はつねにゼロで、変更することはできない。
・ロールバックセグメントの MINEXTENTS の最小値は２。
・ロールバックセグメントは I/O が多く、領域の確保/解放が起こりやすいので専用の表領域を確保する。
・フラグメンテーションを防止するため INITIAL と NEXT のサイズは同じにする。
・エクステントの個数を１０∼３０の範囲に抑えたほうがパフォーマンス上好ましい。
UNLIMITED EXTENTS
・ロールバック・セグメントには MAXEXTENTS UNLIMITED 指定をしない
動的縮小方法
・ALTER ROLLBACK SEGMENT rbs SHRINK コマンドを使用
V$ROLLSTAT を使用したサイズの見積もり方法
• 本番に近いテスト運用で必要サイズを推測
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
33
4. テンポラリー・セグメントの管理
テンポラリー・セグメントは、メモリー上で処理したいデータ量が使用可能なメモリー量を超えたとき
に、そのデータを一時的に格納するためのディスク領域です。そしてこのテンポラリー・セグメントを格
納する表領域を一時表領域といいます。
この領域の設定を誤ると、「ORA-01630:一時セグメントで最大エクステント:num に達しました（表領
域:name）。」や「ORA-01652:一時セグメントを拡張できません（:num 表領域:name）。」などのエラー
が発生する可能性があります。
この章ではこれらの問題を回避するためのテンポラリー・セグメントの設定のポイントを説明します。
4.1. いつ使用されるのか
Oracle 上で以下の処理を行うときにテンポラリー・セグメントが使用される可能性があります。
• ソートが発生するような処理を行ったとき
- DISTINCT、UNION、MINUS、GROUP BY、ORDER BY を含んだ SQL 文
- INDEX 作成（CREATE INDEX 文）
- ソート/マージ･ジョイン
• 表をジョインする SQL 文の実行計画が HASH JOIN を選択したとき
Oracle は上記の処理をメモリー上で実行しようとします。しかし、ソートするデータがメモリー（初
期化パラメータ、SORT_AREA_SIZE で指定したサイズ）に収まりきらない場合、テンポラリー・セグメント
を自動的に作成し、処理が終わると、自動的に削除します。
4.2. 使用する一時表領域の指定
Oracle が使用する一時表領域は、ユーザー毎に指定します。ユーザー作成時に、明示的に一時表領域
として使用する表領域を指定しない場合、SYSTEM 表領域が一時表領域として使われます。SYSTEM 表領域
は、データ・ディクショナリ用の表領域なので、その他の用途に使用するべきではありません。必ず、ユ
ーザーに明示的に使用する一時表領域を指定しましょう。また、複数のユーザーが同時に処理を行う環境
では、一時表領域を複数用意してユーザーレベルで I/O の分散を図ることも、パフォーマンスの向上に効
果があります。
一時表領域として指定できる表領域には、次の３種類の表領域があります。
l
通常の表領域
l
専用一時表領域（R7.3 以降）
l
テンポラリファイルを使った専用一時表領域（R8.1.5 以降）
ユーザーDAVID 作成時に、テンポラリー表領域 TEMPORARY_DATA を割り当てます。
SQL> CREATE USER DAVID IDENTIFIED BY LYNCH
2
DEFAULT TABLESPACE USER_DATA
3 TEMPORARY TABLESPACE TEMPORARY_DATA
4
QUOTA 1M ON USER_DATA;
ユーザーが作成されました。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
34
ユーザーSCOTT の一時表領域を TEMPORARY_DATA に変更します。
SQL> ALTER USER SCOTT
2
TEMPORARY TABLESPACE TEMPORARY_DATA;
ユーザーが変更されました。
テンポラリー・セグメントは、それ専用の表領域を用意してください。表や索引などのオブジェクトが
存在する表領域を指定することもできますが、フラグメンテーションの原因となるので好ましくありませ
ん。
また一時表領域には必ず DEFAULT STORAGE 句を指定します。テンポラリー・セグメントのエクステント
サイズは、一時表領域の DEFAULT STORAGE パラメータに従います。テンポラリー・セグメントは Oracle
によって自動的に作成されるため、テンポラリー・セグメントに対し直接 INITIAL、NEXT といった値を設
定することはできません。
4.3. 専用一時表領域
Oracle7 R7.3 から表領域を一時表領域専用に指定できるようになりました。専用一時表領域の主な特
徴を以下に示します。
• 一時領域（テンポラリー･セグメント）は削除されない
• エクステントは自動的に全て同じ大きさになる
• エクステントの管理は SGA で行われる
一時領域（テンポラリー･セグメント）は削除されない
以前のテンポラリー・セグメントは、自動的に作成、拡張され、処理が終われば、自動的に削除さ
れるものでした。しかし、専用一時表領域に作成されたテンポラリー・セグメントは、ソート処理が
終了して必要なくなっても削除されません。
つまり、テンポラリー・セグメントは一つしかなく、この一つのセグメントをすべてのユーザーで
共有することになります。ただし、一つのエクステントは一人のユーザーが占有します。
これにより、ソート処理毎のセグメント作成、削除にかかるオーバーヘッドがなくなるため、パフ
ォーマンスが向上します。
エクステントはすべて同じ大きさになる
専用一時表領域中のエクステントは、自動的にすべて同じ大きさに割り当てられます。すべてのエ
クステントは記憶領域パラメータの NEXT で指定した大きさになり、
INITIAL と PCTINCREASE は無視さ
れます。
エクステントの管理はSGA で行われる
専用一時表領域中、テンポラリー・セグメントのエクステントの管理は SGA で行われます。エクス
テント数が増加するごとに消費する SGA も増加しますので、エクステントサイズを小さくすると、SGA
を浪費する可能性があります。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
35
次のように専用一時表領域を作成します。
SQL> CREATE TABLESPACE TEMP_DATA
2
DATAFILE 'C:¥ORANT¥DATABASE¥TEMP1ORCL.ORA' SIZE 10M
3
DEFAULT STORAGE ( INITIAL 100K
NEXT 100K
MAXEXTENTS UNLIMITED )
4 TEMPORARY;
表領域が作成されました。
SQL> SELECT TABLESPACE_NAME, CONTENTS FROM DBA_TABLESPACES
2
WHERE TABLESPACE_NAME='TEMP_DATA';
TABLESPACE_NAME
CONTENTS
------------------------------ --------TEMP_DATA
TEMPORARY
一時表領域専用モードの変更は次のように行います。
SQL> ALTER TABLESPACE TEMP_DATA PERMANENT;
表領域が変更されました。（一時表領域専用モードが解除されています。）
SQL> ALTER TABLESPACE TEMP_DATA TEMPORARY;
表領域が変更されました。（一時表領域専用モードに設定されています。）
パフォーマンスを重視する場合は、専用一時表領域の設定をお勧めします。
4.4. デフォルト記憶領域パラメータ
ここでは、一時表領域の最も効果的なデフォルト記憶領域パラメータの設定について説明します。
エクステントの効果的サイズ
Oracle はエクステントをまたがって I/O を行うことができません。ソート領域メモリ・バッファか
らテンポラリー・セグメントへ効果的な I/O をするため、一時表領域のエクステントのサイズを、
SORT_AREA_SIZE よりも大きな値に設定します。
具体的には、セグメント・ヘッダ用の 1 ブロック（DB_BLOCK_SIZE）に SORT_AREA_SIZE の倍数を足し
た値にします。例えば、DB_BLOCK_SIZE が 4KB で、SORT_AREA_SIZE が 64KB のとき、一時表領域のエク
ステントのサイズを、4KB ＋ 64KB ＝ 68KB、または 4KB ＋ 64KB * n （n：整数）に設定します。
エクステントサイズがある程度大きければ、ソート時にエクステントをまたがる回数は少ないので、
このオーバーヘッドをそれほど気にする必要がなくなります。ただし専用一時表領域では、一つのエ
クステントは一人のユーザーが占有します。そのため、あまり大きいエクステントにすると、それだ
け一時表領域が必要になるので、領域管理上無駄が多くなります。1∼10MB 程度を目安と考え、バラン
スを考えたエクステントサイズを指定してください。
INITIAL、NEXT、PCTFREE、MAXEXTENTS
専用一時表領域指定をしていない場合、テンポラリー・セグメントに割り当てるすべてのエクステ
ントの大きさを等しくするために、INITIAL と NEXT を同じ値に、PCTINCREASE を 0 に設定します。こう
することで、一時表領域でのフラグメンデーションを防ぐことができます。
専用一時表領域指定をしたときは INITIAL の値は使われず、初めから NEXT の値が利用され、エクス
テントが割り当てられます。また PCTINCREASE はいつも 0 となり、自動的にすべてのエクステントの大
きさが NEXT の値と等しくなります。
予想外のソート発生量に備えて、MAXEXTENTS は UNLIMITED を指定しておきましょう。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
36
必要サイズ
同時にソートされる可能性のある最大データ量（最大表サイズ）の 2 倍の大きさを目安としてくださ
い。
以上を次にまとめます。
INITIAL ＝ NEXT ＝ DB_BLOCK_SIZE ＋ n * SORT_AREA_SIZE（n：整数）
• 1∼10MB 程度がサイズの目安
• 専用一時表領域では、NEXT の値だけが使用される
• 専用一時表領域では、一つのエクステントは一人のユーザーが占有
PCTFREE ＝ 0
• 専用一時表領域では、指定した値は無視され、自動的に 0
必要サイズ ＝ 最大表サイズ * 2
MAXEXTENTS ＝ UNLIMITED
エクステントサイズ 1MB の一時表領域 TEMP を次のように作成します。
SQL> CREATE TABLESPACE TEMP
2
DATAFILE 'C:¥ORACLE¥ORADATA¥ORCL¥TEMP1ORCL.DBF' SIZE 50M
3
DEFAULT STORAGE ( INITIAL 1M
4
TEMPORARY;
NEXT 1M
MAXEXTENTS UNLIMITED )
表領域が作成されました。
4.5.テンポラリファイルを使った専用一時表領域
テンポラリファイルとは、ログを生成しないデータファイルのことです。ソート等のために一時的にデ
ータを格納する目的で使用される一時表領域では、リカバリなどの必要がないため、本来、REDO ログを
とる必要はありません。Oracle 8i では、テンポラリ・ファイルで構成される、REDO ログを生成しない専
用一時表領域を作成できます。
テンポラリファイルを使った専用一時表領域は、実際のところ、ローカル管理表領域を使った専用一時
表領域のことです。ただしローカル管理表領域に対し temporary 属性を指定することはできないので、ロ
ーカル管理表領域を専用一時表領域として使うためには、このテンポラリファイルを使った専用一時表領
域を使用します。
テンポラリファイルを使用した専用一時表領域は、通常のデータファイルを使用した専用一時表領域と
次の点で異なります。
l
常に NOLLOGING モードにセットされます
l
オフラインや読み取り専用の指定はできません。
l
改名はできません。
l
MINIMUM EXTENT や DEFAULT STORAGE の設定はできません。
l
メディア回復の対象となりません。
この機能によって、Oracle8i 以降では２つの専用一時表領域がサポートされることになります。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
37
・テンポラリファイルを使用した専用一時表領域（R8.1.5 以降）
テンポラリファイルを使用した専用一時表領域を作成するためには、CREATE TEMPORARY TABLESPACE 文
を使用します。CREATE TEMPORARY TABLESPACE 文では、DATAFILES 句の代わりに TEMPFILES 句を使用しま
す。EXTENT MANAGEMENT LOCAL は省略可能です。構文は次のようになっています。
CREATE TEMPORARY TABLESPACE 表領域名 TEMPFILE
‘ファイル名’ SIZE サイズ[K￨M]
EXTENT MANAGEMENT LOCAL UNIFORM SIZE サイズ［K￨M］
実際に作成してみましょう。
SQL>CREATE TEMPORARY TABLESPACE
TEMP_FILE TEMPFILE
2
‘C:¥ORACLE¥ORADATA¥ORCL¥TEMPF1ORCL.DBF’ SIZE 50M
3
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;
表領域が作成されました。
データディクショナリを見て、通常の表領域との違いを確認します。
SQL>SELECT TABLESPACE_NAME, CONTENTS, LOGGING,
2
EXTENT_MANAGEMENT FROM DBA_TABLESPACES
3
WHERE TABLESPACE_NAME LIKE ('TEMP%');
TABLESPACE_NAME
CONTENTS
LOGGING
EXTENT_MAN
------------------------------ --------- --------- ---------TEMP
TEMPORARY
LOGGING
TEMP_FILE
TEMPORARY
NOLOGGING LOCAL
DICTIONARY
通常のデータファイルを使用した専用一時表領域と、テンポラリファイルを使用した専用一時表領域
を区別するには、DBA_TABLESPACES の CONTENTS カラムと EXTENT_MANAGEMENT カラムを使用します。どち
らの表領域も、CONTENTS カラムの値は「TEMPORARY」になっていますが、テンポラリファイルを使用した
専用一時表領域の EXTENT_MANAGEMENT の値は「LOCAL」になっています。次の表は、表領域の種類による
データディクショナリの値の違いです。
表領域の種類
CONTENTS の値
EXTENT_MANAGEMENT の値
専用一時表領域
TEMPORARY
DICTIONARY
テンポラリファイルを使用した
TEMPORARY
LOCAL
PERMANENT
LOCAL
専用一時表領域
ローカル管理表領域
テンポラリファイルに関する情報は、V$DATAFILE などの従来のデータディクショナリには表示されま
せん。新たに次のデータディクショナリビューが追加されています。
DBA_TEMP_FILES 、 V$TEMPFILE 、 V$TEMP_EXTENT_POOL 、 V$TEMP_PING 、 V$TEMP_EXTENT_MAP 、
V$TEMP_SPACE_HEADER
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
38
4.6.初期データベースをカスタマイズする
この節では、ここまで説明したことをもとにして初期データベースをカスタマイズします。初期デー
タベースには次のような表領域がテンポラリー・セグメント用に作成されています。
表領域名
:TEMPORARY_DATA
INITIAL
:10K
NEXT
:10K
MINEXTENTS
:1
MAXEXTENTS
:121
PCTINCREASE
:50
表領域のサイズ
:2MB
この設定では INITIAL、NEXT、表領域のサイズが小さいため、大きな表に索引を作成する場合などに、
一時領域不足によるエラーが発生する可能性があります。
また PCTINCREASE が 0 でないため、異なる大きさのエクステントが割り当てられることになり、フラグ
メントの原因となる可能性があります。
例えば、データベースに格納されている表の最大サイズが 10MB 程度であったとします。この表に対し
て必要なテンポラリー・セグメントの大きさは 20MB（10MB * 2）程度と推測できます。そこで次のよう
なパラメータを持つ専用一時表領域を実際に作成する方法を解説します。この専用一時表領域であれば最
大 100M バイトまでのソート処理に耐えられます。
INITIAL
:DB_BLOCK_SIZE (2KB) + SORT_AREA_SIZE (1024KB) = 1026KB (指定不要)
NEXT
:INITIAL = 1026KB
MINEXTENTS
:2
MAXEXTENTS
:UNLIMITED
PCTINCREASE
:0 (指定不要)
表領域サイズ
:50MB (AUTOEXTEND 100MB)
１．SORT_AREA_SIZE のデフォルト値は 64K です。パフォーマンスを向上させるために、もっと大きな
サイズに設定します。今回は SORT_AREA_SIZE を 1024KB（1MB）に設定します。init.ora に次の一
行を加えて Oracle を再起動します。
SORT_AREA_SIZE = 1048576
起動後、サーバーマネージャから次のコマンドを実行すれば、sort_area_size の設定値を確認で
きます。
SVRMGR> SHOW PARAMETERS SORT_AREA_SIZE
NAME
TYPE
VALUE
----------------------------------- ------- -----------------------------sort_area_size
整数値
1048576
２.SQL*Plus もしくはサーバーマネージャから、データベース管理者ユーザーで接続します。
３.既存の小さな表領域を削除します。
SQL> DROP TABLESPACE TEMPORARY_DATA;
表領域が削除されました。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
39
４.新しい設定の専用一時表領域を作成します。
SQL> CREATE TABLESPACE TEMPORARY_DATA
2
DATAFILE 'C:¥ORANT¥DATABASE¥TEMP1ORCL.ORA' SIZE 50M REUSE
3
AUTOEXTEND ON NEXT 1026K MAXSIZE 100M
4
DEFAULT STORAGE ( INITIAL 1026K NEXT 1026K MINEXTENTS 2 MAXEXTENTS UNLIMITED )
5
TEMPORARY;
表領域が作成されました。
５.専用一時表領域の設定を確認します。
SQL> SELECT * FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='TEMPORARY_DATA';
TABLESPACE_NAME
INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
------------------------------ -------------- ----------- ----------- ----------- -----------MIN_EXTLEN STATUS
CONTENTS
LOGGING
---------- --------- --------- --------TEMPORARY_DATA
0 ONLINE
1050624
1050624
2
2.147E+09
0
TEMPORARY LOGGING
６.次のコマンドを実行し、ユーザーの一時表領域を明示的に指定します。
ALTER USER ユーザー名 TEMPORARY TABLESPACE 一時表領域名;
今回は、ユーザーSCOTT の一時表領域に TEMPORAY_DATA を指定します。
SQL> ALTER USER SCOTT TEMPORARY TABLESPACE TEMPORARY_DATA;
ユーザーが変更されました。
4.7. 使用の確認方法
ここではソート処理のディスク使用頻度とサイズを確認する以下の方法を説明します。
• V$SYSSTAT による使用頻度の確認手順
• V$SORT_SEGMENT による使用サイズの確認手順
良いパフォーマンスを得るため、CREATE INDEX など全件ソートするような大量のデータを処理する場
合を除いて、できるだけメモリー上で処理が終わるように、SORT_AREA_SIZE を設定します。
ソート処理がディスクへの書き出しを行っているかどうかは、BSTAT/ESTAT（「第 5.4 節 REDO ログ･フ
ァイルのサイズ見積もり」参照）を利用するか、V$SYSSTAT ビューを参照して確認します。
また専用一時表領域の場合、V$SORT_SEGMENT ビューを参照すれば、ソート処理がディスク上に必要と
したテンポラリー・セグメントのサイズを知ることができます。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
40
V$SYSSTAT による使用頻度の確認手順
1. まず、インスタンス起動時からのディスクでのソート回数の統計値を確認します。
SQL> SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME='sorts (disk)';
NAME
VALUE
---------------------------------------------------------------- --------sorts (disk)
44
2. 確認したい処理を実行します。
3. 再度、インスタンス起動時からのディスクでのソート回数の統計値を確認します。
SQL> SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME='sorts (disk)';
NAME
VALUE
---------------------------------------------------------------- --------sorts (disk)
49
4. ステップ 3 で得た値（VALUE）からステップ 1 で得た値（VALUE）を引いた数が、ディスクに対
して発生した書き込み回数になります。この値が 0 になれば、実行した処理のソートはメモリー上
だけで行われたことになります。
ディスクへの書き込み回数 ＝ ステップ 3 の VALUE − ステップ 1 の VALUE
今回は 5（= 49 - 44）回のディスクへの書き込みが起きていることがわかります。
V$SORT_SEGMENT による使用サイズの確認手順
(注意)この方法が行えるのは専用一時表領域だけです。
1. Oracle を起動します。
2. 確認したい処理を実行します。ここでは、sort_test 表にインデックスを作成し、select order
by を実行します。
SQL> CREATE INDEX PK_SORT_TEST ON SORT_TEST(COL1);
索引が作成されました。
SQL> SELECT * FROM SORT_TEST ORDER BY COL1;
（実行結果）
3. 次のコマンドを実行して、専用一時表領域にテスト運用時に必要とされたテンポラリー領域の
サイズを求めます。次の列を参照します。
TABLESPACE_NAME:
専用一時表領域の名前
MAX_SORT_SIZE:
個々のソートで使用されたエクステントの最大数
MAX_SORT_BLOCKS:
個々のソートで使用されたブロックの最大数
ここでは、TEMPORARY_DATA が専用一時表領域です。
SQL> SELECT TABLESPACE_NAME, MAX_SORT_SIZE, MAX_SORT_BLOCKS
2
FROM V$SORT_SEGMENT WHERE TABLESPACE_NAME = 'TEMPORARY_DATA';
TABLESPACE_NAME
MAX_SORT_SIZE MAX_SORT_BLOCKS
------------------------------- ------------- --------------TEMPORARY_DATA
9
4635
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
41
この結果より、ステップ２で発生したソート処理が使用した専用一時領域の最大エクステント
数は９、最大ブロック数は 4635、すなわち 4635 * 2048 ＝ 9492480 (Byte)だとわかります。
この結果と、本稼動における次の項目を考慮して本番で必要なソート領域を見積もってくださ
い。
• データの増加量（データが増えれば、必要なソート領域も増加します）
• ソート処理の同時実行ユーザー数（各ユーザー毎にエクステントが確保されます）
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
42
テンポラリー・セグメントの管理のまとめ
テンポラリー・セグメントが使用されるとき
• データがメモリー（SORT_AREA_SIZE）に収まりきらないとき
− ソートが発生するような処理を行ったとき
− SQL 文の実行計画が HASH JOIN を選択したとき
使用する一時表領域の指定
• 必ず、ユーザー毎に、明示的に使用する一時表領域を指定
• テンポラリー・セグメントはそれ専用の一時表領域に格納
専用一時表領域
• Oracle7 R7.3 から表領域を一時表領域専用に指定可能
• 一時領域（テンポラリー･セグメント）は削除されないのでパフォーマンス向上
• エクステントは自動的にすべて同じ大きさになる
• エクステントの管理は SGA で行われる
テンポラリファイルを使用した専用一時表領域
• Oracle8i R8.1.5 から指定可能
• 内部的にはローカル管理の表領域を使用
一時表領域のデフォルト記憶領域パラメータ
• INITIAL ＝ NEXT ＝ DB_BLOCK_SIZE ＋ n ＊ SORT_AREA_SIZE（n：整数）
−
1∼10MB 程度が目安
−
専用一時表領域では、NEXT の値だけが利用される
−
専用一時表領域では、一つのエクステントは一人のユーザーが占有
• PCTINCREASE ＝ 0
−
専用一時表領域では、自動的に PCTINCREASE ＝ 0 となる
• 必要サイズは最大表サイズの 2 倍程度
一時表領域使用の確認方法
• BSTAT/ESTAT または V$SYSSTAT ビューで使用頻度を確認
• V$SORT_SEGMENT ビューで使用サイズを確認（専用一時表領域のとき）
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
43
5. REDO ログ・ファイルの管理
REDO ログ･ファイルは、データベースへの変更を記録したファイルで、障害からの回復に使用されます。
Oracle は最低 2 つの REDO ログ・ファイルを必要とし、複数の REDO ログ・ファイルを循環的に使用しま
す。
REDO ログ･ファイルの設定次第では、良いパフォーマンスが得られなかったり、また障害回復に時間が
かかる、といった問題が発生します。この章では、これらの問題を回避する REDO ログ・ファイル管理の
ポイントを説明します。
5.1. サイズを考える
REDO ログ・ファイルのサイズはチェック・ポイントの頻度を決定する要因の一つです。不用意な設定
はパフォーマンス、または障害回復に悪影響を与えることもあります。ここでの説明を参考にして、シス
テムに応じた適切なサイズを設定してください。
チェックポイントとパフォーマンス
チェック・ポイントとは修正されたすべてのデータベース・バッファ・キャッシュの内容を DBWR（デ
ータベース･ライター）がデータ･ファイルに書き込むイベントのことです。チェック･ポイントは、デ
ータ・ブロックのリスト作成やディスク I/O を伴うため、あまり頻繁に発生するとパフォーマンスに悪
影響を与えます。少なくとも、一つのトランザクション中にログの切り替えは起こらないことが望ま
しいでしょう。実行時のパフォーマンスを優先するならば、チェック・ポイントの頻度を低くしてく
ださい。しかし、実行時のパフォーマンスよりも迅速な回復を優先するとしたら、チェック・ポイン
トの頻度を高くしてください。
ログの切り替え時には、必ずチェック・ポイントが発生します。REDO ログ・ファイルのサイズが小
さければ、ファイルはすぐいっぱいになり、ログの切り替えが頻繁に発生します。しかしサイズが大
きければその頻度は少なくなります。
このことをまとめると次のようになります。
REDO ログ・ファイルのサイズ
チェック・ポイントの頻度
パフォーマンス
リカバリ時間
小
高
悪
小
大
低
良
大
一般に稼働中のデータベースのパフォーマンスは、まれにしか起こらないデータベースのリカバリ
にかかる時間より考慮すべき重要な点となります。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
44
一般的サイズ
5MB ずつ、3 個の REDO ログなら、ほとんどのシステムでうまく動くと思われます。次の表の一般的
サイズを参考にしてシステムの規模にあった REDO ログ・ファイルのサイズを設定してください。
システムの規模
REDO ログ・ファイルの一般的サイズ
大
10∼20MB
中
5∼10MB
小
1∼5MB
まず、5MB ずつ、3 個の REDO ログ・ファイルを用意してシステム運用してみてください。それでも、
パフォーマンスに不満があれば、サイズを大きくすることを検討してください。REDO ログ・ファイル・
サイズの変更方法については第 5.3.節を参考にしてください。
5.2. 配置を考える
パフォーマンス向上、および障害対策のため、つぎのポイントを考慮して REDO ログ･ファイルを配置し
てください。
• データ･ファイルとのディスク競合の回避
• 多重化メンバーへの並列書き込み
REDO ログ・ファイルはデータファイルと同様に Oracle により頻繁にアクセスされます。これらのファ
イルが同じディスク上に存在している場合、ディスクの競合が発生する可能性があります。ディスクの競
合を回避し、LGWR の順次書き込みをより高速にするために、REDO ログ･ファイルに専用のディスクを用い
ることをお勧めします。
また、REDO ログ･ファイルが多重化（ミラー化）されている場合には、グループにおける各メンバーを、
別々のディスクに配置することをお勧めします。LGWR が別々のディスク上に存在している個々のメンバ
ーに対して、並列に書き込みを行えるため、書き込み時間を短縮することができるからです。これはディ
スク障害対策としても有効です。
REDO ログ･ファイルは Oracle でのミラーリングが可能ですが、パフォーマンスを重視する場合は RAID1、
または RAID1+0 構成にするのも良いでしょう。RAID5 はパフォーマンスに悪影響を与える可能性があるの
でお勧めできません。
グループ1
グループ2
グループ3
メンバー A
LGWR
ディスクα
メンバー B
ディスクβ
図 5.1. REDO ログ・ファイルの配置
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
45
5.3.初期データベースをカスタマイズする
この節では、ここまで説明したことをもとにして初期データベースをカスタマイズします。
初期データベースには 2 個（グループ）の REDO ログ・ファイルがあります。この REDO ログ・ファイル
のサイズはともに 200K バイトと小さく、実際の運用ではチェックポイントが頻繁に発生し、パフォーマ
ンスに悪影響を与える可能性があります。
そこで次のような REDO ログ・ファイルを実際に作成し、初期データベースの REDO ログ・ファイルを変
更する方法を解説します。この REDO ログ・ファイルであればほとんどのシステムでうまく動くと考えら
れますが、必要であればさらに追加・変更を行ってください。
サイズ
：5MB
個数
：3 個
１．サーバーマネージャを起動し、INTERNAL で接続します。
２．Oracle を MOUNT モードで起動します。
SVRMGR> STARTUP MOUNT
Oracle インスタンスが起動しました。
...
データベースがマウントされました。
３．5MB の REDO ログ・ファイルを 3 個（グループ）追加します。
SVRMGR> ALTER DATABASE ADD LOGFILE
2> GROUP 3 ('C:¥ORANT¥DATABASE¥LOG3ORCL.ORA') SIZE 5M,
3> GROUP 4 ('C:¥ORANT¥DATABASE¥LOG4ORCL.ORA') SIZE 5M,
4> GROUP 5 ('C:¥ORANT¥DATABASE¥LOG5ORCL.ORA') SIZE 5M;
文が処理されました。
４．データベースを OPEN します。
SVRMGR> ALTER DATABASE OPEN;
文が処理されました。
５．他ユーザーのアクセスを制限します。
SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION;
文が処理されました。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
46
６．現在使用中の REDO ログ・ファイルを確認します。
SVRMGR> SELECT GROUP#, BYTES, MEMBERS, STATUS FROM V$LOG;
GROUP#
BYTES
MEMBERS
STATUS
---------- ---------- ---------- ---------------1
204800
1 CURRENT
2
204800
1 INACTIVE
3
5242880
1 UNUSED
4
5242880
1 UNUSED
5
5242880
1 UNUSED
5 行選択されました。
７．削除したい REDO ログ・ファイルが使用中（CURRENT）なので、ログを切り替えます。新しく追加し
た REDO ログ・ファイルが CURRENT になるまでこれを繰り返します。
SVRMGR> ALTER SYSTEM SWITCH LOGFILE;
文が処理されました。
８．CURRENT が追加する REDO ログ・ファイルに切り替わったことを確認します。
SVRMGR> SELECT GROUP#, BYTES, MEMBERS, STATUS FROM V$LOG;
GROUP#
BYTES
MEMBERS
STATUS
---------- ---------- ---------- ---------------1
204800
1 INACTIVE
2
204800
1 INACTIVE
3
5242880
1 CURRENT
4
5242880
1 UNUSED
5
5242880
1 UNUSED
5 行選択されました。
9．不要な REDO ログ・ファイルを削除します。
SVRMGR> ALTER DATABASE DROP LOGFILE GROUP 1, GROUP 2;
文が処理されました。
１０．再び現在の REDO ログ・ファイルの状態を確認します。このように表示されていれば終了です。
SVRMGR> SELECT GROUP#, BYTES, MEMBERS, STATUS FROM V$LOG;
GROUP#
BYTES
MEMBERS
STATUS
---------- ---------- ---------- ---------------3
5242880
1 CURRENT
4
5242880
1 UNUSED
5
5242880
1 UNUSED
3 行選択されました。
１１．他ユーザーのアクセス制限を解除します。
SVRMGR> ALTER SYSTEM DISABLE RESTRICTED SESSION;
文が処理されました。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
47
１２．最後に、削除した REDO ログ・ファイルを OS レベルでも削除しておきます。
c:¥>del C:¥ORANT¥DATABASE¥LOG1ORCL.ORA
Deleting C:¥ORANT¥DATABASE¥LOG1ORCL.ORA
1 file deleted
229,376 bytes freed
c:¥>del C:¥ORANT¥DATABASE¥LOG2ORCL.ORA
Deleting C:¥ORANT¥DATABASE¥LOG2ORCL.ORA
1 file deleted
229,376 bytes freed
5.4. サイズの見積もり方法
ここではテスト運用を実施して本番用の REDO ログ・ファイルに必要なサイズを推測する方法を説明し
ます。
V$SYSSTAT を使用した見積もり法
テスト運用を実施して動的パフォーマンス表 V$SYSSTAT より得た値から本番用の REDO ログ・ファイ
ルに必要なサイズを推測することができます。以下にその手順を示します。
1. あらかじめ適当な大きさの REDO ログ・ファイルを作成しておきます。
Oracle インストール時に NT に作成される初期データベースにはサイズ 200KB の REDO ログ・ファ
イルが 2 個作成されています。
2. Oracle を起動します。
3. テスト運用を実施します。
このテスト運用が本番での処理に近いほど見積もりの精度が高まります。
4. テスト運用終了後、動的パフォーマンス表 V$SYSSTAT の redo size を検索します。
redo size はデータベースが起動されてから現在に至るまでの REDO エントリのサイズです。この
値をもとに REDO ログ・ファイルのサイズを設定します。
SQL> SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME='redo size';
NAME
VALUE
---------------------------------------------------------------- --------redo size
28501348
この例では、テスト運用での REDO エントリのサイズが 28501348B ≒ 27MB だとわかります。
この他に、パフォーマンスをモニタするための BSTAT/ESTAT と呼ばれるスクリプトを使用し、テス
ト運用を実施して本番用の REDO ロ グ・ファイルに必要なサイズを推測する方法があります。
BSTAT/ESTAT についての詳細は『ORACLE USER VOL.7』93 ページの「Oracle7 DATABASE PERFORMANCE TUNING
UTLBSTAT/UTLESTAT のご紹介」または、技術資料「実行計画とトレースの取得方法」を参考にしてくだ
さい。
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
48
REDO ログ・ファイルの管理のまとめ
サイズについて
• サイズとチェックポイントの頻度、パフォーマンス、リカバリ時間を考慮
• 5MB ずつ、3 個の REDO ログが目安
配置について
• データファイルとは別の専用のディスク使用を推奨
• 多重化メンバーも別ディスクに配置
• パフォーマンス重視には RAID1、RAID0+1
サイズの見積もり方法
• テスト運用から V$SYSSTAT または BSTAT/ESTAT を使用し必要サイズを推測
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
49
おわりに
本書では、次に挙げる各領域についての設定・管理のポイントを解説しました。
• 表と索引
• 表領域
• ロールバック・セグメント
• テンポラリー・セグメント
• REDO ログ・ファイル
また Oracle インストール時に作成できる初期データベースのロールバック・セグメント、テンポラリ
ー・セグメント、REDO ログ・ファイルをカスタマイズする方法を解説しました。
本書で解説した、それぞれの領域管理のポイントはトラブルを未然に防ぐことに重点をおいたもので
す。本書ではあまり詳しく触れない点についてお知りになりたい方は、ORACLE のマニュアル、または、
本文中に紹介した技術資料を参考にしてください。
本書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。こ
の文章に関連して不都合が生じた場合も、米国オラクル社および日本オラクル株式会社は一切保証せず、
特に責任は負いかねますのでご容赦ください。また許可なく、改編、引用することを禁じます。
1998 年 10 月 初版
1999 年 1 月 改訂版
専用一時表領域に関する情報を追加
1999 年 2 月 改訂 2 版
連鎖行の調査方法、データファイルサイズに関する注意、
MINIMUM EXTENT パラメータに関する情報等を追加
2000 年 2 月 改訂 3 版
ローカル管理表領域、テンポラリファイルを使った一時表領域、
データファイルの自動拡張に関する注意事項に関する記述を追加
日本オラクル株式会社 Design & Migration Services
Copyright© ORACLE CORPORATION JAPAN 1999,2000
領域管理∼トラブル防止のテクニック∼
Design & Migration Services
50