大きなトランザクションや大量データのインポート時などに、ロールバック・セグメントの領域が不足して、"ORA-1562: ロールバック・セグメントを拡張できませんでした(ID=xx)" というエラーで失敗してしまうときがある。そのトランザクションを途中でのコミットなしに終了させるには、十分な大きさのロールバック・セグメントを新たに作成しなければならない。このような場面で必要なロールバック・セグメントの大きさを見積もるための手法について示す。
5.4.1.ロールバックセグメントの拡張方法
ロールバック・セグメント（以下RBS）のエクステントは循環的に使用されます。すなわちロールバックエントリ(以下 undo)に使用されていた領域は、そのトランザクションがコミットされると再使用可能になります。もし、現在使用中のエクステントが一杯になり、次のエクステントが他のトランザクションで使用中の場合は、ロールバック・セグメントが拡張されます。
ORACLEにおいて、RBSは非常に重要な役割を果たしています。具体的には次の通りです。
① 処理の取り消し
② 読み取り一貫性処理
③ リカバリ
①は、INSERT/DELETE/UPDATE処理時の取り消し（ROLLBACK）を行うために機能します。処理の決定COMMIT/ROLLBACKをおこなうことで確保していたRBSが開放されます。②についてはある問い合わせで参照されるデータがすべて同一時点のデータになるようにRBSを使用しています。③はデータベースがクラッシュしたときに使用されます。
RBSは、実際のロールバック情報を記憶する部分とトランザクションテーブル＊１と呼ばれるデータ域を管理する部分から構成されます。RBSにセーブされる情報は必ずエクステントの先頭からセーブされることはありません。これは、トランザクションテーブルの管理単位がエクステント単位ではなくスロット＊２という単位で管理されているからであり、これによってデータ占有率を高めています。RBSは、ロールバックセグメント表領域のサイズおよびMAXEXTENTSに達するまで拡張を次のように行います。
Oracleではロールバック・セグメントの最大エクステント数はオラクルブロックサイズによって決まります。例えば 2048バイトのオラクルブロックにしている場合、最大エクステント数は 121です。
ロールバック・セグメントのエクステント数の制限を越えてしまうことによるエラー(すなわち ORA-1562や ORA-1547)によるトランザクションの失敗を避けるためには、エクステントの数をもっと少なくしなければいけません。大きなトランザクションが必要とするロールバック・セグメントの大きさを見積もることによって、エクステント数の制限を越えることなく、逆に無駄な領域をつくることなくロールバック・セグメントを作成することが出来ます。
RBSエクステント拡張
図中において、①・②・③はエクステント、T１は１トランザクション量を示しています。図においてパターン１及びパターン２共にT1トランザクションはエクステント①からスタートしていますが、パターン２の場合は、エクステント①の半分より後半部分のスロットからロールバック情報がセーブされているため、エクステント③までではロールバック情報が収まり切れず、新規にエクステント④をアロケーションしてしまいます。よって、エクステントの拡張は自分がスタートしたスロットが存在するエクステントでサイクリックに使い切ってしまったタイミングになります。
最初に確保されたエクステント内に作成されます。
ORACLEブロックに相当します。
5.4.2.ロールバックセグメントサイズ見積り手法
undoが必要とするバイト数(ロールバックに必要なバイト数)は以下の２点の事柄に依存しています。
トランザクションのタイプ(insert, update, delete, etc.)
実際のデータ
一般的に、あるデータを insertするときより、同じデータを deleteするときに生成される undoの方が大きくなります。何故なら insertをロールバックするときにはレコードを削除するだけで良いのですが、deleteをロールバックするには再びレコードをインサートしないといけないからです(実際のレコードを記録するのと rowidだけを記録するのでは、rowidだけの方が少ない場合がほとんどでしょう)。図1.1はテストテーブルに同じ数の行を insertと deleteしたときに使用される undoの大きさを示したものです。
図1.1 insertとdeleteが使用するUNDO (byte) # of Rows Undo for Insert Undo for Deletens(same table)
--------- -------------- -----------------------------
1000 61946 114290
10000 621456 1143029
また、行の一部のカラムを updateするときと、行の全部を updateするときに使用される undoの大きさは、updateするカラムの行全体に対するデータ量と比例するわけではありません。何故ならカラムを特定することによる新たなオーバーヘッドがかかるからです。例えば、図1.2は 全部で8カラムあるテーブルに対して、そのうち3カラムだけを updateした場合と、8カラム全てを updateした場合の undoの使用する大きさを示していますが、相対的に 3カラムだけの updateの方が使用する undoが大きくなります。
図1.2 updateが使用するUNDO (byte)
# of Rows Update of 3 Columns Update of 8 Columns(same table)
--------- ------------------- -------------------------------
1000 81192 101192
10000 821472 1014383
上記のように生成される undoの大きさは状況によって大きく変化するため、どんな SQL文に対しても適用できる undoのバイト数を計算する公式はありません。データベース管理者は、ある特定の SQL文に対して実際にどれだけ undoが生成されたかを見ることしか出来ないのです。
大きなトランザクションがどの位の大きさの undoを使用するかを推測するためには、オリジナルのテーブルと同じ構造を持ち、行数の少ないテストテーブルに対してトランザクションを行った結果を元に、オリジナルテーブルのundoの使用量を推定するのが最適です。図1.1, 1.2を見ると、行数と undoの大きさはほぼ比例していることがわかります。
例えば、オリジナルのトランザクションが以下のような場合、
SQL>UPDATE prod1 SET col1 = (SELECT col2 FROM prod2 WHERE col3 = 'Y');
1. テスト用に prod1, prod2の小さいテーブルを作成します
SQL>CREATE TABLE test1 AS SELECT * FROM prod1 WHERE ROWNUM <= 100;
SQL>CREATE TABLE test2 AS SELECT * FROM prod2 WHERE ROWNUM <= 100;
2. もし prod1, prod2がインデックスを持っていたら、同様に作成します
SQL>CREATE UNIQUE INDEX i_test1 ON test1(col1);
SQL>CREATE UNIQUE INDEX i_test2 ON test2(col2);
3. オリジナルのトランザクションをテスト・テーブルに対して行う SQL文を含んだ TEST.SQLというテスト・スクリプトを作成します。例えばオリジナルのトランザクションが上記の例のような場合、以下のようになります
SQL>UPDATE test1 SET col1 = (SELECT col2 FROM test2 WHERE col3 = 'Y');
4. UNDO.SQL(次項図3参照)を実行して、TEST.SQLが実行されるときにデータベース全体で使用される undoを測定します
UNDO.SQLが使用するシステムディクショナリ V$ROLLSTATの定義は図2のとおりです。
図2. V$ROLLSTAT ビュー
Column Name Description
----------- -----------
USN ロールバックセグメント番号
EXTENTS ロールバック・エクステントの数
RSSIZE ロールバック・セグメントのサイズ(byte)
WRITES 起動時からロールバック・セグメントに書き込まれたバイト数
XACTS アクティブ・トランザクションの数
GETS 獲得したヘッダの数
WAITS 待機したヘッダの数
OPTSIZE ロールバックセグメントの最適サイズ
HWMSIZE ロールバックセグメントのサイズの高水位標
SHRINKS ロールバックセグメントが縮小された回数
WRAPS ロールバックセグメントがラップされる回数
EXTENDS ロールバックセグメントのサイズが拡張された回数
AVESHRINK 平均縮小サイズ
AVEACTIVE アクティブエクステントの単位時間当りの現行平均サイズ
STATUS ロールバックセグメントの状態
CUREXT 現行のエクステント
CURBLK 現ブロック
UNDO.SQLは TEST.SQLの実行前後でのロールバック・セグメントへの全ての書き込みを調査します。実行前後での書き込み数の差は、このとき発生した undoの大きさです。従って他のトランザクションが実行されていない状態での測定でなければ正確ではありません。
図3. UNDO.SQL rem UNDO.SQL
rem この SQL*Plusコマンドは複数の SQLやPL/SQL文が含まれるトランザクションで
rem 使用する UNDOの量を測定することが出来ます。テストするトランザクションを
rem TEST.SQLに書きカレント・ディレクトリに置いて UNDO.SQLを実行してください。
rem 注意点: このスクリプトを実行するユーザは V$ROLLSTATにアクセス出来ないと
rem いけません
rem 注意点: このテスト実行するときには他のユーザが何もしていないときに
rem してください
SET FEEDBACK OFF
SET TERMOUT OFF
COLUMN NAME FORMAT A40
DEFINE UNDO_OVERHEAD_FOR_THIS_SCRIPT = 54 *1
DROP TABLE undo$begin;
DROP TABLE undo$end;
CREATE TABLE undo$begin (writes NUMBER);
CREATE TABLE undo$end (writes NUMBER);
INSERT INTO undo$begin SELECT SUM(writes) FROM SYS.V$ROLLSTAT
/
rem TEST.SQLがあれば実行する
SET TERMOUT ON
SET FEEDBACK ON
@test
SET TERMOUT OFF
SET FEEDBACK OFF
INSERT INTO undo$end SELECT SUM(writes) FROM V$ROLLSTAT
/
SET TERMOUT ON
SET FEEDBACK ON
SELECT ((e.writes-b.writes) - &UNDO_OVERHEAD_FOR_THIS_SCRIPT)
"UNDOのバイト数" FROM undo$begin b, undo$end e
/
SET TERMOUT OFF
SET FEEDBACK OFF
DROP TABLE undo$begin;
DROP TABLE undo$end;
*1 UNDO_OVERHEAD_FOR_THIS_SCRIPTは、
INSERT INTO undo$begin SELECT SUM(writes) FROM V$ROLLSTAT;
INSERT INTO undo$end SELECT SUM(writes) FROM V$ROLLSTAT;
というSQL文を使用します。undoのオーバーヘッドを避けるために必要なパラメータです。このSQL文のオーバーヘッドは常に一定ですが、その値はRDBMSのバージョンによって違いがあります。使用している環境でのこの値を求めるためには、TEST.SQLに「SELECT * FROM EMP;」のようにUNDOを生成しないSQL文を指定し、UNDO.SQLを実行したとき、UNDOのバイト数の結果が０になるようにUNDO_OVERHEAD_FOR_THIS_SCRIPTの値を調整してください。
全体のundoの計算
もし undoが 'x' byteで 'n'行のテーブルであれば、以下の式でトランザクション全体で生成される undoの大きさを計算することが出来ます。
T = x * (N / n) * 1.05
T = このトランザクションで使用する undoのトータルサイズの推測値 (byte)
x = 今回のテストで生成された undo (byte)
N = オリジナルテーブルの行数
n = テストテーブルの行数
今回測定した大きなトランザクションと同時に実行される可能性のある全てのトランザクションに関しても、上のスクリプトによる測定を行い、それらを合わせた大きさがロールバック・セグメントの最大の大きさになります。
この手法は、特にそのトランザクションの実行に長い時間がかかったり、何度も実行するのが困難な場合に、ロールバック情報(undo)の大きさを決定するときに非常に役にたちます。
また、この手法の結果の正確さは、テストトランザクションがどれだけ正確に実際の状況を反映するかにかかっています。SQL文や行サイズが大きく変化するトランザクションの場合、サンプルテーブルを大きくすることによってしか結果を正確にする方法はありません。しかし、それにもかかわらず、単独の大きなトランザクションを実行しようとしたときに領域をオーバーしないことを保証するためには、非常に役に立つ手法です。
5.4.3.ロールバックセグメントの数及びサイズ
ロールバックセグメントのサイズ及び数を設計するには、トランザクションの量・個数を・タイプを事前に調査しておく必要があります。ここではサンプルとして次のような構成で考えます。
トランザクションのタイプ
個数
総エントリサイズ
エントリの平均サイズ
最大エントリ
製造品目入力
３
２１０K
７０K
７０K
年間製造量集計
１
５００K
５００K
５００K
製造番号入力
２０
８００K
４０K
４０K
年間製造種別解析
１
７００K
７００K
７００K
月間製造量集計
１０
５００K
５０K
５０K
合計
３５
２７１０K
ここで、個数は同時に実行される各タイプのトランザクションの個数を示します。また、総エントリサイズは同時実行される各タイプの全エントリを示しています。
１つのトランザクションで複数のレコードを更新しても、トランザクションは１つのみカウントされます。上表のケースでは、データベース内で平均３５個のトランザクションが同時に実行されていることになります。また、データの平均サイズは２７１０KBです。
１．単一トランザクションをサポートするのに必要な最小可能サイズを計算します。
前提条件
２０％の空き領域を残す。
１５％を非アクティブで使用中のデータに使用する。
５％をヘッダ領域として使用する。
最小可能サイズ = （トランザクションの最大サイズ） * １００ /
(１００-((空き領域の％)＋（非アクティブで使用中のデータの％）＋
（ヘッダの％）））
= （トランザクションの最大サイズ） * １００ / （100-(20+15+5)）
= （トランザクションの最大サイズ） * １．６７
= ７００KB * １．６７
= １１７０KB
結果より、トランザクションを処理するには、最低１１７０KB以上のロールバックセグメントを作成する必要があります。
２．必要なロールバックセグメントの最小総サイズを計算します。
前提条件
２０％の空き領域を残す。
１５％を非アクティブで使用中のデータに使用する。
５％をヘッダ領域として使用する。
最小総サイズ = SUM（エントリの総サイズ） * １００ /
(１００-((空き領域の％)＋（非アクティブで使用中のデータの％）＋
（ヘッダの％）））
= SUM（エントリの総サイズ） * １００ / （100-(20+15+5)）
= SUM（エントリの総サイズ） * １．６７
= ２７１０KB * １．６７
= ４５２５KB
結果より、全てのエントリに対して、ある時点のロールバックセグメント領域の総量は最小でも４５２５KB以上なければなりません。
３．ロールバックセグメントの最小必要数および最大必要数を求めます。
ロールバックセグメントの最小必要数 = ４５２５ / １１７０
= ３．８７
= ４ （切り上げる）
ロールバックセグメントの最大必要数 = 同時に実行されるトランザクション数
= ３５
結果より、ロールバックセグメントは最小で４必要で、最大で３５必要であることが分かります。
４．トランザクションを平均エントリサイズに従って分類します。
トランザクションのタイプ
個数
総エントリサイズ
エントリの平均サイズ
最大エントリ
グループ
製造品目入力
３
２１０K
７０K
７０K
グループＡ
製造番号入力
２０
８００K
４０K
４０K
月間製造量集計
１０
５００K
５０K
５０K
小計
３３
１５１０K
年間製造量集計
１
５００K
５００K
５００K
グループＢ
年間製造種別解析
１
７００K
７００K
７００K
小計
２
１２００K
合計
３５
２７１０K
この分類表より、最小エクステントサイズは、そのグループの最大トランザクションのサイズになります。
グループＡの最小エクステントサイズ = ７０ＫＢ
グループＢの最小エクステントサイズ = ７００ＫＢ
この値より、グループＡのトランザクションは７０ＫＢで処理可能ですが、グループＢのトランザクションでは、(７００Ｋ/７０Ｋ)の計１０エクステントもラップしてしまいます。グループＢのトランザクションに対するラップ回数を抑えるため、エクステントサイズを大きくする検討をします。
目安として、全グループの最小エクステントサイズを１．５～２倍することでおおよその最適な値が得られます。ここでは、２倍した１４０ＫＢとします。
Ｏｒａｃｌｅでは、ロールバックセグメント数の算出を、下表に示すような推奨値を提示しています。
同時トランザクション数 (n)
ロールバックセグメント数の推奨値
N < 16
4
16 <= N < 32
8
32 <= N
N/4 (但し５０以下)
よって、（３５/４）から９個のロールバックセグメントが求められます。これに加えて、同時トランザクション数が４に対して１のロールバックセグメントを用意することも推奨しているため、中間の６個を採用します。
ここまでで得られたデータ。
「データベースの全ロールバックセグメントの最小総サイズ」 ４５２５ＫＢ
「ロールバックセグメント最小可能サイズ」 １１７０ＫＢ
「ロールバックセグメント数」 ６個
「１エクステントサイズ」 １４０ＫＢ
５．１セグメントで必要な最小エクステント数を求めます。
１セグメントで必要な最小エクステント数 = (単一エクステントのトランザクション数) ＋
（(長いトランザクションにおけるラップの回数)＋１） * (長いトランザクションの平均個数))
= (３３/６)＋(４＋１) * １
= ５．５ ＋ ５
= １１ （切上げ）
一番長いトランザクションは７００ＫＢなので、長いトランザクションにおけるラップの回数は、１エクステント１４０ＫＢより４発生します。
６．非アクティブで使用中の領域を求めます。
非アクティブで使用中の領域 = (非アクティブで使用中のパーセンテージ) *
（最小エクステントの個数）
= ０．１５ * １１
= ２ エクステント (切上げ)
非アクティブで使用中のパーセンテージは、１５％と見積もっています。
７．空き領域のエクステント数を求めます。
空き領域のエクステント数 = (５００ＫＢ / １４０ＫＢ)
= ４ （切上げ）
ここで、初期エクステント１を引いて
= ４－１
= ３
項番５で一番長いトランザクションに対して必要なエクステント数は調べたので、ここでは次に大きなトランザクション５００ＫＢのラップ回数をカウントします。
８．ＯＰＴＩＭＡＬの値を求めます。
ＯＰＴＩＭＡＬ = ((１セグメント当りのデータエクステント最小個数) ＋
(ロールバックセグメントのヘッダーのエクステント数) ＋
(非アクティブで使用中のエクステント数) ＋
(空き領域のエクステント数)) *
(エクステントサイズ)
= (１２＋１＋２＋３) * １４０Ｋ
= １８ * １４０Ｋ
= ２５２０ＫＢ
ロールバックセグメントのヘッダエクステント数は１と考えます。
１８がＭＩＮＥＸＴＥＮＴＳとなります。
以上の結果により、ＲＢＳ表領域にＲ１ロールバックセグメントを作成するケースを示します。
ＳＶＲＭＧＲ>create rollback segemnt R1 tablespace RBS
>storage (initial 140K next 140K optimal 25２0K minextents 18 maxextents 249 );
ＳＶＲＭＧＲ>alter rollback segemnt R1 online;
フラグメンテーションを防止するために、Next extentsはinitial extentsと同じ値を設定します。
以上サンプルのトランザクションから算出方法を提示しましたが、これで完全な値が定義できたわけではありません。カットオーバー後に実際のロールバック情報をＶ＄ＲＯＬＬＳＴＡＴなどで監視し、確認することが大切です。
最終更新：2008年11月15日 22:14
