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

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

初期化パラメータのSORT_AREA_SIZEと一時表領域のDEFAULT STORAGE句のINITIAL、
NEXT、PCTINCREASE に関して、次のような指針がある。
「SORT_AREA_SIZE=INITIAL=NEXT PCTINCREASE=0にする。」（メルマガ38回参照）

この指針は、簡単に言ってしまえば、メモリ上のSORT_AREA_SIZEで収まりきら
なかったソート領域をディスク上の一時表領域に出力するのであるが、そのサ
イズが最高でSORT_AREA_SIZEと同じサイズ分ずつ出力されるということだと思わ
れる。（この指針に従えば、例えばソート処理がSORT_AREA_SIZEの10倍の領域を必
要とするとき、SORT_AREA_SIZEで指定したサイズ分、10エクステント確保されるイ
メージである。）

さらに、細かい指針として、一時表領域のセグメントヘッダー（Oracleブロッ
クの1ブロック分）を考慮して、以下のような指針も見たことがある。
「INITIAL=NEXT=SORT_AREA_SIZE+DB_BLOCK_SIZE PCTINCREASE=0」

では、どちらの指針が正しいのか、またこれらの指針は正しいと言えるのか検証し
てみよう。

まず、この検証を行なうにあたり次のようなことを考えた。

１．
ソートを伴なうあるSELECT文を発行し、ディスク上でソートをするぎりぎりの
SORT_AREA_SIZEの値つまり、SORT_AREA_SIZEがあと1大きければ、ディスク上で
はなくメモリ上でソートを行なうような閾値を求める。

２．
１．により、本当にメモリ上で必要な領域というのがわかるはずである。
SORT_AREA_SIZEというのは、例えば10Mに設定されていても、EMP表のような
件数の少ないデータをメモリ上でソートした場合、メモリ上で10M確保する訳では
なく、「必要な分だけ」メモリ上に確保する。つまり、SORT_AREA_SIZEの大きさ分
メモリが確保される訳ではないので実際メモリ上でどれくらいの領域を必要とした
のかが見えない。ところが１．のSORT_AREA_SIZEの閾値を見つけることにより、メモ
リ上で必要とした領域がわかるはずである。

３．
１．の閾値を見つけるにあたってメモリ上でソートを行なったか、ディスク上で
ソートを行なったかを簡単に判断するためにAUTOTRACEの機能を使う。
実行計画や、ある一部の統計情報を確認するには、AUTOTRACEが簡単である。
今回はこれを利用して、統計情報sorts (memory)とsorts (disk)を取得する。

＜AUTOTRACEを行なうために必要な作業＞
＜スクリプト（UTLXPLAN.SQLやPLUSTRCE.SQL）はNT版 ORACLE8.1.6の環境で実行＞
（バージョンやOSが違っても同じ様な場所にあるから見つけられるでしょう）

A．
TRACEの情報を取りたいユーザー（以下の例ではSCOTT）でPLAN_TABLEを作成

SQL>CONNECT SCOTT/TIGER
/*UTLXPLAN.SQLはPLAN_TABLEを作成するスクリプト*/
SQL>@D:OracleOra81RDBMSADMINUTLXPLAN.SQL

B．
SYSユーザーでPLUSTRACEロールを作成しSCOTTへそのロールをGRANT

SQL>CONNECT SYS/MANAGER
/*PLUSTRCE.SQLは、PLUSTRACEというロールを作成するスクリプト*/
SQL>@D:OracleOra81sqlplusadminPLUSTRCE.SQL
SQL>GRANT PLUSTRACE TO SCOTT;

これでAUTOTRACE の機能が使用できる。

４．
SORT_AREA_SIZEの閾値を見つけるにあたり、初期化パラメータファイル（init.ora）
をいちいち変更してOracleを再起動するのではなく、以下のようなコマンドでセッ
ションごとに変更することにする。

ALTER SESSION SET SORT_AREA_SIZE=3241985;

初期パラメータには、セッションレベルで変更できるもの、システム全体に対して
変更できるもの、どちらでも変更できないものなどがある。
それらは、V$PARAMETERのISSES_MODIFIABLE,ISSYS_MODIFIABLE列で確認できる。

ISSES_MODIFIABLE列   セッションレベルで変更できるか？（ALTER SESSION）
ISSYS_MODIFIABLE列   システム全体に対して変更できる？（ALTER SYSTEM)

では、実際にSORT_AREA_SIZEを検索してみよう。

SELECT NAME,VALUE,ISSES_MODIFIABLE,ISSYS_MODIFIABLE
FROM V$PARAMETER WHERE NAME = 'sort_area_size';
NAME              VALUE     ISSES_MODIFIABLE   ISSYS_MODIFIABLE
---------------------------------------------------------------
sort_area_size    3241985   TRUE               DEFERRED

ISSES_MODIFIABLE=TRUEであれば、ALTER SESSIONコマンドで変更できることを
意味する。また、ISSYS_MODIFIABLE=DEFERREDであれば、ALTER SYSTEM DEFFERD
コマンドで変更できることを意味する。ALTER SYSTEMを試してみようか。

SQL>  ALTER SYSTEM SET SORT_AREA_SIZE=3241985;
ORA-02096: 指定した初期化パラメータはこのオプションでは修正できません。
SQL>  ALTER SYSTEM  SET SORT_AREA_SIZE=3241985 DEFERRED;
システムが変更されました。

上記を見てわかるように、ISSYS_MODIFIABLE=DEFFERDのパラメータは、DEFFERD
オプションをつけないと「ORA-02096」が発生する。

また、ALTER SYSTEM DEFFERDで変更されたパラメータの値は、既に接続されている
セッションでは反映されず、それ以後に接続してきたセッションにのみ反映される。

５．
ディスクソートされる表領域のDEFAULT STORAGE句をINITIAL=2K NEXT=2K
PCTINCREASE=0にすることにより、SORT_AREA_SIZEの閾値でソートした際にどれ
だけエクステントが発生したかを調べる。また、このソート用の表領域は、
ソート終了後に空領域として解放されない専用一時表領域（PERMANENT）とする。

６．
ソート処理は以下のSQL文で行なう。

SELECT /*+ FULL(T10) */ EMPNO,JOB FROM T10MAN_ORG T10 ORDER BY EMPNO;

（T10MAN_ORGはEMP表を10万件に拡張したようなもの）

次回は、上記６つの前提条件をふまえて、検証の結果を示す。

以上雪解けの茅ヶ崎にて

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ Oracle入門生活 ▼━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜Oracle入門＞ペンネームモンキーターン

前回は、制約の実行タイミングを見ていただいた。
今回は、前回の続きで「整合性制約の管理」をお送りしよう。
内容は、遅延制約の設定方法についてである。

— それでは、どっぷり入門生活スタート！ —————————–

制約の設定とは、その制約が遅延可能かどうかを設定し、且つ初期状態は即時
か遅延かを設定することである。

制約の遅延に関する属性は、３種類である。

１．遅延可能（ DEFERRABLE ）で 初期状態は遅延（ INITIALLY DEFERRED ）
２．遅延可能（ DEFERRABLE ）で 初期状態は即時（ INITIALLY IMMEDIATE ）
３．遅延不可（ NOT DEFERRABLE ）で 初期状態は即時（ INITIALLY IMMEDIATE ）

ここで、遅延不可に初期状態は遅延を指定しようとすると、遅延不可の制約に
初期状態を遅延にしようとしているので、エラーが返るのである。

＜設定に関する注意点＞
・遅延可能（ DEFERRABLE ）、遅延不可（ NOT DEFERRABLE ）の指定を省略し
た場合、デフォルトで３．の遅延不可に設定される。遅延不可の制約は、ALTER
文、 SET CONSTRANTS文を使っても遅延状態には出来ないので注意が必要で
ある。ただし、初期遅延（ INITIALLY DEFERRED ）が指定されている場合は、
自動的に遅延可能に設定される。

・初期遅延（ INITILLY DEFERRED ）、初期即時（ INITILLY IMMEDIATE ）の
指定を省略した場合、デフォルトで初期即時に設定される。

例＞

SQL> CREATE TABLE EMP(
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY  指定なし
~~~~~~~~~~

これは、遅延不可に設定される（デフォルト）。一度遅延不可に設定され
ると遅延制約状態への変更は、出来ないので注意が必要である。
言い換えると、遅延可能（ DEFERRABLE ）の指定をしないと、遅延制約は
使用できないのである。

SQL> CREATE TABLE EMP(
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY INITIALLY DEFERRED
~~~~~~~~~~~~~~~~~~

これは、遅延可能で初期状態は遅延状態に設定される。

SQL> CREATE TABLE EMP(
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY DEFERRABLE
~~~~~~~~~~

これは、遅延可能で初期状態は即時状態に設定される。

SQL> CREATE TABLE EMP(
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY NOT DEFERRABLE
~~~~~~~~~~~~~~

これは、遅延不可で初期状態は即時状態に設定される。

ここで、制約の設定を遅延可能（ DEFERRABLE ）のみの設定を行った場合に注
意していただきたいのは、遅延制約が使用可能な制約に設定しただけで、制約
が遅延制約状態であるわけではないのである。この場合、制約の初期状態は、即
時制約のままである。
それでは、どのようにして遅延制約可能な制約を遅延制約に設定するかというと

＜セッション内での制約の遅延指定＞

SQL> ALTER SESSION SET CONSTRAINTS = DEFERRED ;

これは、暗黙的にすべての制約が含まれます。

＜トランザクション内での制約の遅延指定＞

SQL> SET CONSTRAINTS 制約名 DEFERRED ;

これは、トランザクション期間中から、別の SET CONSTRAINTS文で制約の指定
が上書きされるまでか、COMMIT or ROLLBACKによりトランザクションが終了す
るまでである。トランザクションが終了すると、制約の状態は初期状態に戻る。
ここで、制約名のところに ALL と指定すると、すべての制約に対しての変更
となる。

今回は、この辺で「どっぷり入門生活」おしまい ————————-

「どっぷり入門生活」では、基本的な質問をお待ちしています。また、このような
テーマでやってほしい等の要望がございましたら、メールでどしどしお寄せく
ださい。

以上風邪気味モンキーターンより

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