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

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

今回から、ダイレクトソートに関する検証を行なう。
ダイレクトソートとは、ディスクソートを行なうときのアルゴリズムの一つで
ある。

＜ダイレクトソートとは＞

ディスクソートでダイレクトソートを使用しない場合は、データをデータベース
バッファに読み込み、一時表領域への書き出しはDBWRで行なう。

ダイレクトソートを使用する場合は、一時表領域へ書き出すためのバッファを
用意してデータの書き出しを行なうので、DBWRの負荷を軽減させることができ
るとともに、データベース・バッファに対する競合の軽減やヒット率の向上に
も効果がある。

以下にダイレクトソートを使用する場合としない場合の違いを図に示す。

上記の文章を読むと、ダイレクトソートは非常によい機能のように思われるかも
しれない。そう、非常にいい機能なのだ！！その裏付けの一つとして、この機
能はOracle8までは、sort_direct_writesというパラメータでfalse,true,auto
（デフォルト）のいずれかを設定して、ダイレクトソートを使用するかしない
かを決めていた。だが、Oracle8iでは、sort_direct_writesというパラメータ
は廃止され、ディスクソート処理はすべて「ダイレクトソート」になった。

今回の検証は、ダイレクトソートを使用するときと使用しないときで、データ
ベース・バッファ上でどのような動きの違いがあるかを実際に見てみる。この
検証は、Oracle8で行った（Oracle8iでは、すべてのソート処理がダイレクトソー
トで行われるので、動きの違いを検証することはできない）。

また、データベース・バッファの動きを見るために、X$BHを使用する。
BHは「BUFFER HEADER」の略である。X$BHで検索されるデータ数は、初期化パラ
メータのdb_block_buffersの値に一致する。

SVRMGR> show parameter db_block_buffers
NAME                                TYPE    VALUE
----------------------------------- ------- ------
db_block_buffers                    整数値  200   ← 一致！！
SVRMGR> SELECT COUNT(*) FROM X$BH;
COUNT(*)
----------
200 ← 一致！！

X$BHには、FILE#という列があり、FILE#はV$DATAFILEのFILE#と一致する。つま
り、そのファイル番号からどのデータファイルのデータがデータベース・バッ
ファ上に載っているのかがわかる。今回は、ソートで使用するテーブルスペース
のデータファイル番号は2なので、以下のようなSQL文を発行することにより、
ソートセグメントのデータブロックの数を確認することができる。

SELECT COUNT(*) CNT FROM X$BH WHERE FILE#=2;

＜＜検証方法＞＞

＜検証に使用するダイレクトソート処理を行なわないSQL文＞

SQL> alter session set sort_area_size=65536;
SQL> alter session set sort_direct_writes=false;
SQL> select * from t10man_org order by sal;

＜検証に使用するダイレクトソート処理を行なうSQL文＞

SQL> alter session set sort_area_size=65536;
SQL> alter session set sort_direct_writes=true;
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終わり--------------------------

＜検証結果＞

＜ダイレクトソートではない処理の検証結果＞

以下が1秒毎のバッファ数の推移（抜粋）であるが、200個のデータベース・
バッファがすべて、ソートセグメントで埋め尽くされてしまった。これが、
ヒット率の低下及び、DBWR負荷が増大する原因である。

|192|
|198|
|196|
|200|
|200|
|200|

＜ダイレクトソート処理の検証結果＞

以下がバッファ数の推移（抜粋）であるが、1ブロックだけデータベース・
バッファに載っている様子がうかがえる。違いは一目瞭然であろう。

|1|
|1|
|1|
|1|
|1|
|1|

次回は、sort_direct_writes=autoで検証を行う。

このメールが皆様の元に届く頃には、私は「NET&COM」のブースにいるでしょう。
時間の許す限り、是非、「インサイトテクノロジー」のブースにお立ち寄り下さい。
本文中でも触れた「パフォーマンスインサイト」を出展しておりますので、是非、
このツールの良さを体感していって下さい。
また、前回のOOWのときと違って、一人一人のお客様とお話したいと思っております
ので、よろしくお願いします。

以上サツマイモタイヤキはどうなの？？茅ヶ崎にて

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

今回は、「整合性制約の管理」のつづきで「整合性制約の状態」についてお送
りしよう。

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

前回は、制約の状態を「DISABLE NOVALIDATE」に設定して動きを見てもらった。
今回は、制約の状態を「DISABLE VALIDATE」に設定したときの動きを見てみよ
う。
「DISABLE 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 ;

＜制約 PK_WORKを DISABLE VALIDATE状態に変更する＞

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 VALIDATE CONSTRAINT PK_WORK ;
表が変更されました。
SQL> SELECT TABLE_NAME , INDEX_NAME FROM USER_INDEXES
                                        WHERE TABLE_NAME = 'WORK' ;
レコードが選択されませんでした。

主キーを DISABLE VALIDATE に設定すると主キーに付いていた索引は、削除さ
れるのである。

＜ INSERT文＞

SQL> INSERT INTO WORK(ID) VALUES(1) ;
INSERT INTO WORK(ID) VALUES(1)
*
エラー行: 1: エラーが発生しました。
ORA-25128:
制約(SCOTT.PK_WORK)が使用禁止および検査対象の表で挿入/更新/削除はできません。

＜ UPDATE文＞

SQL> UPDATE WORK SET ID = 6 WHERE ID = 1 ;
UPDATE WORK SET ID = 6 WHERE ID = 1
*
エラー行: 1: エラーが発生しました。
ORA-25128:
制約(SCOTT.PK_WORK)が使用禁止および検査対象の表で挿入/更新/削除はできません。

＜ DELETE文＞

SQL> DELETE FROM WORK ;
DELETE FROM WORK
*
エラー行: 1: エラーが発生しました。
ORA-25128:
制約(SCOTT.PK_WORK)が使用禁止および検査対象の表で挿入/更新/削除はできません。

以上の結果より
制約を DISABLE VALIDATE 状態に設定すると、既存のデータの一意性をチェック
した後は、表に対する変更（DML文）を禁止されているのが確認できる。

制約が「DISABLE VALIDATE」状態の特徴は、表のデータに対して変更は行われ
ないことであるが、デメリットとして、既存のデータの一意性は保証したいと
きに、一意性制約を作成すると作成されていた索引が作成されないということ
である。つまり、この索引を利用した検索ができないということである。

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

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

近々「どっぷり入門生活」の内容＆やり方を変更しようと思っています。
例えば、テーマは持たずに１回読み切りタイプや、もっと実用的な入門タイプ
等を考えています。
読者の皆様の、ご意見をお聞かせください。

よろしくお願いします。

以上「今日から NET&COM21 来てね！私も行きます。」モンキー・ターンより

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