ソートに関する検証 その８
～ソートに関する検証 その８ ～ ペンネーム ちゃむ
前回は、SORT_DIRECT_WRITES=FALSE,TRUEのときの動きを説明した。
今回は、SORT_DIRECT_WRITES=AUTOダイレクトソートに関する検証を行なう。
＜SORT_DIRECT_WRITES=AUTOについて＞
SORT_DIRECT_WRITES=AUTOは、デフォルトである。
マニュアルには以下のような記述がある。
「このパラメータをAUTOに設定すると、ソート領域サイズの値がブロックサイ
ズの10倍を超える場合、ディスクへの書き込み用にソート領域からメモリが割
り当てられます。」
これでは、かなりわかりにくい。
これは、次のような記述だとわかりやすいだろう。
「このパラメータをAUTOに設定すると、SORT_AREA_SIZE=655360（640K）以上だと
ダイレクトソートが行われ、SORT_AREA_SIZE=655359以下だとダイレクトソートは
行われないで、データベース・バッファを経由したソート処理になります。」
つまり、SORT_AREA_SIZE=655360がダイレクトソートの閾値となっているのである。
これは、Oracle8、Oracle7.3であれば、ほとんどのプラットフォームで当てはまる
はずである。(Oracle7.2では、SORT_DIRECT_WRITESはTRUEかFALSEしか設定できな
かったはず。）
SORT_DIRECT_WRITES=TRUEに設定したときに有効になるパラメータの中で、
SORT_WRITE_BUFFER_SIZE （直接書き込み用のバッファサイズ デフォルト 32k）
SORT_WRITE_BUFFERS （直接書き込み用のバッファ数の指定 デフォルト 2）
という2つのパラメータがある。これらにより、直接書き込み用バッファの大きさ
は、SORT_WRITE_BUFFER_SIZE×SORT_WRITE_BUFFERSで決定される。
直接書き込み用バッファに関しては、前回の図を参照
以下のSQL文で上記のパラメータのデフォルト値が正しいという証拠を示す。
（ISDEFAULT=TRUEのときは、デフォルト値のままだよという意味）
SELECT NAME,VALUE,ISDEFAULT FROM V$PARAMETER
WHERE NAME IN ('sort_direct_writes','sort_write_buffers',
'sort_write_buffer_size');
NAME VALUE ISDEFAULT
----------------------------------------------
sort_direct_writes AUTO TRUE
sort_write_buffers 2 TRUE
sort_write_buffer_size 32768 TRUE
sort_write_buffersのデフォルト値が1となっているリファレンスマニュアル
があるので注意してほしい。
上記の検索結果がsort_write_buffersのデフォルト値が2である証拠である。
さきほどの、マニュアル中に以下のような記述がある。
「ソート領域サイズの値がブロックサイズの10倍」
これは、SORT_WRITE_BUFFER_SIZEとSORT_WRITE_BUFFERSのデフォルト値の
掛け算 2×32K=64kの10倍、つまり640Kというサイズが閾値となると解釈
できる。さきほど、「ほとんどのプラットフォームで当てはまる」と記述
した。実際に閾値が640K以外の環境は、今まで私はでくわしたことはない。
でも、もし閾値が640K以外であるとすれば、V$PARAMETERで検索されるsort_
write_buffersとsort_write_buffer_sizeのデフォルト値の掛け算の値も64K
以外なのであろう。
では、sort_direct_writes=autoのときのダイレクトソートを行なうか行なわ
ないかの閾値がsort_area_size=655360であるかを確認してみよう。
＜検証方法＞
＜検証に使用するダイレクトソート処理を行なわないSQL文＞
SQL> alter session set sort_area_size=655360; SQL> alter session set sort_direct_writes=auto; SQL> select * from t10man_org order by sal;
＜検証に使用するダイレクトソート処理を行なうSQL文＞
SQL> alter session set sort_area_size=655359; SQL> alter session set sort_direct_writes=auto; SQL> select * from t10man_org order by sal;
＜検証に使用するデータベース・バッファの確認方法＞
SELECT COUNT(*) CNT FROM X$BH WHERE FILE#=2;
上記のSQL文を1秒おきに発行し動きを見る。
以下は、弊社自社開発インタプリタ言語 「POPSQL」で確認
（再びいやしく宣伝ですが、「POPSQL」は弊社製品 パフォーマンスインサイトの中で
使用されている言語です。簡単でしょ。監視ジョブとかもちょーイージーに作れます。）
-----------------------POPSQL始め--------------------------
REM X$BHを検索するためにSYSでCONNECT
CONNECT sys/manager
SET FETCH 1
LOOP(;;)
REM CNTはローカル変数（2行以上検索される場合はローカル配列）
REM として count(*)の値が格納される
SAMPLE SELECT COUNT(*) CNT FROM X$BH WHERE FILE#=2;
REM 画面に出力 で囲むと変数の値が展開される。
MESSAGE |CNT|
REM 1秒間スリープ
SLEEP 1
ENDLOOP
-----------------------POPSQL終わり--------------------------
＜検証結果＞
＜sort_area_size=655360、sort_direct_writes=autoのときの X$BHの様子＞
前回同様、以下が1秒ごとのバッファ数の推移の抜粋であるが、1ブロックだけ
データベースバッファに載っている様子が確認できる。つまり、ダイレクトソ
ートを行なっている証拠である。
|1|
|1|
|1|
|1|
|1|
|1|
＜sort_area_size=655359、sort_direct_writes=autoのときの X$BHの様子＞
前回同様、以下が1秒ごとのバッファ数の推移の抜粋であるが、200個のデータ
ベースバッファがすべて、ソートセグメントで埋め尽くされてしまった様子を
確認できる。（db_block_buffers=200）
|192|
|198|
|196|
|200|
|200|
|200|
以上 しっかり並べとけ（でも「そーと」ね） 茅ヶ崎にて
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ Oracle入門生活 ▼━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜Oracle入門＞ ペンネーム モンキーターン
今回は、「整合性制約の状態」の最終回をお送りしよう。
— それでは、どっぷり入門生活スタート！ —————————–
前回は、制約の状態を「DISABLE VALIDATE」に設定して動きを見てもらった。
今回は、制約の状態を「ENABLE NOVALIDATE」と「ENABLE VALIDATE」に設定し
たときの動きを見てみよう。
まず、検証で使うテーブルを作成する。
SQL> CREATE TABLE WORK
(ID number constraint PK_WORK PRIMARY KEY
NOT DEFERRABLE INITIALLY IMMEDIATE ) ;
状態を何も指定しないときは、デフォルトで「ENABLE VALIDATE」の状態に
なるのである。
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 ;
ここで、既存のデータに対して制約の状態によりチェックの違いを見るため
に、制約を「DISABLE NOVALIDATE」状態（データ変更、既存のデータもチェッ
クされない）にして、既存のデータの一意性を崩して２つの状態の違いを見
てみよう。
SQL> ALTER TABLE WORK DISABLE NOVALIDATE CONSTRAINT PK_WORK ;
表が変更されました。
SQL> INSERT INTO WORK(ID) VALUES(1) ;
^^^
SQL> COMMIT ;
これで、テーブルWORKは、一意なデータではなくなった。
まず、「ENABLE NOVALIDATE」状態について見てみよう。
「ENABLE NOVALIDATE」の状態とは、データの変更はチェックされるが既存の
データはチェックされないのである。
SQL> ALTER TABLE WORK ENABLE NOVALIDATE CONSTRAINT PK_WORK ; エラー行: 1: エラーが発生しました。 ORA-02437: (SCOTT.PK_WORK)を有効にできません - 主キー違反です。
次に、「ENABLE VALIDATE」状態について見てみよう。
「ENABLE VALIDATE」の状態とは、制約を何も指定せずに作成した場合この
状態である。変更するデータ、既存のデータ両方がチェックされる。
SQL> ALTER TABLE WORK ENABLE VALIDATE CONSTRAINT PK_WORK ; エラー行: 1: エラーが発生しました。 ORA-02437: (SCOTT.PK_WORK)を有効にできません - 主キー違反です。
「ENABLE VALIDATE」状態のときは、既存のデータに一意性違反があるのでエ
ラーになっている。
「ENABLE NOVALIDATE」状態のときは既存のデータはチェックされないはずで
あるが、ここではエラーが起こっている。
なぜかというと、テーブルを作成したときに主キー制約を付けて作成したため
に、暗黙で作成された索引 PK_WORK が一意な索引であるため邪魔をしている
のである。
そこで、主キーを含む一意でない索引を使って主キー制約を動作させて主キー
の制約チェックが行われるようにしてみよう。
まず、検証で使うテーブルを作成する。
SQL> CREATE TABLE WORK2
( ID number CONSTRAINT PK_WORK2 PRIMARY KEY
DISABLE NOVALIDATE ) ;
~~~~~~~~~~~~~~~~~~
SQL> INSERT INTO WORK2(ID) VALUES(1) ;
~~~
SQL> INSERT INTO WORK2(ID) VALUES(2) ;
SQL> INSERT INTO WORK2(ID) VALUES(3) ;
SQL> INSERT INTO WORK2(ID) VALUES(4) ;
SQL> INSERT INTO WORK2(ID) VALUES(5) ;
SQL> INSERT INTO WORK2(ID) VALUES(1) ;
~~~
SQL> COMMIT ;
これで、一意でないデータのテーブルが作成された。
このテーブルWORK2に一意でない索引を作成する。
SQL> CREATE INDEX WORK2_IDX ON WORK2(ID) ; 索引が作成されました。
この索引を使って、主キー制約を動作させて主キーの制約チェックを行なうの
である。
SQL> ALTER TABLE WORK2 ENABLE NOVALIDATE CONSTRAINT PK_WORK2 ;
表が変更されました。
SQL> SELECT TABLE_NAME , INDEX_NAME FROM USER_INDEXES
WHERE TABLE_NAME = 'WORK' ;
TABLE_NAME INDEX_NAME
-------------- --------------
WORK WORK2_IDX
テーブルWORK2を作成したときに定義したPK_WORK2索引が使われていないのが
確認できる。
制約が正しく動作するか見てみよう。
SQL> INSERT INTO WORK2(ID) VALUES(1) ;
~~~
エラー行: 1: エラーが発生しました。
ORA-00001: 一意制約 (SCOTT.PK_WORK2) に反しています。
「ENABLE NOVALIDATE」状態のときに、データの変更はチェックされるが既存の
データはチェックされないのが確認できる。
「ENABLE NOVALIDATE」の利点は、一意でない索引で主キー制約を動作できる
ので、一意索引の作成が行なわれない。また、索引列の重複も避けることがで
きるのである。
次回から、内容を一変して新装開店しますので、しばらくお待ちください。
今回は、この辺で「どっぷり入門生活」おしまい ————————-
「どっぷり入門生活」では、基本的な質問をお待ちしています。また、このよ
うなテーマでやってほしい等の要望がございましたら、メールでどしどしお寄
せください。
以上 風邪引いて３連休がパーの モンキー・ターンより