Oracle の領域管理
∼トラブル防止のテクニック∼
THE
Database
目次
はじめに... 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.T
ABLESPACEC
OALESCEについて ... 16
2.3.D
EALLOCATEU
NUSEDS
PACEについて... 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
5.REDO ログ・ファイルの管理... 44
5.1.サイズを考える ... 44
5.2.配置を考える... 45
5.3.初期データベースをカスタマイズする... 46
5.4.サイズの見積もり方法... 48
REDO ログ・ファイルの管理のまとめ... 49
おわりに... 50
はじめに
データベースのトラブルはどうして起こるのでしょうか。その原因はさまざまです。 例えば、領域に関するトラブルがあります。「領域を確保できない」というエラーはその代表的なも のでしょう。しかし、この原因は単に「データ量の見積もりの甘さ」による「表領域のサイズ不足」で片 付けられないことが多いのです。実際のところ、これらのトラブルの多くは Oracle の領域管理の仕組み を知っていれば未然に防げたはずです。また、データベースのパフォーマンス悪化が領域の設定に起因し ていることもあります。 本書の目的は、領域の設定・管理に起因するトラブルを回避するポイントを解説することです。トラ ブルが発生したあとで、どのように対処するのかではなく、トラブルをできるだけ未然に防ぐ方法を解説 します。 まずデータベースの領域について簡単にまとめてみましょう。 Oracle の世界（論理的） でのセグメント 表 索引 ロールバッ ク・セグメント テンポラリ・セ グメント Oracle の世界（論理的） でのファイル 表領域（TABLESPACE）群 REDO ログ・フ ァイル群 OS 上（物理的）のファイ ル データファイル群 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はオラクル社の登録商標です。
ø その他のすべての企業名と商品名は各社の登録商標または商標、製品名です。 ø なお、本文中では 、 は明記していません。
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 種類のセグメント（表、索引、ロールバック、テンポラリ）全般に対しても表領域の分割を考慮しな ければいけません。各セグメントの章の中でも触れていますので、確認してください。
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 エクステント・サイズの増加率(%) これらの設定次第では運用途中に重大な障害が発生する可能性があります。 これらのパラメータの詳細な意味は本書では解説しません。ここではこれらのパラメータを設定する 指針を解説します。これらの 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 を設定して、不必要なエクステントの数の増加は
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 表が作成されました。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
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
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 更新は全くないので、読み込むブロック数を最小にする。参考までに Oracle Enterprise Manager（OEM）による PCTREE と PCTUSED の設定は以下のようになりま す。 多い（増加行サイズの更新） PCTFREE：20 多い PCTFREE：10 更新アクティビティ 少ない PCTFREE：5 多い（削除を含む） PCTUSED：60 多い PCTUSED：40 挿入アクティビティ 少ない PCTUSED：60
表と索引の管理のまとめ
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）を指定する
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; データベースが変更されました。 (注意)ファイルサイズを縮小する場合、必ず指定したサイズにまでなるとは限りません。既に格納され ているオブジェクトのサイズや格納位置が影響するからです。
データファイル・サイズの自動拡張を作成時に設定する方法
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;
データファイルの自動拡張設定を確認する方法
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 バランスと管理上のメリットの方が大きい でしょう。自動拡張のクセに注意する
自動拡張は便利な機能ですが、注意すべき点もあります。次のようなシナリオを考えてください。表 領域 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エクステント2（20MB） 表Aエクステント1（20MB） 表Bエクステント1（15MB） 結合された空領域（30MB） 表Bを削除 COALESCE データ・ファイル 表Bエクステント2（15MB） 表Aエクステント2（20MB） 表Aエクステント1（20MB） エクステントの残骸（15MB） データ・ファイル エクステントの残骸（15MB） 表Aエクステント2（20MB） 表Aエクステント1（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 表領域に空き領域の結合処理を行うとき、以下のコマンドを実行します。2.3. Deallocate Unused Space について
予想したデータ量をもとに巨大なエクステントを確保したのに、実際のデータ量は少なかった、とい う場合があります。ところが、一度確保したエクステントは DROP または TRUNCATE コマンドによってしか 開放することはできません。確保された巨大なエクステントはそのほとんどにデータを格納することなく、 他の誰も使えない領域を無駄に占有することになるのです。例えば、図 2.2.の状態①です。50MB のエク ステントを確保したにもかかわらず、それ以上増えることもないデータは 10MB 程しか格納されていませ ん。40MB 分の領域が無駄に使用されているのです。 エクステント（50MB） データ・ファイル 高水位標 DEALLOCATE 空き領域（10MB） 未使用領域（40MB） エクステント（10MB） データ・ファイル 高水位標 開放された領域（40MB） 空き領域（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;
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参照）。 1.5MB 2MB 1MB 1.6MB 1.4MB 1.3MB 0.8MB 2MB 1MB 1MB 1MB 2MB 2MB
状態①
状態②
1MBMINIMUM EXTENT指定無し
MINIMUM EXTENT 1M 指定
0.8MB 1MB 2MB 1MB 1MB 1MB 図 2.3. MINIMUM EXTENT パラメータ指定 状態①： MINIMUM EXTENT は指定されていないある表領域の状態です。様々なサイズのエクステン トが割り当てられ、断片化が発生しています。 状態②： MINIMUM EXTENT 1M と指定されている表領域です。すべてのエクステントのサイズは 1MB の倍数になっており、断片化は発生しにくくなっています。 表に指定されたエクステント・サイズと、例えばMINIMUM EXTENT 1Mと指定された表領域に実際に割 り当てられるエクステント・サイズの関係を以下に示します。 表に指定したエクステント・サイズ 実際に割り当てられるエクステント・サイズ 500KB 1MB 1.5MB 2MB
実際に表領域を作成し、割り当てられるエクステントのサイズを確認してみましょう
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);
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 の空き エクステントを使用することができませんでした。しかしローカル管理の表領域では、連続した空き領 域と認識できるため、このようなことは発生しません 今後両者を明確に区別したいときは、従来の表領域のことをディクショナリ管理の表領域と記述しま す。ローカル管理表領域を作成する
ローカル管理表領域を作成するために、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表領域名 DATAFILE ‘ファイルのディレクトリ’SIZE
サイズ[K|Ｍ] 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
表領域の管理のまとめ
表領域の拡張
データファイルの追加・拡張方法：
• 手動追加法 ：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から新しく導入された表領域。 • 表領域内に作成したビットマップでエクステントを管理するため、効率的な領域管理ができる。
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：スナップショットが古すぎます」が発生する可能性があります。バッチ系
大量データの更新処理では、大きなロールバックセグメントを用意します。領域不足によるエラーを 回避するとともに、あらかじめ十分な領域を確保することによってパフォーマンスを向上させます。組み合わせ処理
小さな 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 以下）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 一つのロールバックセグメントの最大サイズ ×ロールバックセグメントの個数バッチ処理
バッチ処理ではエクステントサイズが大きい方が好ましいので 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 を指定することによって、ロールバッ クセグメントを任意のサイズまで縮小することができます。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２．ロールバックセグメント用の表領域のサイズを求めます。最悪のケースも考えて、１つのロール バックセグメントが最大エクステント数まで拡張しても平気なようにします。 (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
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;
８．はじめから作成してあるロールバックセグメントを削除します。
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)
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.
テスト運用を実施します。 このテスト運用が本番での処理に近いほど見積もりの精度が高まります。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)
