Oracle9iに関する検証 その１３
＜Oracle 9iに関する検証 その１３＞
ペンネーム ちょびひげ
— データベースバッファの大きさを
ヒット率で評価する事はナンセンス？？？ —
Oracle BlockはDirect I/O処理を除いて全てデータベース・バッファ経由とな
っている。したがって、チューニングのポイントは「使用頻度の高いOracle
Blockをバッファ上で複数プロセスから共有する」ということになる。共有で
きる割合が低ければメモリアクセスより負荷の高い（はるかに）ディスクから
の読み込み（物理読み込み）が発生してしまう。
当シリーズで解説してきたがOracle9iのLRUリストアーキテクチャでは、デー
タベースバッファを「ホットな領域」と「コールドな領域」の2つに分けて管
理し、アクセスの多いブロックと少ないブロックをそれぞれの属性（ホットor
コールド）で循環させ効率化を計るように改善された。
しかし、いかにOracleがこのような仕組みを持っていても、バッファのサイズ
が絶対的に小さければバッファ上のアクセスの多いブロックが再利用されずに
バッファ上から追い出されてしまう。
データベースバッファの適切な大きさを判断する基準として、ホットな領域の
TCH（アクセスされた回数）がつかえる。今回はこれに注目して検証を行なう。
では試しにバッファのサイズが小さな環境で上記の値を検索してみよう。
実行する負荷によって今回の検証結果は様々に変化するので、今回は”このよ
うに見える”と言う程度に検証結果を載せる。
ちなみに、負荷として単純な5種類ほどのトランザクションが同時に3つ動く、
ある一定の負荷である。
*************************************************************
# バッファサイズの設定を確認
NAME TYPE VALUE -------------------- ----------- ------- db_8K_cache_size big integer 8388608 --- （8M） db_cache_size big integer 8388608 --- （8M）
# ホットな領域（lru_flag=8）のタッチ（TCH）の平均を求める
SQL> select blsiz,avg(tch), set_ds
2 from x$bh
3 where lru_flag=8
4 group by blsiz, set_ds;
BLSIZ AVG(TCH) SET_DS
---------- ---------- --------
4096 8.64285714 82F80284
8192 1.1 82F80C80
※SET_DSはバッファの種類（KEEP、RECYCLEなど）を一意に識別する
この値を見ると以下のようなことが分かる。
ブロックサイズ8Kのホットな領域のアベレージが”1.1”と言うことは平均1.1
回アクセスされている。つまりその程度の再利用率である。（※ただし、数分
観察すると変動は大きい 0.2～3）
ただし、当シリーズでの検証からも分かるように、ホットな領域から追い出し
がかかる際に、TCHの値が2以上の場合はTCHがリセットされて（0になって）ホ
ットな領域上に残る。つまり、実際の再利用回数はもっと高いことが予測され
る。いずれにせよ、ホットな領域が少なすぎる状態でバッファのサイズが小さ
いと言えよう。
では、ブロックサイズ4Kの領域はどうであろうか、こちらは平均が”7”で再
利用率が良い。
しかし、これだけの情報でそこまで言い切ってしまってよいのであろうか？？
実は他にも見るべき情報がある。それは、ホットな領域のパーセンテージであ
る。以下の検索結果を見ていただきたい：
*************************************************************
# ホットな領域のブロック数とコールドな領域のブロック数を検索
SQL> select blsiz
2 , sum(decode(lru_flag,8,1,0)) hot
3 , sum(decode(lru_flag,8,0,1)) cold
4 ,set_ds
5 from x$bh
6 group by blsiz, set_ds;
BLSIZ HOT COLD SET_DS
---------- ---------- ---------- --------
4096 280 1686 82F80284
8192 500 502 82F80C80
上記はホットな領域とコールドな領域にあるバッファの数を示している。デフ
ォルトではホットな領域とコールドな領域の割合は”1：1”である。検索結果
を見て頂くとブロックサイズ4Kのホットな領域にはブロックが8つしか存在し
ない。つまり、効率が良いと言えば良いが、実態はバッファが余った状態なの
である。
では、初期化パラメータの値を変更して再度同様の検索を行なってみよう。
当然、ブロックサイズ8Kのバッファのサイズは大きくし、ブロックサイズ2Kの
バッファのサイズは小さくする。
NAME TYPE VALUE
------------------ ----------- -----------
db_8k_cache_size big integer 33554432 --- （30M）
db_cache_size big integer 4194304 --- （4M）
BLSIZ AVG(TCH) SET_DS
---------- ---------- --------
4096 31.3888889 82F881A4
8192 3.039 82F88BA0
BLSIZ HOT COLD SET_DS
---------- ---------- ---------- --------
4096 95 888 82F881A4
8192 2000 2008 82F88BA0
ブロックサイズ4Kのバッファはまだ、余裕があり余っている。
ブロックサイズ8Kのバッファは先程よりはTCHの平均がほんの少し上がってい
ることが分かる。ただし、ホットな領域のTCHの平均が低いことは確かである。
実際にブロックサイズが8Kのバッファの、1分間のディスクのI/O量を
v$buffer_pool_statistics表から取得したところ以下のような変化が見られた。
バッファサイズ : 8 MB ディスク読み込み量 : 16818 BLOCK/min ---------------------------------------- バッファサイズ : 36 MB ディスク読み込み量 : 3734 BLOCK/min
物理読み込みの量が約4分の1になっていることが分かる。
しつこいようだが、もう少しバッファのサイズをもう少し大きくした場合の検
証結果である。
バッファサイズ : 40 MB
ディスク読み込み量 : 10 BLOCK/min
BLSIZ AVG(TCH) SET_DS
---------- ---------- --------
4096 .468888889 82F8BF84
8192 62.8360656 82F8C980
ホットな領域の平均値が高い値で推移している。1分間のバッファへの読み込
み量は10BLOCKでほとんどないに等しい状態で、パフォーマンス的に見れば最
高の状態といえる。
今回の内容を見て、多くの読者の方はヒット率を見ればいいじゃないか！と言
う方も多いと思う。確かにバッファのヒット率を見ることは、意味がある。た
だし、過去に検証したようにブロックサイズはアプリケーションでFETCHする
際の配列設定でヒット率が変わる。また、全件検索などの効率の悪いSQL文が
流れた時に、それが原因でヒット率が下がる傾向にある。
以下は全件検索を行なった際のヒット率とホットな領域の変化である。
ヒット率 X$BHの検索結果
--------------- -------------------------------
99.77% BLSIZ AVG(TCH) SET_DS
---------- ---------- --------
4096 2.38 82F8BF84
8192 20.453081 82F8C980
--- 全件検索処理 ---
86.26% BLSIZ AVG(TCH) SET_DS
---------- ---------- --------
4096 3.18444444 82F8BF84
8192 24.1344674 82F8C980
※あるタイミングでlur_flag=8のブロックが急激に増える（50%に達する）こ
とがあるが、このときはAVGも急激に下がる
当然、負荷の高いSQL文、つまり大量のDISK I/Oが行なわれるので、バッファ
のヒット率は下がる。しかし、ホットな領域のデータは全件検索の再利用性の
低いデータから保護されるので、ホットな領域のTCHの平均値が下がることは
ない。この保護されるべきデータがどんどん追い出される環境は、最初に述べ
たとおりバッファが小さい。
全件検索の負荷を切り捨てたバッファサイズの調整が可能であることが分かる。
本番環境では、トランザクションの形態やユーザ数などが時間によって様々に
変化する環境が多いであろう。そのような環境では適切なバッファのサイズを
調べるのは極めて難しいかもしれない。しかし、トランザクションのピーク時
間を見極めて、その時のパフォーマンスに合わせたバッファサイズの設定は不
可欠である。
また、Oracle9iからはバッファのサイズをリアルタイムに変更することが可能
である。アプリケーションによってOracleのブロックサイズを適切なサイズに
設定し、時間によってバッファのサイズを変更することによって最高のパフォ
ーマンスを得ることが出来るかもしれない。
以上、夏間近の茅ヶ崎にて