Oracle9iに関する検証 その８
＜Oracle 9iに関する検証 その８＞
ペンネーム ちょびひげ
— データベース・バッファ上のデータ 3 -－
今回はインデックス検索の場合の、バッファ上のデータを見ていきたい。
では早速、1件のSELECT処理を、条件（ID）を１～100000に増やしながら行な
ってみる。
ちなみに、テーブル（BLOCK4）の列（ID)は１～100000の連番で、この列には
インデックスが張ってあり、ブロックサイズが4Kのテーブルスペースに作成
してある。
# テーブル定義
SQL> desc block4; 名前 NULL? 型 ---------- -------- ---------------------------- ID NUMBER TEXT VARCHAR2(200)
検索は本メールマガジンでおなじみの自社開発言語POPSQLで行なう。
# ブロックサイズ4Kのバッファ上に空き領域が無い（STATE=0）ことを確認
SQL> select blsiz, count(*) blocks, state from x$bh
2 where state=0 ←------空き領域を示す”0”
3 group by blsiz ,state;
BLSIZ BLOCKS STATE
---------- ---------- ----------
2048 3784 0
16384 506 0
# POPSQLのプログラム
CONNECT ORA/ORA --- [接続]
SET FETCH 1
LOOP(x=1; x<100001; x++) --- [1～100000までループ]
SAMPLE select * from block4 where id = :x; --- [SQL文]
ENDLOOP ↑
[ここが1～1000000で変化]
# バッファ上の様子
SQL> select
2 o.object_name, blsiz , count(*) blocks , lru_flag , tch, state
3 from x$bh b , dba_objects o
4 where b.obj = o.object_id
5 and o.object_name in ('BLOCK4','BLOCK4_IDX')
6 group by b.blsiz, o.object_name, lru_flag, tch, state;
OBJECT_NAME BLSIZ BLOCKS LRU_FLAG TCH STATE
-------------- ---------- ---------- ---------- ---------- ----------
BLOCK4_IDX 2048 174 0 1 1
BLOCK4_IDX 2048 1 0 2 1
BLOCK4_IDX 2048 1 0 21 1
BLOCK4_IDX 2048 1 0 22 1
BLOCK4 4096 354 0 1 1
↑ ↑ ↑
↓ 【注目1】 【注目2】 【注目3】
↓ ↓ ↓
OBJECT_NAME BLSIZ BLOCKS LRU_FLAG TCH STATE
-------------- ---------- ---------- ---------- ---------- ----------
BLOCK4_IDX 2048 730 0 1 1
BLOCK4_IDX 2048 1 0 12 1
BLOCK4_IDX 2048 1 0 19 1
BLOCK4_IDX 2048 3 0 20 1
BLOCK4_IDX 2048 1 0 88 1
BLOCK4 4096 1279 0 1 1
まず始めにTCHの値（ブロックへのアクセス回数）を見て頂きたい【注目3】。
インデックス（BLOCK4_IDX）のTCHの値が大きいものが存在する。
ここでは詳しく検証しないが、インデックスの構造を考えればリーフブロック
にたどり着くまでにブランチブロックをアクセスする必要があるため、ブラン
チブロックのアクセスの回数は多くなるはずである。アクセス回数（TCH）が
88回のブロックなどはルート・ノードであることが容易に予想される。
詳しくはバックナンバー ”インデックスに関する検証” を見て頂きたい。
次に注目して頂きたいのはテーブル（BLOCK4）のバッファ上のブロック数であ
る【注目1】。1件ごとに10万件の検索を行なうことにより、バッファ上のすべ
ての領域を占めると思われたが、約7万件を挿入したところで、テーブル
（BLOCK4）がバッファ上で占める割合が増えなくなった。
実はこれはOracleの隠しパラメータ ”_db_percent_hot_default” で決めら
れた、”ホットな領域”に関係する。
一度でもアクセスされたデータをすべてLRUリストのMRU側に持ってきては頻繁
に使用するデータの追い出しがかかる可能性が高くなる。
その頻繁にアクセスされるデータの追い出しがかかる確率を低くする為、デー
タベースバッファの領域をホットな部分とコールドな部分に分けているのであ
る。そのホットな領域の割合を決めるパラメータが”_db_percent_hot_default”
なのである。このパラメータの値はデフォルトでは50％になっており、ディス
ク上のアクセスされたデータは、コールドな領域（50%）のMRU側におかれる。
先ほどの検証ではブロックサイズ4Kのバッファのブロック数が約3000ブロック
の為、コールドな部分の1500ブロックの領域内でテーブル（BLOCK4）のデータ
が循環している状態と思われる。上記の結果では1279ブロックで1500ブロック
に達してないのは、SYS所有のオブジェクトの領域なども含まれる為である。
次に、このホットな領域にブロックを入れるかどうか判断する基準が、X$BHの
TCH列の値である。つまりメモリ上でタッチ（アクセス）された回数である。
この回数も隠しパラメータ”_db_aging_hot_criteria”で設定されている。
つまり、メモリ上で指定された回数（デフォルトでは2）アクセスされたブロ
ックはホットな領域のMRU側に移される。
隠しパラメータの設定は以下のSQL文で確認出来るので見てみよう。
X$KSPPIとX$KSPPCVを検索することにより確認出来る。
SQL> select i.ksppinm parameter , v.ksppstvl VAL
2 from x$ksppi i, x$ksppcv v
3 where i.indx = v.indx
4* and i.ksppinm in ('_db_aging_touch_time','_db_percent_hot_default');
PARAMETER VAL
------------------------------ ------------------------------
_db_percent_hot_default 50
_db_aging_hot_criteria 2
”_db_aging_hot_criteria”が2になっているのでTCHの値が2になっているブ
ロックはホットな領域に移動される。
ちなみに先ほどの結果を見るとTCHが2を超えているものでも（BLOCK4_IDX）で
もホットな領域に移動されていない。
ホットな領域に移動とは前回ご紹介したX$BHの列（LRU_FLAG）の値が8になる
ことである。
次回は、、実際に検証と共に順を追って、データベース・バッファ上に読み込ま
れたデータのLRUリスト上での流れを見てみたい。
以上、茅ヶ崎にて