STORAGE句でのエクステント関連の設定

ディクショナリ管理とローカル管理で意味合いが異なるもの、そもそもローカル管理では使われない・無視されるものがありわかりにくい。マニュアル(SQLリファレンス)の記載も今一煮え切らない。ということで検証してみる。

STORAGE句で指定するエクステント関連の設定項目

INITIAL
NEXT
MINEXTENTS
MAXEXTENS (ローカル管理では無視される。エクステント数は事実上無制限)
MAXSIZE
PCTINCREASE

今回得た設計上の指針 (ローカル管理が大前提)

STORAGE句で指定するエクステント関連の設定項目で検討価値があるのは2つ

INITIAL
MAXSIZE

MAXSIZE

MAXSIZEはそのままの意味。テーブルにサイズ上限を設定したければ設定する。明らかに拡張しないマスタテーブルなどに用途があるかもしれないが、あえて制約をしたいという判断があるかは疑問。頭のすみにサイズ制約をつけられると記憶しておくぐらいとする。

INITIAL

作成時に割り当てるエクステントのサイズは下記式で決まる。
- 作成時に割り当てる下記計算で使った後
  - NEXTは無視される。AUTOALLOCATEの場合はOracleが適切と判断したサイズ(64KB, 1MB, 8MB, 64MBが候補)のエクステントを1つずつ追加する。UNIFORMの場合は常に均一サイズのエクステントを1つずつ追加する。NEXTは何の意味もない。
  - PCTINCREASEは無視される。0として扱われると考えても良いが、エクステントの追加時にNEXTすら使われないので使いようがない。
  - MINEXTENTS は 1 としてディクショナリに登録される。これも意味が無い。
- 下記式からわかるように、MINEXTENTSがデフォルトの1であれば、右項は0となりINITIALのみで初期の割り当てサイズが決まる。
- わざわざMINEXTENTS、PCTINCREASE、NEXTを指定しなくても、希望のサイズをINITIALに指定すればそれで初期の割り当てサイズは決められる。この決定以外にMINEXTENTS、PCTINCREASE、NEXTは使われないので、指定する動機は完全になくなる。

INITIALの指定を検討するケース

あらかじめサイズを確保することにより、確実に容量を確保する。
- 他のテーブルなどが膨張し、容量を確保できなくなる事をさける。
- 追加の容量割り当ては ALTER TABLE ALLOCATE EXTENT でもできるので、『指定するものとしないものがあると混乱する』と判断して指定しないことも十分あり。
AUTOALLOCATEを使用していて、かつ大きいテーブルである事がわかっている場合。
- INITIALにAUTOALLOCATEで使用するエクステントサイズ(64KB, 1MB, 8MB, 64MB)の倍数を指定することにより、適切なエクステントサイズを選択するよう促す事ができる。

エクステントを事前確保する性能的なメリットは訴求ポイントになりにくい

プロとしてのOracle物理設計入門 (Oracle現場主義)『ストレージ句』より引用

従来の設計では、将来想定されるデータ件数を格納できるだけのエクステントをINITIALに指定するのが一般的でした。しかし、この考えは拡張できるエクステント数に制限があったということと連続したデータを確保した方が、パフォーマンスが良いという考えからきたものです。一方、この場合は無駄な領域が多くなる可能性もあります。
現在では、エクステントの制限も無くなり、ディスクの仮想化が進んでいるため必ずしも連続したデータを確保できる保証はありません。そのため、INITIALは表領域のエクステント・サイズと同じにするか、想定される件数の1/10程度にすることをおすすめします。

エクステントサイズを大きくしてエクステント数を少なくする、事前に連続したエクステントを確保する、というアプローチが是認されるのは『エクステントからエクステントへ移動する場合にコストがかかる』という了解がある場合だ。ランダムアクセスについてはエクステントを跨ぐ可能性は小さいためエクステント数の議論はあてはまらない。となるとあてはまるのは全表スキャンだ。仮にUNIFORM 16M の表領域上で、1000エクステントになっている表(約16GB)を全表スキャンする場合に、全てのエクステント同士が隣接していないとする。この場合、エクステントを移動する際常にディスクのシーク・回転待ちがあると悲観的に考える。すると、シーク・回転待ちの回数は初回分も入れて1000回。回転数15KのHDDであれば平均的なシーク・回転待ち時間は5ms程度と期待できるので、1000回で計5秒になる。16GBのデータとなると、8Gbps(800MB/s)のFibre Channelで帯域幅を最大につかったとしても転送に20秒かかる。当然そんな速度が出る訳も無いので、もっと時間がかかる。とすれば、エクステント間を移動する際のシーク・回転待ちが5秒だとしても、全体としてみれば20%以内の時間しか占めないことになる。仮にこの数字が気になるとすれば、まず全表スキャンをやめる方法を模索するべきだろう。

検証

表領域は下記の通り作成。

動作が判りやすいと思われる UNIFORM 1M とした。

CREATE SMALLFILE TABLESPACE DATA_TEST
    DATAFILE '/oradata/TESTDB01/dbf/data_test.01.dbf' SIZE  1G  AUTOEXTEND OFF
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE  1M;

検証は下記のSQLで実施

ユーザはTESTとする。

-- 検証用の表
DROP TABLE storagetest PURGE;
CREATE TABLE storagetest (
    id number,
    data varchar2(1000)
  )
  TABLESPACE DATA_TEST -- UNIFORM(1M)
  STORAGE(
    INITIAL 5M
    NEXT 10M
    MINEXTENTS 4
    MAXEXTENTS 100 --本当に無視されるのか試しにいれてみる
    PCTINCREASE 100
    MAXSIZE 100M
    );

-- 作成直後の状態確認
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'storagetest');
SELECT
  TABLE_NAME,
  INITIAL_EXTENT/1024/1024 "INITIAL_EXTENT(MB)",
  NEXT_EXTENT/1024/1024 "NEXT_EXTENT(MB)",
  MIN_EXTENTS,
  MAX_EXTENTS,
  PCT_INCREASE
FROM USER_TABLES WHERE TABLE_NAME = 'STORAGETEST';
SELECT
  SEGMENT_NAME,
  EXTENTS,
  BLOCKS,
  INITIAL_EXTENT/1024/1024 "INITIAL_EXTENT(MB)",
  NEXT_EXTENT/1024/1024 "NEXT_EXTENT(MB)",
  MIN_EXTENTS,
  MAX_EXTENTS,
  MAX_SIZE,
  PCT_INCREASE
FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'STORAGETEST';

-- データを入れる
INSERT INTO storagetest VALUES(1, 'helloworld');
COMMIT;

-- データを入れたあとの状態確認
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'storagetest');
SELECT
  TABLE_NAME,
  INITIAL_EXTENT/1024/1024 "INITIAL_EXTENT(MB)",
  NEXT_EXTENT/1024/1024 "NEXT_EXTENT(MB)",
  MIN_EXTENTS,
  MAX_EXTENTS,
  PCT_INCREASE
FROM USER_TABLES WHERE TABLE_NAME = 'STORAGETEST';
SELECT
  SEGMENT_NAME,
  EXTENTS,
  BLOCKS,
  INITIAL_EXTENT/1024/1024 "INITIAL_EXTENT(MB)",
  NEXT_EXTENT/1024/1024 "NEXT_EXTENT(MB)",
  MIN_EXTENTS,
  MAX_EXTENTS,
  MAX_SIZE,
  PCT_INCREASE
FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'STORAGETEST';

-- データを入れて拡張させる
DECLARE
  data VARCHAR2(1000) := LPAD('*',1000,'*');
  extents_org NUMBER;
  extents_now NUMBER;
BEGIN
  SELECT count(*) INTO extents_org FROM USER_EXTENTS WHERE SEGMENT_NAME = 'STORAGETEST';

  FOR i IN 1..70 LOOP
    FOR j IN 1..1000 LOOP
      INSERT INTO storagetest VALUES(i, data);
    END LOOP;

    SELECT count(*) INTO extents_now FROM USER_EXTENTS WHERE SEGMENT_NAME = 'STORAGETEST';
    IF extents_org <> extents_now THEN
      DBMS_OUTPUT.PUT_LINE('EXTENT# CHANGED '
        || extents_org || ' -> ' || extents_now
        || ' @' || i || 'th loop');
      extents_org := extents_now;
    END IF;
  END LOOP;
END;
/

-- 拡張したあとの状態確認
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'storagetest');
SELECT
  TABLE_NAME,
  INITIAL_EXTENT/1024/1024 "INITIAL_EXTENT(MB)",
  NEXT_EXTENT/1024/1024 "NEXT_EXTENT(MB)",
  MIN_EXTENTS,
  MAX_EXTENTS,
  PCT_INCREASE
FROM USER_TABLES WHERE TABLE_NAME = 'STORAGETEST';
SELECT
  SEGMENT_NAME,
  EXTENTS,
  BLOCKS,
  INITIAL_EXTENT/1024/1024 "INITIAL_EXTENT(MB)",
  NEXT_EXTENT/1024/1024 "NEXT_EXTENT(MB)",
  MIN_EXTENTS,
  MAX_EXTENTS,
  MAX_SIZE,
  PCT_INCREASE
FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'STORAGETEST';

-- TRUNCATEしてみる
TRUNCATE TABLE storagetest;

-- TRUNCATE後の状態確認
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'storagetest');
SELECT
  TABLE_NAME,
  INITIAL_EXTENT/1024/1024 "INITIAL_EXTENT(MB)",
  NEXT_EXTENT/1024/1024 "NEXT_EXTENT(MB)",
  MIN_EXTENTS,
  MAX_EXTENTS,
  PCT_INCREASE
FROM USER_TABLES WHERE TABLE_NAME = 'STORAGETEST';
SELECT
  SEGMENT_NAME,
  EXTENTS,
  BLOCKS,
  INITIAL_EXTENT/1024/1024 "INITIAL_EXTENT(MB)",
  NEXT_EXTENT/1024/1024 "NEXT_EXTENT(MB)",
  MIN_EXTENTS,
  MAX_EXTENTS,
  MAX_SIZE,
  PCT_INCREASE
FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'STORAGETEST';

検証結果

作成直後の状態
- USER_TABLESには定義が登録されているが、USER_SEGMENTSにはまだ登録されていない。
- USER_TABLESには指定したINITIAL、NEXT、MIN_EXTENTS、PCTINCREASE がそのまま入っている。
- 無視されるらしい MAX_EXTENTS もそのまま入っている。
- この時点ではまだエクステントを確保していないようだ。

TABLE_NAME      INITIAL_EXTENT(MB)   NEXT_EXTENT(MB)    MIN_EXTENTS     MAX_EXTENTS    PCT_INCREASE  
--------------- -------------------- ------------------ --------------- -------------- --------------
STORAGETEST     5                    10                 4               100            100

SEGMENT_NAME    EXTENTS     BLOCKS       INITIAL_EXTENT(MB)    NEXT_EXTENT(MB)     MIN_EXTENTS     MAX_EXTENTS     MAX_SIZE     PCT_INCREASE 
--------------- ----------- ------------ --------------------- ------------------- --------------- --------------- ------------ -------------

データを入れたあとの状態
- エクステントが確保されている。INITIA_EXTENTは75MBになっている。
  - 考え方としては INITIAL[5MB] + NEXT[10MB] + NEXT*(100%+PCTINCREASE%)[20MB] + NEXT*(100%+PCTINCREASE%)*(100%+PCTINCREASE%)[40MB] = 75MB になる。
  - MINEXTENTS が 2 以上の場合には NEXT を PCTINCREASE を反映して増やしつつ、MINEXTENTS-1回加えたサイズを初期のエクステント割り当てにすることになる。
- PCTINCREASEは0にされている。
- MINEXTENTSは1にされている。
- USER_SEGMENTS にレコードが追加された。MAXSIZE は USER_TABLES にはなく USER_SEGMENTS にある。定義上の単位はバイト単位だが、保存上はブロック数だ(8KブロックでDB作成)。
- MAX_EXTENTS の値は定義値が完全に上書きされ、2G 個となっている。1Mのエクステントで形成した場合は 2G * 1MB で 2PB になる。
  - 事実上無制限と考えてよいだろう。
  - エクステント数を4桁以上にするのは避けた方が良さそうな雰囲気(OTNでの議論など)はあるので UNIFORM のサイズには注意。ただし、OTNの議論などはディクショナリ管理時代の影響もあるので、ローカル管理なら5桁ぐらいに挑戦してみるのもありか？

TABLE_NAME      INITIAL_EXTENT(MB)   NEXT_EXTENT(MB)    MIN_EXTENTS     MAX_EXTENTS    PCT_INCREASE  
--------------- -------------------- ------------------ --------------- -------------- --------------
STORAGETEST     75                   10                 1               2147483645     0           

SEGMENT_NAME    EXTENTS     BLOCKS       INITIAL_EXTENT(MB)    NEXT_EXTENT(MB)     MIN_EXTENTS     MAX_EXTENTS     MAX_SIZE     PCT_INCREASE 
--------------- ----------- ------------ --------------------- ------------------- --------------- --------------- ------------ ------------- 
STORAGETEST     75          9600         75                    10                  1               2147483645      12800        0

拡張したあとの状態
- 拡張する単位は1エクステント単位になっていることがループ内の出力からわかる。つまりNEXT_EXTENT は無視されており、UNIFORM の均一サイズのエクステントが1つずつ追加で割り当てられている。

EXTENT# CHANGED 75 -> 76 @67th loop
EXTENT# CHANGED 76 -> 77 @68th loop
EXTENT# CHANGED 77 -> 78 @69th loop
EXTENT# CHANGED 78 -> 79 @70th loop
EXTENT# CHANGED 79 -> 80 @71th loop

TABLE_NAME      INITIAL_EXTENT(MB)   NEXT_EXTENT(MB)    MIN_EXTENTS     MAX_EXTENTS    PCT_INCREASE  
--------------- -------------------- ------------------ --------------- -------------- --------------
STORAGETEST     75                   10                 1               2147483645     0           

SEGMENT_NAME    EXTENTS     BLOCKS       INITIAL_EXTENT(MB)    NEXT_EXTENT(MB)     MIN_EXTENTS     MAX_EXTENTS     MAX_SIZE     PCT_INCREASE 
--------------- ----------- ------------ --------------------- ------------------- --------------- --------------- ------------ ------------- 
STORAGETEST     80          10240        75                    10                  1               2147483645      12800        0

TRUNCATE後の状態
- TRUNCATEにより INITIAL_EXTENT まで切り詰められている。INITIAL の指定のみに依存せず、実際に初期に確保した容量は保つことがわかる。

TABLE_NAME      INITIAL_EXTENT(MB)   NEXT_EXTENT(MB)    MIN_EXTENTS     MAX_EXTENTS    PCT_INCREASE  
--------------- -------------------- ------------------ --------------- -------------- --------------
STORAGETEST     75                   10                 1               2147483645     0           

SEGMENT_NAME    EXTENTS     BLOCKS       INITIAL_EXTENT(MB)    NEXT_EXTENT(MB)     MIN_EXTENTS     MAX_EXTENTS     MAX_SIZE     PCT_INCREASE 
--------------- ----------- ------------ --------------------- ------------------- --------------- --------------- ------------ ------------- 
STORAGETEST     75          9600         75                    10                  1               2147483645      12800        0

AUTOALLOCATEの場合の検証

どうなるのかと思ったらカオスだった。
INITIAL、NEXT、MINEXTENTS、PCTINCREASE から INITIAL_EXTENT を計算するところまでは UNIFORM と同じ。
11.2 のマニュアル(SQLリファレンスの storage_clause の INITIAL)には『INITIALを14Mに設定した場合は、8Mのエクステントが2つ作成され、INITIAL設定を超える結果となります。8Mのエクステント1つと1Mのエクステント6つという最適度の低い作成方法は採用されません。』と書いてあるが、11.2.0.1 だと普通に 8M + 1M×6 になった(INITIAL 14M のみ指定)。
- INITIAL 132MB の場合は 64MB×2 + 8MB になった。微妙に超えすぎないように Oracle が加減している様子が伺える。
INITIAL で大きな値を指定すると、大きめのエクステント(64MB)を使ってくれる。すると拡張時にも大きめのエクステントを使ってくれる傾向が強くなる。
- INITIALが小さいと小さなエクステントばかり使って拡張しようとして効率が悪くなっている。

INITIALとその後の拡張の様子観察

テーブルの作成は下記のSQLで実施(INITIALのサイズは検証パターンで変える)。

DROP TABLE storagetest PURGE;
CREATE TABLE storagetest (
    id number,
    data varchar2(1000)
  )
  TABLESPACE DATA_TEST -- AUTOALLOCATE
  STORAGE(
    INITIAL initialM
    );

テーブルを拡張させるための処理(150MBぐらいのレコードを入れる)。

DECLARE
  data VARCHAR2(1000) := LPAD('*',1000,'*');
  extents_org NUMBER;
  extents_now NUMBER;
BEGIN
  SELECT count(*) INTO extents_org FROM USER_EXTENTS WHERE SEGMENT_NAME = 'STORAGETEST';

  FOR i IN 1..150 LOOP
    FOR j IN 1..1000 LOOP
      INSERT INTO storagetest VALUES(i, data);
    END LOOP;

    SELECT count(*) INTO extents_now FROM USER_EXTENTS WHERE SEGMENT_NAME = 'STORAGETEST';
    IF extents_org <> extents_now THEN
      DBMS_OUTPUT.PUT_LINE('EXTENT# CHANGED '
        || extents_org || ' -> ' || extents_now
        || ' @' || i || 'th loop');
      extents_org := extents_now;
    END IF;
  END LOOP;
END;
/

INITIALとその後の拡張の関係は下記の通り。
- テーブル作成後の状態は1レコードINSERTした状態。

INITIAL	テーブル作成後のエクステント	150,000レコード追加後のエクステント
指定せず	64KB×1 (計1エクステント)	64KB×1, 64KB×15, 1MB×63, 8MB×14 (計93エクステント)
4MB	1MB×4 (計4エクステント)	1MB×4, 1MB×60, 8MB×14 (計78エクステント)
16MB	8MB×2 (計2エクステント)	8MB×2, 8MB×20 (計22エクステント)
64MB	64MB×1 (計1エクステント)	64MB×1, 64MB×2 (計3エクステント)
120MB	64MB×1, 8MB×7 (計8エクステント)	64MB×1, 8MB×7, 64MB×1 (計9エクステント)
128MB	64MB×2 (計2エクステント)	64MB×2, 64MB×1 (計3エクステント)
191MB	64MB×2, 8MB×8 (計10エクステント)	64MB×2, 8MB×8 (計10エクステント)