Oracle 9i 関する検証　その２

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

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

今回はデータベース・バッファのヒット率について検証してみる。
一般的にデータベース・バッファのヒット率は最低でも90%を維持する必要が
あると言われている。

ちなみに、ヒット率は以下の式で求めることが出来る。

1 – (physical reads / (db block gets + consistent gets)）

上記のphysical reads、db block gets、consistent getsの値は
v$buffer_pool_statisticsやv$sysstatから取得できる。

9iの以下の環境で、検索条件を変えてヒット率を見ていこう。
尚、今回の検証はすべて、検索するデータをメモリ上に載せるために十分な
データベース・バッファのサイズを事前に設定している。

----------------------------------------------------------------
1．ブロックサイズが2Kと16Kのテーブルスペースを作成
2．各テーブルスペースにテーブル名だけが違うまったく同一のテーブルを
作成
3．各テーブルに100万件のデータ（1レコード長の平均サイズは100バイト）
を挿入
----------------------------------------------------------------

複数のテーブルスペースでブロックサイズが設定できる9iの新機能、マルチ
ブロック・サイズを使用してブロックサイズが2Kと16Kの2つのケースでデー
タベース・バッファのヒット率を取得してみる。

では、まずは単純にフル検索を行なってみよう。
Oracleを立ち上げて最初の検索なので、データベースバッファ上に検索対象
のデータはまったく存在しない状態であり、一般的に考えるとヒット率が0で
あることが予想される。

今回はOracleのAUTOTRACE機能で、physical readsなどの統計情報を取得する。
(今回は1つのSQL文に焦点を当てている為、AUTOTRACEの統計情報を使用）

# DB_BLOCK_SIZE = 2K

SQL> select * from short2;

1000000 rows selected.

Elapsed: 00:01:10.82

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE
1    0   TABLE ACCESS (FULL) OF 'SHORT2'
Statistics
----------------------------------------------------------
225  recursive calls
12  db block gets
133369  consistent gets
66674  physical reads
0  redo size
56036940  bytes sent via SQL*Net to client
8733749  bytes received via SQL*Net from client
66668  SQL*Net roundtrips to/from client
4  sorts (memory)
0  sorts (disk)
1000000  rows processed

# DB_BLOCK_SIZE = 16K

SQL> select * from short16;

1000000 rows selected.

Elapsed: 00:01:05.43

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE
1    0   TABLE ACCESS (FULL) OF 'SHORT16'
Statistics
----------------------------------------------------------
225  recursive calls
11  db block gets
73983  consistent gets
7815  physical reads
0  redo size
56036940  bytes sent via SQL*Net to client
8733749  bytes received via SQL*Net from client
66668  SQL*Net roundtrips to/from client
4  sorts (memory)
0  sorts (disk)
1000000  rows processed

上記の結果からバッファのヒット率の状況を見る際には
以下の点に注意する必要があると言えよう。

○consistent getsの値にはデータブロック以外のデータも含まれる。

テーブルのフルスキャンで全データのDISKからの読み込みが発生した場合は

physical reads = db block gets + consistent gets

だと思われがちだが、上記の結果より、
”db block gets + consistent gets”がphysical readsの値をはるかに上回
っていることが分かる。

○ブロックサイズが大きい方がヒット率は一般的高い。

既にお気づきだとおもうがブロックサイズによってphysical readsの値が大
きく違う。

DB_BLOCK_SIZE             2K          16K
---------------- ----------- ------------
consistent gets       133369        73983
physical reads         66674         7815

先程の検証結果はデータベースを立ち上げて最初の検索結果である。
データベース・バッファ上にはデータは載っていないので、検索対象オブジ
ェクト(short2,short16)のデータ自体のヒット率は0%と考えて間違いない。
先程のヒット率を求める式に入れて、ヒット率を求めてみよう。

1-(physical reads / (db block gets + consistent gets)）

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

ヒット率89%！！

すべてのデータをフルスキャンしているにも関わらずヒット率が89%もあるよ
うに見えてしまうのである。
ブロックサイズを16Kのように大きくしている場合は、データベース・バッフ
ァのヒット率を見る際に注意する必要があると言えよう。

では、なぜブロックサイズが大きい場合にヒット率が著しく高いのであろうか？
実はブロックサイズの違った時と同じ現象がブロックサイズ2Kだけの場合で
も再現出来る。

以下は1レコード長の違いによるヒット率の違いである。
ブロックサイズは2Kで検証を行なっている。

*************************************************************
平均1レコード長(Byte)  全件検索時のヒット率(%)
---------------------  ----------------------
1621                   0.685
813                   7.459
56                  65.832
14                  85.830
*************************************************************

1ブロックに1レコードしか格納できないレコード長(1621)の場合はヒット率
は1%を下回っており、期待通り（？）の結果といえる。
1ブロックに格納するレコード数が増加するにしたがってヒット率が高くなっ
ている。

先ほどはブロックサイズが16Kの場合で平均レコード長100バイトでヒット率
を出したが、ブロックサイズ2Kの場合でも平均レコード長を100バイトの約7
分の1にすることによってヒット率は16Kの場合と同じような高い値になるこ
とがわかる。

以上の結果よりphysical readsは純粋にブロックをディスクから読み込んだ
ブロック数であるが、consistent getsは同じブロックに対してOracleの内部
的な処理で複数回アクセスした回数がカウントアップされているものと思わ
れる。

データベース・バッファのサイズを変更する場合は、単にヒット率を見るだ
けではなくその他の要因もみて、考える必要があると言えよう。

インデックス検索の場合：
ブロックサイズが2Kと16Kで、まったく同じインデックス検索を行なった場合
のヒット率は以下の様になった。

ブロックサイズ  ヒット率
--------------  --------
2K      8.6%
16K     12.0%

こちらもOracleを起動して最初の検索である。
テーブルの全件検索の場合のヒット率に比べ著しく低い。
同一のブロックへのアクセスが、1回のみ(1レコード)の場合が多い為であろ
うことが分かる。

最後に9ｉからはバッファプールのヒット率の計算をする際に、ブロックサイ
ズごとにヒット率を出すことが可能である。
以下の様にv$buffer_pool_statisticsにはブロックサイズごとに、統計情報
が格納されている。

SQL> select block_size , consistent_gets, PHYSICAL_READS,DB_BLOCK_GETS
2  from v$buffer_pool_statistics;

BLOCK_SIZE CONSISTENT_GETS PHYSICAL_READS DB_BLOCK_GETS
---------- --------------- -------------- -------------
2048          164506          75739           814
16384           73950           7815            11

以上、春休み茅ヶ崎にて

Oracle 9i 関する検証 その２