ソートに関する検証 その６
～ソートに関する検証 その６ ～ ペンネーム ちゃむ
今回は、前回の前提をふまえて以下のような指針が正しいかどうかを検証する。
＜指針＞
初期化パラメータのSORT_AREA_SIZEと一時表領域のDEFAULT STORAGE句のINITIAL
、NEXT、PCTINCREASE に関して、次のような指針がある。
「SORT_AREA_SIZE=INITIAL=NEXT PCTINCREASE=0にする。」
以下の検証はDB_BLOCK_SIZE=2Kの環境でかつソートを行なうSQL文を発行する前に
Oracleを再起動する。
＜SORT_AREA_SIZEの閾値＞
SELECT /*+ FULL(T10) */ EMPNO,JOB FROM T10MAN_ORG T10 ORDER BY EMPNO;
（T10MAN_ORGはEMP表を10万件に拡張したようなもの）
上記のようなSQL文であれば、SORT_AREA_SIZEの閾値は3241984と3241985であった。
以下にその時のAUTOTRACEの様子を示す。
＜メモリソート＞
SQL> ALTER SESSION SET SORT_AREA_SIZE=3241985; SQL> SET AUTOTRACE TRACE SQL> SELECT /*+ FULL(T10) */ EMPNO,JOB FROM T10MAN_ORG T10 ORDER BY EMPNO; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1607 Card=100000 Byt es=1100000) 1 0 SORT (ORDER BY) (Cost=1607 Card=100000 Bytes=1100000) 2 1 TABLE ACCESS (FULL) OF 'T10MAN_ORG' (Cost=379 Card=10000 0 Bytes=1100000) Statistics ---------------------------------------------------------- 0 recursive calls 15 db block gets 2499 consistent gets 1433 physical reads 0 redo size 5706824 bytes sent via SQL*Net to client 740350 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 1 sorts (memory) ← メモリソートをした証拠 0 sorts (disk) ← ディスクソートをしていない証拠 100000 rows processed
＜ディスクソートの閾値＞
ALTER SESSION SET SORT_AREA_SIZE=3241984;
「ソート先のテーブルスペースの情報」
１．SCOTTユーザーのTEMPORARY_TABLESPACEはTEMP5である様子。
SQL> SELECT USERNAME,TEMPORARY_TABLESPACE FROM USER_USERS; USERNAME TEMPORARY_TABLESPACE ------------------------------- SCOTT TEMP5
２．TEMP5のDEFAULT STORAGE句は、INITIAL=4K NEXT=2K PCTINCREASE=0であり
かつ専用一時表領域（TEMPORARY）である様子。
（INITIALエクステントは、セグメントヘッダ１ブロックを含むのでTEMP5の
DEAFULT STORAGE句に INITIAL=2Kと指定して作っても実際はINITIAL=4Kになる。
SQL> SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,PCT_INCREASE, CONTENTS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='TEMP5'; TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE CONTENTS ------------------------------------------------------------------ TEMP5 4096 2048 0 TEMPORARY
上記の専用一時表領域（TEMPORARY）の状況を踏まえてディスクソートを行なっ
てみよう。
SQL> ALTER SESSION SET SORT_AREA_SIZE=3241984; SQL> SET AUTOTRACE TRACE SQL> SELECT /*+ FULL(T10) */ EMPNO,JOB FROM T10MAN_ORG T10 ORDER BY EMPNO; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1607 Card=100000 Byt es=1100000) 1 0 SORT (ORDER BY) (Cost=1607 Card=100000 Bytes=1100000) 2 1 TABLE ACCESS (FULL) OF 'T10MAN_ORG' (Cost=379 Card=10000 0 Bytes=1100000) Statistics ---------------------------------------------------------- 89896 recursive calls ← 内部コールも盛りだくさん 6479 db block gets ← メモリソートより読み込み数が多い 71788 consistent gets ← メモリソートより読み込み数が多い 3572 physical reads ← メモリソートより読み込み数が多い 1342444 redo size ← SELECT文なのにREDOの生成 5706824 bytes sent via SQL*Net to client 740350 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 0 sorts (memory) ← メモリソートをしていない証拠 1 sorts (disk) ← ディスクソートをした証拠 100000 rows processed
では、その時の専用一時表領域がどれくらい拡張したかを以下のSQL文で確かめてみよう。
SQL> SELECT TABLESPACE_NAME,SEGMENT_NAME,EXTENTS,BLOCKS FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='TEMP5'; TABLESPACE_NAME SEGMENT_NAME EXTENTS BLOCKS ---------------------------------------------- TEMP5 13.450 1016 1016
EXTENTS=1016、BLOCKS=1016よりエクステントが1016個でブロック数が1016ブロック
確保された様子がわかる。
ここで、検証したかった内容を考えてみる
SORT_AREA_SIZE=3241984=3241984/1024=3166K=3166/2=1583ブロックである。
また、ディスクソートの閾値は 3241985だが、内部的にはDB_BLOCK_SIZEに
丸められているはずである。つまり、1584ブロックである。
＜結果比較＞ ディスク上での実際のソートブロック数（BLOCKS）= 1016ブロック そのときのSORT_AREA_SIZEの閾値 = 1583ブロック
今回の検証ではSORT_AREA_SIZEの閾値1583の約3分の2が専用一時表領域
（TEMPORARY）に書き込まれたことになる。
つまり、SORT_AREA_SIZE=INITIAL=NEXTというようなことにはならない。
しかし、一番いけないのは、INITIAL=NEXT=2Kのように小さいエクステントに
対してメモリから吐き出すことである。今回検証した指針に従えば、ソート
セグメントの1エクステントで収まるので、この指針は個人的に適切であると
判断している。但し、前回でも述べたが、セグメントヘッダー分を加味して、
「INITIAL=NEXT=SORT_AREA_SIZE+DB_BLOCK_SIZE、PCTINCREASE=0」
なんてここまで細かい指針はいらない気がする。
また、もっとソート量が多いときに、INITIAL=NEXT=SORT_AREA_SIZE*正の整数
というような指針も間違いではないであろう。
最後に、今回検証するに当たり統計上で違いが出たのでその説明をしておこう。
recursive calls、redo sizeは、一時表領域上での領域管理に必要としたもの。
ということでREDOは、SELECT文でも発生することはある。
consistent gets、physical readsは、ソート済みデータを一時表領域に書き出
し（一時待避）、マージ処理のために再度読み込む必要があるため、その分、
メモリソートに比べて読み込み数が増えるのは当然であろう。
以上 鉄砲通りが長くなった 茅ヶ崎にて
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ Oracle入門生活 ▼━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜Oracle入門＞ ペンネーム モンキーターン
前回は、「整合性制約の管理」（遅延制約の設定方法）について理解していた
だけたと思う。
今回は、「整合性制約の管理」のつづきで「整合性制約の状態」についてお送
りしよう。
— それでは、どっぷり入門生活スタート！ —————————–
整合性制約は、次のいずれかの状態にすることができる。
１．DISABLE NOVALIDATE・・・新たに入力されるデータの制約はチェックされない。
既存のデータもチェックされない。
２．DISABLE VALIDATE・・・・データの変更が禁止される。
既存のデータはチェックされる。
UPDATE文、INSERT文、DELETE文を受け付けない。
つまり、既存のデータの制約は確実に保証する。
３．ENABLE NOVALIDATE ・・・新たに入力されるデータの制約はチェックされる。
既存のデータはチェックされない。
全体のデータを見れば、制約が守られているわけで
はない。
４．ENABLE VALIDATE ・・・・新たに入力されるデータの制約はチェックされる。
既存のデータもチェックされる。
注：２．は、Oracle8iから拡張された。（一番特殊な機能かも）
３．は、Oracle8から拡張された。 （あまり使えない機能かも）
Oracle8でいう「DISABLE」は、１．のことである。
Oracle7でいう「DISABLE」と「ENABLE」は、１．と４．のことである。
それでは、それぞれの状態に設定したときの動きを見てみよう。
まず、検証で使うテーブルを作成する。
SQL> CREATE TABLE WORK (ID number constraint PK_WORK PRIMARY KEY NOT DEFERRABLE INITIALLY IMMEDIATE ) ;
状態を何も指定しないときは、デフォルトで４．の状態になるのである。
SQL> INSERT INTO WORK(ID) VALUES(1) ; SQL> INSERT INTO WORK(ID) VALUES(2) ; SQL> INSERT INTO WORK(ID) VALUES(3) ; SQL> INSERT INTO WORK(ID) VALUES(4) ; SQL> INSERT INTO WORK(ID) VALUES(5) ; SQL> COMMIT ;
各状態に変更するには、ALTER TABLE 文を使う。
ALTER TABLE 表名 変更したい状態 CONSTRAINT 制約名 ;
＜DISABLE VALIDATEに変更する例＞
SQL> ALTER TABLE EMP DISABLE VALIDATE CONSTRAINT PK_EMP ;
＜ DISABLE NOVALIDATE の場合＞
これは、Oracle7またOracle8でいう「DISABLE」と同じ状態である。
Oracle8iで「DISABLE」と指定したときも、この状態になるのである。
WORK表の PK_WORK制約を DISABLE NOVALIDATE状態にして、動きを見てみよう。
入力データの制約はチェックされないし、既存のデータもチェックされない
はずである。
SQL> SELECT TABLE_NAME , INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'WORK' ; TABLE_NAME INDEX_NAME -------------- -------------- WORK PK_WORK SQL> ALTER TABLE WORK DISABLE NOVALIDATE CONSTRAINT PK_WORK ; 表が変更されました。 SQL> SELECT TABLE_NAME , INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'WORK' ; レコードが選択されませんでした。
主キーを DISABLE NOVALIDATE に設定すると主キーに付いていた索引は、削除
されるのである。
SQL> INSERT INTO WORK(ID) VALUES(1) ; ---------------→（A） ~~~ 1行が作成されました。 SQL> COMMIT ; コミットが完了しました。
上の結果より
制約を DISABLE NOVALIDATE 状態に設定すると、PRIMARY制約が使用不可になり
主キーについていた索引が削除されていた。よって、PRIMARY制約に違反する
（A）の値も挿入することができたのである。
今回は、この辺で「どっぷり入門生活」おしまい ————————-
「どっぷり入門生活」では、基本的な質問をお待ちしています。また、このような
テーマでやってほしい等の要望がございましたら、メールでどしどしお寄せく
ださい。
以上 「NET&COM21 来てね！ 私も行きます。」 モンキー・ターンより
（場所：幕張メッセ、主催：日経BP社）