前回のおさらい

前回「表領域には空きがあるのに(1/2)」では、空き容量が十分あるはずなのに、
表領域を拡張しようとすると何故かエラーが発生する、というところで終わりました。
さて、一体何が原因だったのでしょうか。

エクステントとは？

たろー「Tさんは確か、Oracle Master Silver に合格してたよね。」
新人T「はい、先日取得しました。」
たろー「じゃあ、『 エクステント の定義』を言ってごらん？」
新人T「え！？『 エクステント の定義』ですか？！」
たろー「うん。」
新人T「う…………。すみません、分かりません。」
たろー「 エクステント っていうのはね。『連続したブロックから構成される』んだ。」
新人T「連続したブロック？」
たろー「そうだ。今回獲得しようとした エクステント のサイズは、エラーメッセージから分かる通り、64MBだよね？」
新人T「はい。」
たろー「つまり、64MBの『連続したブロック』が USERS表領域に無いんだよ。」
新人T「？？？」
たろー「分かりにくいよね。絵で描くと、USERS表領域は今こんなイメージになってるんだ。」

新人T「！」
たろー「この状態で、『連続した64MBの領域』はあるかな？」
新人T「無いです！」
たろー「そうだね。要はこういうことだよ。USERS表領域は全体的に空きがあるんだけど、獲得しようとしている64MBのエクステントサイズがどこにも無いんだ。」
新人T「ええー・・・。」
たろー「Tさん。USERS表領域の エクステント 割当ての方式（ALLOCATION_TYPE）は何で作ったのかな？」
新人T「デフォルトの AUTOALLOCATE で作りました。」
たろー「だよね。さっき俺も確認したけど、、、」
　

set linesize 1000
col TABLESPACE_NAME for A10
col ALLOCATION_TYPE for A15

select TABLESPACE_NAME, ALLOCATION_TYPE
  from dba_tablespaces
 where TABLESPACE_NAME='USERS'
;

TABLESPACE ALLOCATION_TYPE
---------- ---------------
USERS      SYSTEM

たろー「dba_tablespaces の ALLOCATION_TYPEが SYSTEMってことは、AUTOALLOCATEで作られてるって意味だ。」
新人T「はい。」
たろー「AUTOALLOCATEでは、エクステント 追加対象の セグメント のサイズに応じて
Oracle Databaseが自動的に最適サイズを決定するんだ。」
新人T「ということは、64MBというのは・・・。」
たろー「そう。Oracle が自動で判断して取得しようとしたサイズだ。
何せPARALLEL(32)で作ろうとしているテーブルだ。さぞかし大きなサイズなんじゃないかな？」
新人T「はい、大きなテーブルです。DWH（Data Ware House）で使用するテーブルなので・・・。」

AUTOALLOCATE で取得されるエクステントサイズ

AUTOALLOCATE で作成された表領域で取得される エクステント サイズは、どのように決定されるのでしょうか？
残念ながらそれは Oracle の内部処理のため、明確な仕様が公開されていません。
Oracle Database のバージョンによっても、その仕様は異なる可能性があります。

ただ、セグメント のサイズに応じて、
　● 64KB
　● 1MB
　● 8MB
　● 64MB
上記４つのサイズのどれかを取得するようになっているようです。

たろー「試しにPARALLEL(1)で実行してごらん？」
新人T「PARALLEL(1)ですか？」
新人T（カタカタカタ）
新人T「あ、動いてます！どうしてですか？！」
たろー「PARALLEL(32)だと、32個のプロセスが同時に６４MBの領域を確保しようとするんだ。
だから、１個のプロセスでも領域を確保できないとすぐにエラーになる。」
新人T「な、なるほど・・・。でもPARALLEL(1)だとパフォーマンスが・・・。」
たろー「そう。パフォーマンスは出ない。」
新人T「パフォーマンスを維持しつつ、かつ、効率よく領域を使うにはどうしたらいいんでしょうか？」

パフォーマンスを維持して、表領域を効率よく使うには？

たろー「それは勿論、UNIFORM を使うしかないよ。」
新人T「UNIFORM ？？」
たろー「 エクステント サイズを Oracle Databaseに任せるんじゃなくて、自分で決めておくんだ。例えば、、、」
　

CREATE TABLESPACE USERS02
SIZE 5G AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 4M
;

set linesize 1000
col TABLESPACE_NAME for A10
col ALLOCATION_TYPE for A15

select TABLESPACE_NAME, ALLOCATION_TYPE
  from dba_tablespaces
 where TABLESPACE_NAME='USERS02'
;

TABLESPACE ALLOCATION_TYPE
---------- ---------------
USERS02    UNIFORM

たろー「表領域をこういう風に作るんだ。この例だと、エクステント サイズは４MBに固定される。」
新人T「な、なるほど・・・。これなら AUTOALLOCATE なんて必要ない。
これから構築するデータベースでは、表領域はもう全て UNIFORM で作ってしまえばいいですね。」
たろー「いや、そうとも限らないんだ。今回は大きなテーブルであることが分かっているけど、
どんなサイズか事前に分からなかったり、様々なサイズのテーブルが入り混じる場合は、UNIFORMでは決め難い。
どんなに小さいテーブルも４MBで作られてしまうからね。その場合は領域の無駄が多くなるので注意が必要だよ。」
新人T「た、確かに・・・。」

適切なエクステントサイズ

新人T「UNIFORM を使う場合、サイズはいくつにするのがいいんでしょうか？」
たろー「このシステムでは、おそらくASM（Automatic Storage Management）を使っているよね？」
新人T「はい、そうです。」
たろー「ならば、ASMディスクグループのAU（Allocation Unit）サイズと同じにするのがお奨めだ。AUサイズはいくつにしてるの？」
新人T「この表領域のデータファイルが格納されているASMディスクグループのAUサイズは４MBで作成しています。」
たろー「ならば、UNIFORM SIZE 4Mがお奨めだ。これはオラクル社のベストプラクティスでもある。」
新人T「分かりました。UNIFORM SIZE 4M で表領域を作って、対象オブジェクトを MOVE することにします。」
たろー「うん。MOVE した後、インデックスを REBUILD するのを忘れないでね。」
新人T「はい！ありがとうございます！」

---

こうしてTさんは、ORA-1653 の呪縛から逃れられることが出来ました。
　

AUTOALLOCATE は便利なんですが、痒い所に手が届かないんですよね。
『アプリケーション側でどのような使い方をされるのか？』が事前に分かっていれば、UNIFORM SIZE の使用をお奨めします。

 

 

 

 

今回の「心臓外科医の術式」いかがだったでしょうか？
『表領域の監視は使用率だけを見ている』というシステムが殆どだと思いますが、
本来ならば、獲得できる エクステント サイズが存在するかどうかを監視することが大切です。

次回も頑張りますので、応援よろしくお願い致します。
　

 

しかし、たろーちゃんのことを快く思わないH課長が、今回の件を黙って見過ごす訳はありませんでした。
（……続く）