Oracle 9iに関する検証　その３

＜Oracle 9iに関する検証　その３＞
ペンネームちょびひげ

— データベース・バッファとブロックサイズ -－
～データベース・バッファのヒット率続き～

今回は前回の検証で見られた以下の現象についてもう少し調べてみたい。

現象：
ブロックサイズが16Kのテーブルスペースに作成したテーブルに対し、全件検
索を行なった時、すべてのデータをディスク上からメモリに読み込んでいる
にも関わらずヒット率が高い。

以下は、前回の検証で得られたAUTOTRACEの統計情報である。

# DB_BLOCK_SIZE = 16K

SQL> select * from short16;

1000000 rows selected.

Elapsed: 00:01:05.43

Statistics
----------------------------------------------------------
        225  recursive calls
         11  db block gets
      73983  consistent gets
       7815  physical reads

ブロックサイズ  ヒット率
--------------  --------
           16K       89%

まず、上記の結果を得たSQL文のトレースファイルを見てみたい。
トレースファイルは以下のようにalter sessionを行なうことで、初期化パラメ
ータuser_dump_destで指定したディレクトリに出力される。

SQL> alter session set sql_trace=true;

Session altered.

以下、実行したSQL文の情報に該当する部分をトレースファイルから取得した
内容である。
（通常はtkprofを使用するが、今回は直接テキストエディタで見ている）

select * from short16
END OF STMT
PARSE#1:c=30000,e=42918,p=10,cr=75,cu=1,mis=1,r=0,dep=0,og=4,tim=1018008278104148
EXEC #1:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1018008278104469
FETCH #1:c=0,e=2141,p=9,cr=1,cu=3,mis=0,r=1,dep=0,og=4,tim=1018008278106761
                                        ↑
                                       [注目1]！！
                                        ↓
FETCH #1:c=0,e=124,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278108238
FETCH #1:c=0,e=112,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278109385
FETCH #1:c=0,e=108,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278110350
FETCH #1:c=0,e=111,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278111310
FETCH #1:c=0,e=112,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278112272
FETCH #1:c=0,e=115,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278113246
FETCH #1:c=0,e=115,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278114208
FETCH #1:c=0,e=116,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278115165
FETCH #1:c=0,e=137,p=0,cr=2,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278116190
                          ↑[注目2]！！
FETCH #1:c=0,e=116,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278117152
FETCH #1:c=0,e=112,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278118115
FETCH #1:c=0,e=117,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278119157
FETCH #1:c=0,e=118,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278120154
FETCH #1:c=0,e=115,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278121121
FETCH #1:c=0,e=115,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278122106
FETCH #1:c=0,e=116,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278123070
FETCH #1:c=0,e=117,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278124039
FETCH #1:c=0,e=131,p=0,cr=2,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278125038
FETCH #1:c=0,e=111,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=1018008278126019
    ・
    ・
    ・以下同様に続く（6万行くらい。。。）

まず[注目1] の上側をみて頂きたい。
”r”はFETCHで取得された行数を表している。

SQL*Plusでは最初のFETCHで1行取り出し、2行目以降は配列サイズ（arraysiz
e）、つまり、複数行づつ取り出している。
実は、2回以降のFETCHで一度に取得される15行という値はSQL*Plusのデフォル
トの配列サイズなのである。

# 現在の設定を確認

SQL> show arraysize
arraysize 15

# 以下のコマンドで変更することが可能である。

SQL> set arraysize 40

[注目1] の下側を見ていただくと15、つまり15行になっていることが確認出来る。

次に、[注目2]をみて頂きたい。
”cr”は読取り一貫性モード（Consistent Read）でブロックへアクセスした回
数を示している。
これがAUTOTRACEで取得される統計情報の”consistent gets”の値である。
事実、約6万行の”cr”の値の合計と”consistent gets”の値が同じになるこ
とが確認できた。

ほとんどのFETCHはcrが１であるが[注目2]では2になっている。
これは15行を読み込む際に2つのブロックにアクセスしている為であろう。
（例：1ブロックから7行読み込み、もう1ブロックから8行を読み込んでいる）

上記をふまえた上で
—どうすればヒット率が高くなるだろうか？—

方法1．配列サイズを、1ブロックのデータがちょうど1つの配列に入る大きさ
      にする。

これは1回目FETCHで取得される行数が1行であることを考えるとほぼ不可能だ
ろう。1ブロックに1行のデータなら可能だが、今回は検証外とする。

方法2．配列サイズをすべての取得行が入る大きさにする。

1回の読み込みですべての行数が得られればヒット率は高くなるはずである。
先ほどのSQLでは100万行を読み込んでいるので、配列サイズを100万に設定し
てみよう！

SQL> set arraysize 1000000
SP2-0267: arraysize option 1000000 out of range (1 through 5000)

やはり怒られてしまった。5000が最大のようである。
では配列サイズを最大の5000に設定してもう一度ヒット率を計算してみたい。

SQL> set arraysize 5000
SQL> select * from short16;

1000000 rows selected.

Elapsed: 00:00:41.59

Statistics
----------------------------------------------------------
        470  recursive calls
         11  db block gets
       8089  consistent gets
       7825  physical reads

ヒット率 3.4%!
今回だけ読んで頂いている方はあたりまえだろ！と思われるかもしれないが、
arraysizeを大きくしたことによりヒット率が激減したことが分かる。

また、注目して頂きたいのは実行時間である！

配列サイズを15 から 5000 に変更することによって、実行時間が25秒も短縮
されている。この差は大きいと言えよう。

1分05秒43 → 41秒59

では、上記の結果のトレースファイルを見てみよう。

select * from short16
END OF STMT
PARSE #1:c=30000,e=31667,p=10,cr=75,cu=1,mis=1,r=0,dep=0,og=4,tim=1018012670428720
EXEC #1:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1018012670429048
FETCH #1:c=0,e=2112,p=9,cr=1,cu=3,mis=0,r=1,dep=0,og=4,tim=1018012670431310
FETCH #1:c=40000,e=87737,p=32,cr=39,cu=0,mis=0,r=5000,dep=0,og=4,tim=1018012670606317
FETCH #1:c=50000,e=84057,p=39,cr=40,cu=0,mis=0,r=5000,dep=0,og=4,tim=1018012670797449
FETCH #1:c=30000,e=84669,p=40,cr=40,cu=0,mis=0,r=5000,dep=0,og=4,tim=1018012670992133
FETCH #1:c=70000,e=85550,p=40,cr=40,cu=0,mis=0,r=5000,dep=0,og=4,tim=1018012671186722
FETCH #1:c=40000,e=85313,p=40,cr=40,cu=0,mis=0,r=5000,dep=0,og=4,tim=1018012671382058
FETCH #1:c=50000,e=86257,p=40,cr=40,cu=0,mis=0,r=5000,dep=0,og=4,tim=1018012671577024
FETCH #1:c=40000,e=85796,p=40,cr=40,cu=0,mis=0,r=5000,dep=0,og=4,tim=1018012671786778
FETCH #1:c=40000,e=85446,p=40,cr=40,cu=0,mis=0,r=5000,dep=0,og=4,tim=1018012671982569
FETCH #1:c=30000,e=83515,p=32,cr=40,cu=0,mis=0,r=5000,dep=0,og=4,tim=1018012672177466
FETCH #1:c=0,e=85252,p=40,cr=41,cu=0,mis=0,r=5000,dep=0,og=4,tim=1018012672371377
FETCH #1:c=40000,e=84414,p=40,cr=40,cu=0,mis=0,r=5000,dep=0,og=4,tim=1018012672566661
FETCH #1:c=30000,e=84616,p=40,cr=40,cu=0,mis=0,r=5000,dep=0,og=4,tim=1018012672762942
FETCH #1:c=40000,e=86818,p=40,cr=40,cu=0,mis=0,r=5000,dep=0,og=4,tim=1018012672959139
    ・
    ・
    ・以下同様に続く（2000行くらい）

ほとんどのFETCHで40ブロックにアクセス（cr=40）し5000行を取得（r=5000）
している様子がよく分かる。
また、PARSEを行なう際の”cr”が75加算されている事と、”cr”が41の場合
がある事で、その分、読取り一貫性モードでブロックへアクセスが増えて、ヒ
ット率が0%でなく3．4%になっていることが分かる。

2回にわたってデータベース・バッファのヒット率に注目して検証してきたが、
検証出来なかった他の条件でもヒット率が大きく変わること予想される。

ヒット率が下がっても実行時間が早くなっている事を考えると、チューニング
を行なう際は、単純にデータベース・バッファのヒット率だけではなく、その
他の条件も考慮する必要があると言えよう。

以上、桜散った茅ヶ崎にて

Oracle 9iに関する検証 その３