ソートに関する検証　その３

～ソートに関する検証　その３～　　ペンネーム　ちゃむ

前回は、主に、メモリー上でソートを行なう際、どこで行われるかを中心に
説明した。
今回は、主にdiskでソートを行なう場合の一時表領域に関して説明する。

Oracleのソートアルゴリズムを考えた上で設定上の大きな要因は２つある。

１．初期化パラメータの SORT_AREA_SIZE
２．一時表領域のエクステントサイズ及び設定

初期化パラメータのSORT_AREA_SIZEはメモリー上でソート処理する際のMAXサイズ
を決めるものである。そのサイズに収まらないときは、ディスク上で、つまり一時
表領域上でソート処理を行なうことになる。

では、ソートエリアから一時表領域にどれくらいのサイズで書き出すのであろうか？
実は、これは、テーブルスペースのDEFAULT STORAGE句で決まる。TABLEなどを格納
するために使用する表領域に関しては、CREATE TABLEなどをするとき、STORAGE句を
指定しないと、このDEFAULT STORAGEのサイズになるが、通常は、個々のテーブルで
INITIAL NEXT PCTINCREASEなどのエクステントサイズは指定するので、問題になるこ
とはない。

しかし、「一時表領域のDEFAULT STORAGE句のINITIAL NEXT PCTINCREASEで
指定したエクステントサイズは、そのまま一時セグメントの大きさになる。」
したがって、一時表領域では、このDEFAULT STORAGEが非常に重要である。

それに関連してよく、「SORT_AREA_SIZE=INITIAL=NEXT PCTINCREASE=0にする。」
なんて指針がある。この指針の正当性に関しては、別の機会に検証する。

また、Oracle7.3以降の機能である専用一時表領域により、一時セグメント内の
エクステントの割当て、解放などを何度も行なう必要がなくなる。単なる一時表
領域は、永続表領域に作成する。この永続表領域は、TABLEとかINDEXなどのオブ
ジェクトを作成するときと同様、普通の表領域である。つまり、名前をTEMPとかにして、
USERのTEMPORARY TABLESPACEに指定しているだけで、その表領域にTABLEなどを作成
することは可能である。一方、専用一時表領域は、その表領域にTABLEなどを作成
することはできない。

＜永続表領域に一時表領域を作成する例＞

CREATE TABLESPACE TEMP1 DATAFILE 'D:TEMP1.ORA'SIZE 10M
DEFAULT STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0 MAXEXTENTS UNLIMITED);

その一時表領域にテーブルを作成した様子。問題なくテーブルを作成できる。

SQL> CREATE USER CHAMU1 IDENTIFIED BY CHAMU1
2  TEMPORARY TABLESPACE TEMP1
3  DEFAULT TABLESPACE USERS;
ユーザーが作成されました。
SQL> GRANT CONNECT TO CHAMU1;
権限付与が成功しました。
SQL> GRANT RESOURCE TO CHAMU1;
権限付与が成功しました。
SQL> CONNECT CHAMU1/CHAMU1
接続されました。
SQL> CREATE TABLE CHAMU1_TB TABLESPACE TEMP1 AS SELECT * FROM DUAL;
表が作成されました。

その専用一時表領域にテーブルを作成した様子。ORA-2195発生。

CREATE TABLESPACE TEMP2 DATAFILE 'D:TEMP2.ORA'SIZE 10M
DEFAULT STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0 MAXEXTENTS UNLIMITED)
TEMPORARY;

その一時表領域にテーブルを作成した様子。

SQL> CREATE USER CHAMU2 IDENTIFIED BY CHAMU2
2  TEMPORARY TABLESPACE TEMP2
3  DEFAULT TABLESPACE USERS;
ユーザーが作成されました。
SQL> GRANT CONNECT TO CHAMU2;
権限付与が成功しました。
SQL> GRANT RESOURCE TO CHAMU2;
権限付与が成功しました。
SQL> CONNECT CHAMU2/CHAMU2
接続されました。
SQL> CREATE TABLE CHAMU2_TB TABLESPACE TEMP2 AS SELECT * FROM DUAL;
エラー行: 1: エラーが発生しました。
ORA-02195: PERMANENTオブジェクトがTEMPORARY表領域に作成されようとしました。

また、専用一時表領域と単なる一時表領域の違いは、エクステントの割当て、
解放のタイミングである。

＜一時表領域＞

ソート処理のたびに一時セグメントのエクステントの割り当て、解放が行われる。
ソートが行われている一時セグメントは、ソート処理を行なっているプロセス
ごとに確保される。（一時セグメントが複数存在することがある。）

ソート処理がディスク上で必要になったとき、以下の手順でエクステントの割
当て、解放が行なわれる。

1.ソート処理に必要な一時セグメントを空ブロックから確保する。
2.ソート処理終了後、一時セグメントを解放して、空ブロックとする。

＜専用一時表領域＞

ソート処理のたびに一時セグメントのエクステントの割り当て、解放が行われる
わけではない。エクステントの割当てが行われるのはOracleインスタンスが起
動してから初めてのディスクソート処理が起きたときと、それ以降に、今までよ
りも大きいディスクソートの領域が必要になったときだけである。また、解放
するタイミングは、OracleインスタンスをSHUTDOWNするときのみである。
つまり、一時セグメントは、一度割り当てられると、SHUTDOWNするまでは空ブロ
ックとして解放されることはないのである。これにより、単なる一時表領域より割
当て、解放を行なう内部的な領域管理は少ないのである。

また、一時セグメントは、専用一時表領域にたった一つである。この単一セグメ
ントが複数プロセスからのディスクソート処理を一手に引き受ける。

ソート処理がディスク上で必要になったとき、以下の手順でエクステントの割
当て、解放が行なわれる。

1.今回のディスクソートで必要な一時セグメントが、既存の一時セグメントより
大きければ（正確に言うと、ディスクソートで必要な一時セグメントが既存の一
時セグメントのうちで他のプロセスが現在使用していないエクステントより大き
ければ）、その分の領域を空ブロックから新たに一時セグメントに割り当てる。
小さければ、空ブロックからの新たな一時表領域の割り当てはしない。
2.OracleインスタンスをSHUTDOWNするまでは、ソート処理終了後も一時セグメン
トを解放しない。

次回、このソート処理により、一時セグメントが実際にエクステントの割当て、解放
を行なう様子を見てみよう。

以上砂まじりの茅ヶ崎にて

ソートに関する検証 その３