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

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

前回は、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」状態(データ変更、既存のデータもチェッ
クされない)にして、既存のデータの一意性を崩して2つの状態の違いを見
てみよう。

  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」の利点は、一意でない索引で主キー制約を動作できる
ので、一意索引の作成が行なわれない。また、索引列の重複も避けることがで
きるのである。

次回から、内容を一変して新装開店しますので、しばらくお待ちください。

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

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

以上 風邪引いて3連休がパーの モンキー・ターンより